DMax syntax

[duug77]

The code is as follows:

Private Sub cmd_ScopeID_Click()
Dim db As Database
Dim rst As Recordset
Dim i As Integer


Set db = CurrentDb
Set rst = db.OpenRecordset("tdatScope")
While Not rst.EOF

If IsNull(rst!scopeID) Then
rst!scopeID = Nz(DMax("[scopeID]", "tdatScope", "[room]= '" & rst!room & "'") + 1, _
[DLookup("roomOrder", "tdatRooms", "[room]='" & rst!room & "'") * 1000])
End If
rst.MoveNext
Wend
End Sub

I have basically learned Access by trial and error. So I think there's something I just don't understand about parsing through the database and using the DMax function. The routine is just supposed to find the next possible ID value for a given room if the field is blank. I have tried several variations of this code, but it only changes which error comes up.

scopeID and room are both fields within tdatScope.

Any help would be appreciated.

 

[boblarson]

Doesn't look like the DMax is the problem. It looks more like your part here is the problem:

[DLookup("roomOrder", "tdatRooms", "[room]='" & rst!room & "'") * 1000])

There shouldn't be square brackets around the DLookup.

DLookup("roomOrder", "tdatRooms", "[room]='" & rst!room & "'") * 1000)

 

[duug77]

It's also my first try using a variant by the way, and I thought the square brackets were the syntax for the variant. The code never gets that far. It always hangs up on the DMax. As posted, I get an error 2465 Cant' find the field...