SQL Syntax Problem

[DS]

I'm trying to run this and it doesn't seem to be working It works fine
until I put the LineID into it. I need the DMax statement to check the
LineID if its null, if not then make it +1

CurrentDb.Execute "Insert into SalesDetails (LineID,SalesID,ItemID)
Values(Nz(DMax(LineID, SalesDetails), 0) + 1 ," & Me.SalesID & "," &
Me.List215.Column(0) & "))"



Thanks
DS

 

[SusanV]

Assuming you have already dimmed and instantiated LineID:

If IsNull(LineID) then
LineID = '1'
Else
LineID = LineID + 1
End If

CurrentDb.Execute "Insert into SalesDetails (LineID,SalesID,ItemID)
Values(LineID, " & Me.SalesID & "," &
Me.List215.Column(0) & "))"

 

[Van T. Dinh]

Try:

CurrentDb.Execute "Insert into SalesDetails (LineID,SalesID,ItemID) Values
(" & _
Nz(DMax("LineID", "SalesDetails"), 0) + 1 & _
", " & Me.SalesID & ", " & Me.List215.Column(0) & ")"