Good morning all,
I need some assistance with this please!
I am trying to make an auto bill so that when I open my main menu, it runs code below to generate a bill in my table.
I want to loop through the query, and if the Bill Date from query = the Bill Due from the table where AccountID = AccountID and AccountDetailID = AccountDetailID Then
The Bill has been created, if There is no date that matches the criteria, then I want to run the sql statement.
I have never created a loop code so bear with me. Have looked over many examples and such but having issues with writing the DLookUp code. I really need some assistance on this.
I put in red the part that I don't know how to write. I think everything else is in order?
Code:
Sub Something()
' I want to put this on the open event of frmMainMenu so it fires everytime I open main menu!
On Error GoTo ErrorHandler
Dim rs As DAO.Recordset
Dim strSQL As String
Set rs = CurrentDb.OpenRecordset("QryBillPay")
With rs
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
' I need to do a DLookUp I think with an IF statement Here? DlookUP(BillID, tblTransBill, DateReceived) Then
'If BillDate = DateReceived WHERE AccountId = AccountId AND AccountDetailID = AccountDetailID Then
'Next
'Else
strSQL = "INSERT INTO tblTransBill ( AccountID, AccountDetailID, BillType, MinAmountDue, DateReceived, BillDue, ReceivedFrom ) " & _
"VALUES (" & AccountID & ", " & AccountDetailID & ", '" & DetailAccountType & "', " & MinAmtDue & ", #" & BillDate & "#, #" & Next_Due_Date & "#, '" & BusinessName & "')"
'Debug.Print strsql
db.Execute strSQL, dbFailOnError
.MoveNext
Wend
End If
.close
End With
ExitSub:
Set rs = Nothing
Exit Sub
ErrorHandler:
Resume ExitSub
End Sub
Thank you
Dave