|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| SP_LASTINSERTID |
| Author |
Message |
| Aco |
| |
| Group | : Members |
| Posts | : 5 |
| Joined | : 2007-01-09 |
|
| Profile |
|
| Posted : 2007-01-09 09:54:01 |
'The code sample below is used to enter purchase orders into qb from access 2000
'When this is completed i need to read back the SP_LASTINSERTID from QB for confirmation of the order
'how and where do i do this so that i can then use the value from SP_LASTINSERTID field
' i can get to the field names using the following code...but cant get to the field data ?
'
'QBSQL = "SP_LASTINSERTID"
'QBRecordset.Open QBSQL, QBConnection, aDopenstatic, aDlockoptimistic
'
' Dim fld As Field
' Set rst = QBRecordset
' For Each fld In rst.Fields
' Print field names.
' Debug.Print fld.Name
' Next
'This returns SP_LASTINSERID AND ERRORCODE
'but cant get to the field data ?
Dim QBConnection As ADODB.Connection
Dim QBRecordset As ADODB.Recordset
Dim QBMsg As String
Dim QBConnectString As String
Dim QBSQL As String
Dim DB As Database
Dim rt As Integer
Dim count As Integer
Dim partt As Recordset
Dim PreviousEntry As String
On Error GoTo Err_Command218_Click
Me.Requery
DoCmd.OpenQuery "makecount"
Set DB = CurrentDb
Set partt = DB.OpenRecordset("SELECT * " & "FROM [ttt]", dbOpenSnapshot)
partt.MoveLast
partt.MoveFirst
rt = partt.RecordCount
partt.Close
DoCmd.OpenQuery "clearcount"
QBConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"
QBSQL = "SELECT * FROM PurchaseOrderLine"
Set QBConnection = New ADODB.Connection
Set QBRecordset = New ADODB.Recordset
QBConnection.Open QBConnectString
QBRecordset.Open QBSQL, QBConnection, aDopenstatic, aDlockoptimistic
For count = 1 To rt 'Add line items
Qty = Me.Order_Qty
qbDescription = Me.Description
Price = Me.Price_ex__Del_ex_
QBRecordset.AddNew
QBRecordset.Fields("PurchaseOrderLineItemRefListID").Value = "40000-1045537150"
QBRecordset.Fields("PurchaseOrderLineDesc").Value = qbDescription
QBRecordset.Fields("PurchaseOrderLineQuantity").Value = Qty
QBRecordset.Fields("PurchaseOrderLineRate").Value = Price
QBRecordset.Fields("FQSaveToCache").Value = True
QBRecordset.Update
If count < rt Then
DoCmd.GoToRecord , , acNext
Else
GoTo alldone
End If
Next count
alldone:
If Me.Check11 = True Then 'set freight cost
QBRecordset.AddNew
QBRecordset.Fields("PurchaseOrderLineDesc").Value = ""
QBRecordset.Fields("FQSaveToCache").Value = True
QBRecordset.Update
QBRecordset.AddNew
QBRecordset.Fields("PurchaseOrderLineItemRefListID").Value = "2A0000-1080093346"
QBRecordset.Fields("PurchaseOrderLineRate").Value = Me.Text11
QBRecordset.Fields("FQSaveToCache").Value = True
QBRecordset.Update
End If
If Me.Check10 = True Then 'line item message
QBRecordset.AddNew
QBRecordset.Fields("PurchaseOrderLineDesc").Value = ""
QBRecordset.Fields("FQSaveToCache").Value = True
QBRecordset.Update
QBRecordset.AddNew
QBRecordset.Fields("PurchaseOrderLineDesc").Value = Me.Text10
QBRecordset.Fields("FQSaveToCache").Value = True
QBRecordset.Update
End If
QBRecordset.AddNew 'close entry
If Me.ShipToCheck = True Then QBRecordset.Fields("ShipToEntityRefListID").Value = Me.ShipTo
If Me.TermsCheck = True Then QBRecordset.Fields("TermsRefListID").Value = Me.terms
If Me.VendorMsg = True Then QBRecordset.Fields("VendorMsg").Value = Me.Vendor_msg
If Me.UseMemo = True Then QBRecordset.Fields("Memo").Value = Me.QBMemo
QBRecordset.Fields("TemplateRefListID").Value = Me.Template
QBRecordset.Fields("VendorRefListID").Value = Me.Vendor_Ref
QBRecordset.Fields("ExpectedDate").Value = Me.ExpectedDate
QBRecordset.Fields("AmountIncludesVAT").Value = Me.AmountIncludesVAT
QBRecordset.Fields("ClassRefListID").Value = Me.Class
QBRecordset.Fields("ShipMethodRefListID").Value = Me.ship_via
QBRecordset.Fields("IsToBePrinted").Value = Me.To_Print
QBRecordset.Fields("FQSaveToCache").Value = False
QBRecordset.Update
QBRecordset.Close
QBConnection.Close
DoCmd.GoToRecord , , acFirst
End Sub
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-09 12:52:19 |
You need to run
'QBSQL = "SP_LASTINSERTID PurchaseOrderLine"
with the correct table name before your QBConnection.Close line |
|
|
|
| Aco |
| |
| Group | : Members |
| Posts | : 5 |
| Joined | : 2007-01-09 |
|
| Profile |
|
| Posted : 2007-01-09 16:56:17 |
Thanks for the reply,
my problem is that i can open and read the field names from the QB tables, but i dont know how to get to the data in the field. Iv'e tried looking a sample code from allover
'QBSQL = "SELECT * From Class"
'QBRecordset.Open QBSQL, QBConnection, aDopenstatic, aDlockoptimistic
'
' Dim fld As Field
' Set rst = QBRecordset
' For Each fld In rst.Fields
' Print field names.
' Debug.Print fld.Name
' Next
'This returns the name of each field in the open QBRecordset
'but cant get to the field data ?
i think it's an ado issue but i'm new to this level of programing.
|
|
|
|
| Aco |
| |
| Group | : Members |
| Posts | : 5 |
| Joined | : 2007-01-09 |
|
| Profile |
|
| Posted : 2007-01-09 18:02:55 |
I'm getting there, the code below shows how i can get data from one table and put it in another.
But i cant get any data from the Quickbooks table
Any help from anybody will be greatly appriciated.
Dim QBConnection As ADODB.Connection
Dim QBRecordset As ADODB.Recordset
Dim QBConnectString As String
Dim QBSQL As String
Dim rt As Integer
QBConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"
Set QBConnection = New ADODB.Connection
Set QBRecordset = New ADODB.Recordset
QBConnection.Open QBConnectString
QBSQL = "SELECT * from Class"
QBRecordset.Open QBSQL, QBConnection, aDopenstatic, adLockOptimistic
' The next lines fail
Me.RecordSource = "QBRecordset.Open QBSQL, QBConnection, aDopenstatic, adLockOptimistic"
' or
QBRecordset.Open QBSQL, QBConnection, aDopenstatic, adLockOptimistic
Me.RecordSource = QBRecordset
' Here is where i need to set the recordsource of my form to the Quickbooks table
' The next line is ok and gives me access to the data in the fields
Set TABLE = DB.OpenRecordset("Class", dbOpenDynaset, dbAppendOnly)
Dim DB As Database
Set DB = CurrentDb
Me.RecordSource = "SELECT * from Class" ' THIS IS A LOCAL TABLE
BeginTrans
For count = 1 To 5
TABLE.AddNew
TABLE![ListID] = Me.ListID
TABLE.Update
If count <= rt Then DoCmd.GoToRecord , , acNext
Next count
' this just adds the first 5 lines of the class table back into the class table
CommitTrans
TABLE.Close
QBRecordset.Close
QBConnection.Close
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-09 19:22:30 |
sp_lastinsertid is a stored procedure. It's not a table or view and it returns one row with the LastInsertId and error message if any.
SP_LASTINSERTID Invoice
You can workaround using sp_lastinsertid by quering the new purchase order details instead:
select Top 1 txnid from PurchaseOrder ORDER BY TimeModified DESC |
|
|
|
|