Getting Value from record based on Date field

[marnieg]

Here is my code and I will explain.

' user selects a date in the combo box
' First I want to see if records exist for the previous day

dtPrev = Me![Combo4] - 1
intPrev = DCount("bau_auto", "BallotUsage", "bau_loc = '" & Me![Combo0] & "' And bau_date = #" & dtPrev & "#")

' if records dont exist create ballot usage for this location and date
' then open the Ballot Usage form for this location


Dim rs As DAO.Recordset
strSQLQuery = "Select * from BallotBatches where bal_loc = '" & Me![Combo0] & "'"
Set rs = CurrentDb.OpenRecordset(strSQLQuery)
While Not rs.EOF And Not rs.BOF
' check if previous date has records to use values to set to last used

**********************
* here is the part that is not working *

' If there is a previous date then I want to get a value from that record for the insert to load the last_used column

If intPrev > 1 Then
intLastUsed = DLookup("bau_last", "BallotUsage", "bau_loc = '" & Me![Combo0] & "' And bau_date = #" & dtPrev & "#")

' Insert row into Ballot Usage based on Ballot Batches with Last Used loaded
CurrentDb.Execute "Insert into BallotUsage (bau_last_used,bau_type,bau_date,bau_loc,bau_pre,bau_low, bau_high) " & _
"Values ('" & intLastUsed & "', '" & rs.Fields("bal_type") & "', '" & Me![Combo4] & "', '" & rs.Fields("bal_loc") & "', '" & rs.Fields("bal_pre") & "', '" & rs.Fields("bal_low") & "', '" & rs.Fields("bal_high") & "')"

I'm not getting a value for the intLastUsed inserted into the record. The insert is working, but not loading a value in bau_last_used field.

 

[marnieg]

Please ignore this request. I figured out my problem with the DLookup, the criteria was not specific enough and it was returning more than one value.