Loop with a DLookup/If

[d9pierce1]

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?

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

[Welshgasman]

I believe if you just add that table to your query joined on your criteria, then you will only ģet the records that match in the first place?

Plus no need for that movelast/movefirst unless you want to know the record count.
Just use Do while Not Rs.EOF.

[d9pierce1]

That Query changes by the due date, If I dont use a condition then it will create a new bill of the same everyday, correct?

If the bill already exist, then I don't want it repeated!

[d9pierce1]

Maybe I just need to do an update query to put the records from the query into the table when run that are different?

[Welshgasman]

[QUOTE=d9pierce1;510212]That Query changes by the due date, If I dont use a condition then it will create a new bill of the same everyday, correct?

Sorry, got it the wrong way around.

Use a subquery that selects records not in the query using those conditions and Date field = Null or try an Unmatched query wizard, which will pretty much do the same thing?
If you want to contunue your way, then perhaps use DCount() instead. Just has to be 0 for you to update your table.

So

If Dcount(your parameters here) = 0 then
    do your update
end if
MoveNext

Do use Debug.Print to enure your criteria is correct for the DCount() and your SQL for the Update.

[d9pierce1]

Hi Welshgasman,
Thank you for the input and I came up with a append query that does put the recordes in, however I cant figure out how to do records if the date changes in the query?
See Photo...
I put in Is Null but what i think i really need is someway to determine if the Date is null or does not match? I dont know.

[Welshgasman]

So perhaps use the NZ() function on the field and use 01/01/1900 if null? That way a null date will ever match yourdate?

[Micron]

Copy/paste a few rows of table data that pertains to the problem and show desired results? I'm not really following why this would be difficult. If the bill was paid, there is a DateReceived value (which would make IsPaid redundant). If it was issued but not paid, then there is a record in tblTransBill but one/both of those fields are null? If it wasn't issued then there is no record? A query should be able to handle those conditions but perhaps I'm not grasping what's going on.

[d9pierce1]

Hi all,
Here is the table that it appends to.

So, the query brings up the records that have a bill due in the next 30 days from the date it is due. This data will change as days go by.
What I want to do is put this data in the table so that it lets me know a bill is due and when. I have a list box on main menu that pulls data
from that table to show me a bill is due and when and is not yet paid. When I pay the bill it goes away in my list box. That all works great.
What I am tring to do with this is take the data from query and put in table and when new data is queried, then it creates new records. The
only thing I could find to determine if its a new record is the BillDate & DatedReceived = If those dont match, then its a new record. I need to figure out a
way that if the BillDate & DatedReceived dont match, then its a new record and puts only those records in the table, not duplicating the existing records.
Hope this makes some sense of it.

[Micron]

Hope this makes some sense of it.

Not for me. If you can return records of unpaid bills due in 30 days or less in a query, you have them in a table so why do you need to create what seems like duplicate information elsewhere? IsPaid seems like pretty useless information to me. Why not DatePaid, then you not only know it's paid, you know on what date. Nor do I know if you're paying these bills or are billing someone - parts of your posts read both ways. Regardless, it seems to me that you're going about this the wrong way. Maybe clear up what you're doing rather than asking how to loop or create records that you probably don't need to create?

[d9pierce1]

To clarify, How do I get this query to only show records Where the AccountDetailID & BillDate from Query and the AccountDetailID & BillReceived from tblTraansData Dont Match?

If AccountDetail ID is 13 and BillDate is 3/12/2023 (In Query) and the AccountDetailID is 13 & DateReceived (tblTransBill) is 3/12/2023 then that record exist all ready
If AccountDetail ID is 13 and BillDate is 4/12/2023 (In Query) and there is no AccountDetailID with 13 And date received 4/12/2023 then its a new record.

[d9pierce1]

Hi Micron,
What I am attempting is to automatically create a list of bills in the next 30 days via query and put in my table that the records have been recorded I am not looking to pay the bill, just create it so I know its there.
The records in that table are on a listbox on my main menu. Double Click event when I am ready to pay it will open a form to do so. That I have down.
What I dont have down is how to look up the existing records in the table so I dont repost them with this query. As that query changes, i want it to add the new data to the table.

Query Runs
Inserts Data into table,
Table creates a list box
List box opens form to record the bill has been paid,
Once Paid, Bill in list box goes away
Repeat

The only thing that changes in the query is the Next_Due_Date and the BillDate so if the BillDate Matches the ReceivedDate, then nothing, else, adds a new record

[Micron]

How do I get this query to only show records Where the AccountDetailID & BillDate from Query and the AccountDetailID & BillReceived from tblTraansData Dont Match?

Did you try the Unmatched Query Wizard yet?

[Welshgasman]

Looking at your ERD I believe you can just do it with an Unmatched query and then include the date criteria?, as aAccountDetailID should be unique, should it not

I would get a Select query working first, then turn it into an Append query.

I have to literally play with a query to get it working, I just cannot write it in one go, if it is this complex.

[d9pierce1]

Thank you all,
I have the select query working great! Going to make it an append query!
Will let you know.
That was awsome!