Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Unhappy Unanswered: HELP - DAO wont work in upgraded DB !!

    Can anyone explain to me why this doesn't work in Access 2000?

    1.References are not broken
    2.Worked perfectly in Access 97 (without DAO.Recordset, DAO.Database) and no ADO reference




    Calls function Daily Report first


    '************************************************* *****************
    Function DailyReport()
    Dim MSG, Style, Title, Help, Ctxt, Response, Response2, Response3, MyString
    MSG = "This will create Invoices. Are you sure you want to continue ?" & Chr(10) & Chr(13) & "PLEASE ENSURE YOU HAVE CHECKED THE DOCKET NUMBERS BEFORE YOU CONTINUE !!" ' Define message.
    Title = "Confirm Invoice Update !!" ' Define title.
    Style = vbYesNo + vbCritical + vbDefaultButton2
    Response = MsgBox(MSG, Style, Title)

    If Response = vbYes Then ' User chose Yes.
    CreateInvoices '**********REM calls the next function shown below
    Response2 = MsgBox("Invoices have been updated successfully!", vbOKOnly, "Success !")
    DoCmd.OpenForm "Date_Range", , , , , , 0
    Else ' User chose No.
    Response3 = MsgBox("Invoices have not been updated!", vbOKOnly, "Process Cancelled")
    End If
    End Function
    '************************************************* ****************8
    function CreateInvoices()
    DoCmd.Hourglass True
    DoCmd.SetWarnings (False)

    DoCmd.OpenQuery "INVOICE_DETAILS", acViewNormal, acAdd
    'REM This runs up to here OK but the other stuff below doesn't
    DoCmd.SetWarnings (True)
    Dim CurJobNum
    Dim TmpJobNum
    Dim CurInvoiceNum
    Dim CurDocketNum

    Dim dbse As DAO.Database
    Dim rste As DAO.Recordset, lin As DAO.Recordset, doc As DAO.Recordset
    Set dbse = CurrentDb()
    Set lin = dbse.OpenRecordset("SELECT * FROM LastInvoiceNumber;")

    Set rste = dbse.OpenRecordset("SELECT INVOICES.* " _
    & "FROM INVOICES " _
    & "WHERE INVOICES.Invoice_Num = NULL " _
    & "ORDER BY INVOICES.Job_num;")


    If Not rste.EOF Then
    rste.MoveFirst

    Dim count
    count = 0
    While Not rste.EOF

    CurJobNum = rste!Job_Num
    TmpJobNum = CurJobNum
    CurInvoiceNum = InvoiceQuoteDockNum(lin!LastInvoiceNumber, 1)
    '***********************************InvoiceQuoteDo ckNum is the next function
    While TmpJobNum = CurJobNum
    CurDocketNum = rste!Docket_Num
    count = count + 1

    lin.Edit
    lin!LastInvoiceNumber = CurInvoiceNum
    lin.Update
    Set doc = dbse.OpenRecordset("SELECT * FROM DELIVERY_DOCKET " _
    & "WHERE DOCKET_NUMBER = '" & CurDocketNum & "';")

    rste.Edit
    rste!Invoice_Num = CurInvoiceNum
    rste!Invoice_Date = DATE
    rste!DEBTOR_ID = doc!DEBTOR_ID
    rste.Update
    doc.Edit
    doc!INVOICED = CurInvoiceNum
    doc.Update
    doc.Close
    rste.MoveNext
    If rste.EOF Then
    TmpJobNum = -1
    Else
    TmpJobNum = rste!Job_Num
    End If
    Wend
    Wend
    Else
    MsgBox "There are no Invoices to print"
    End If
    rste.Close
    lin.Close
    dbse.Close
    DoCmd.Hourglass False
    UpdatePayments 'REM Last Function on the page
    End Function
    '************************************************* ************

    Function InvoiceQuoteDockNum(LastNum As String, NumType As Integer) As String
    'Returns next valid invoice number
    Const max = 10000
    Const Length = 4
    Dim strMonth As String
    Dim strYear As String
    Dim strNum As String
    Dim strNumTmp As String
    Dim strLastNum As String
    Dim month As Integer
    Dim MyLen As Integer
    Dim oldMonth As String

    oldMonth = Right(Left(LastNum, 4), 3)

    month = DatePart("m", DATE)
    Select Case month
    Case 1
    strMonth = "JAN"
    Case 2
    strMonth = "FEB"
    Case 3
    strMonth = "MAR"
    Case 4
    strMonth = "APR"
    Case 5
    strMonth = "MAY"
    Case 6
    strMonth = "JUN"
    Case 7
    strMonth = "JUL"
    Case 8
    strMonth = "AUG"
    Case 9
    strMonth = "SEP"
    Case 10
    strMonth = "OCT"
    Case 11
    strMonth = "NOV"
    Case 12
    strMonth = "DEC"
    Case Else
    MsgBox "ERROR IN DATE"
    End Select

    strYear = DatePart("yyyy", DATE)
    strYear = Right(strYear, 2)
    If oldMonth = strMonth Then
    strNumTmp = val(Right(LastNum, Length)) + 1
    MyLen = Len(strNumTmp)
    If MyLen < Length Then
    Select Case MyLen
    Case 1
    strNum = "000" & strNumTmp
    Case 2
    strNum = "00" & strNumTmp
    Case 3
    strNum = "0" & strNumTmp
    Case Else
    MsgBox "error in invoice number length"
    End Select
    Else
    strNum = strNumTmp
    End If
    Else
    strNum = "0000"
    End If

    Select Case NumType
    Case 1
    InvoiceQuoteDockNum = "I" & strMonth & strYear & strNum
    Case 2
    InvoiceQuoteDockNum = "Q" & strMonth & strYear & strNum
    Case 3
    InvoiceQuoteDockNum = "D" & strMonth & strYear & strNum
    Case Else


    End Select
    End Function
    '************************************************* *****
    Public Sub UpdatePayments()
    DoCmd.Hourglass True
    DoCmd.SetWarnings (False)
    DoCmd.OpenQuery "PayDetails_COD", acViewNormal, acAdd
    DoCmd.OpenQuery "Paydetails_colected_COD"
    DoCmd.OpenQuery "PayDetails_invoices", acViewNormal, acAdd
    DoCmd.OpenQuery "Paydetails_colected_Invoices"
    DoCmd.SetWarnings (True)
    DoCmd.Hourglass False
    End Sub
    '************************************************* ******* :

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Tip: Get rid of the darn "Chr$(13) & Chr$(10)" stuff. Try using the VBE constant(s) vbCrLf or vbNewLine.

    Make sure the VDE is Referencing DAO 3.6

    Then:
    Code:
    Dim dbse As Database
    Dim rste As Recordset, lin As Recordset, doc As Recordset
    Set dbse = CurrentDb
    Set lin = dbse.OpenRecordset("SELECT * FROM LastInvoiceNumber;")
    
    Set rste = dbse.OpenRecordset("SELECT INVOICES.* " _
    & "FROM INVOICES " _
    & "WHERE INVOICES.Invoice_Num = NULL " _
    & "ORDER BY INVOICES.Job_num;")


  3. #3
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    Make sure the VDE is Referencing DAO 3.6.
    Agree with that and make sure it references it before it references ADO, i.e. is higher in the list. They both have some of the same objects such as database, but treat them differently.

    Oh and the tip about VbCrlf or Newline is good.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •