I really need some help on this query and code

[Matt Brown]

I know i keep on about this but i really need some help.

I posted this last week and some of you tried to help but still it doesn't seem to work.
I converted most of the Danish to English just to help last week but now i will post the lot as is.

I must keep the code as is apart from any alterations to get it to work.

What it seems to be not doing is not producing the next LobeNr (SerialNo) after looking at the tables.
If i claer the tables it places the first one in (1)
The next time i try it fails due to craeting duplicate PKs.
So it tells me the check for next LobeNr part isn't working correctly.

Please, Please can anyone help or see any problems why this shouldn't work.

Code is:

Set db1 = CurrentDb()
SQL = _
"  SELECT MAX(LobeNr) " & _
"  FROM Tilbud  " & _
"  WHERE CustomerNo = '& Trim(Forms!main!CustomerNo)'" & _
"  AND Dato = " & Format(Now(), "yyyymmdd") & ";"



Set rs1 = db1.OpenRecordset(SQL, dbOpenSnapshot)
If rs1.BOF And rs1.EOF Then
    LobeNr = 1
Else
    rs1.MoveFirst
    If IsNull(rs1.Fields(0)) Then
        LobeNr = 1
    Else
        LobeNr = rs1.Fields(0) + 1
    End If
End If
rs1.Close


SQL = _
" SELECT CustomerNo, Dato, LobeNr, [Customer Ref] " & _
" FROM TilbudsHeader " & _
" WHERE CustomerNo =  '& Forms!main!CustomerNo'" & _
" and Dato = " & Format(Now(), "yyyymmdd") & " " & _
" and LobeNr = " & LobeNr

Set rs1 = db1.OpenRecordset(SQL, 2)
    
rs1.AddNew
rs1!CustomerNo = Trim(Forms!main!CustomerNo)
rs1!Dato = Format(Now(), "yyyymmdd")
rs1!LobeNr = LobeNr
If Len(Trim(Me.customerRef)) = 0 Then
    rs1![Customer Ref] = " "
Else
    rs1![Customer Ref] = Left(Me.customerRef, 50)
End If

rs1.Update
rs1.Close

I don't really want to use DMax as i need to stick to the same code.
The coding was done by a guy who is now unreachable so i cannot ask him but i have seen this working but won't work now!

 

[Pat Hartman]

" AND Dato = " & Format(Now(), "yyyymmdd")

should be simply -
" AND Dato = " & Date()

Now() returns date and time whereas Date() returns only date. When you Format() a date you turn it into a text string and this can cause problems with any conditional statements. I don't know which date style Denmark uses but I would guess it to be day/month/year rather than month/day/year so be very careful when using text date strings.