Results 1 to 3 of 3
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    Retrieve Highest Invoice Number form Quickbooks

    I'm hoping to find someone that has some experience with MS Access and querying data from Quickbooks. Specifically, Quickbooks Enterprise. I have an app that will import orders from MS Access into QBES. However, I have to manually visit QBES and get the highest invoice number, enter into MS Access and start the process. What I'd like to do is get the highest Invoice number ([Num] field) and use that in MS Access.

    I downloaded Intuit's SDK Tool and can connect to QBES and run some of there sample queries, but that doesn't seem to be doing what I need.



    Suggestions?

    Thanks!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you can run queries then you just need a query along the lines of

    SELECT Max(InvoiceNo) AS MaxInvNum FROM tblInvoices

    you'll need to find what InvoiceNo and tblInvoices are actually called in QBES. Run some simple 'SELECT *....' queries to see the field names and contents from the different QBES tables until you find the one you want

    EDIT: if your invoice numbers are alpha numeric then you will need probably need to split the number into txt and number to determine the 'highest' invoice number

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    I'm trying to hammer through the below, not knowing VB. It's not likely I will find someone here knowing both MS Access VB and Quickbooks SDK.

    This line is in error: Set query = msgReq.AppendInvoiceQueryRq.ORInvoiceQuery.RefNumb erList.GetAt
    Compile Error: Argument not optional.
    What kind of argument is expected for GetAt? What does GetAt even mean?

    Code:
    Private Sub InvNo()
        Dim accessDB As Database
        Set accessDB = CurrentDb
        If (accessDB Is Nothing) Then
            Exit Sub
        End If
        Dim SessMgr As New QBSessionManager
        
        SessMgr.OpenConnection "", "Highest InvNo"
        SessMgr.BeginSession "", omDontCare
        
        Dim msgReq As IMsgSetRequest
        Set msgReq = SessMgr.CreateMsgSetRequest("US", 13, 0)
        
        Dim query As IInvoiceQuery
        Set query = msgReq.AppendInvoiceQueryRq.ORInvoiceQuery.RefNumberList.GetAt
        
        query.ORInvoiceQuery.InvoiceFilter.ORDateRangeFilter.TxnDateRangeFilter.ORTxnDateRangeFilter.TxnDateFilter.FromTxnDate.SetValue ("2019-08-01")
        query.ORInvoiceQuery.InvoiceFilter.ORDateRangeFilter.TxnDateRangeFilter.ORTxnDateRangeFilter.TxnDateFilter.ToTxnDate.SetValue ("2019-08-10")
        
        Dim resp As IMsgSetResponse
        Set resp = SessMgr.DoRequests(msgReq)
        
        Dim respList As IResponseList
        Set respList = resp.ResponseList
        
        Dim curResp As IResponse
        Set curResp = respList.GetAt(0)
        
        If (curResp.statusCode = 0) Then
            Dim respType As IResponseType
            Set respType = curResp.Type
            If (respType.GetValue = rtInvoiceQueryRs) Then
                Dim custList As IInvoiceRetList
                Set custList = curResp.Detail
                
                Dim curCust As IInvoiceRet
                Dim i As Integer
                Dim insSQL As String
                For i = 0 To custList.Count - 1
                    insSQL = "INSERT INTO Customers " _
                        & "(InvNo) " _
                        & "VALUES " _
                        & "("
                        
                    Set curCust = custList.GetAt(i)
                    If (curCust.Sublevel.GetValue = 0) Then
                        insSQL = insSQL & "'" & curCust.RefNumber.GetValue & "',"
                        accessDB.Execute insSQL
                    End If
                Next i
            End If
        End If
        
        SessMgr.EndSession
        SessMgr.CloseConnection
        Set SessMgr = Nothing
        
        
    End Sub
    thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 05-16-2017, 07:34 AM
  2. Replies: 8
    Last Post: 02-08-2017, 07:58 PM
  3. Replies: 15
    Last Post: 12-22-2016, 12:16 PM
  4. Invoice number
    By slik_02 in forum Access
    Replies: 3
    Last Post: 08-08-2009, 08:05 AM
  5. Invoice Number
    By rzwoo in forum Access
    Replies: 0
    Last Post: 02-25-2009, 01:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums