find next free number - DLookup?

[miss031]

I have the following code that tests if a number is already used, but I would
like to modify it so that a user can click a command button and have the
combo populate with the next free number.

--------------------------------------------------

strWhere = "(bidder_ID = " & Nz(Me.txt_bidder_number, 0) & _
")And (sale_ID = " & Nz(Me.txt_sale_ID, 0) & ")"

If IsNull(Me.txt_cont_bidder_ID) = True Then

varResult = DLookup("bidder_ID", "tbl_cont_bidder", strWhere)
'MsgBox "Checking candidate number"

If Not IsNull(varResult) Then
MsgBox "This bidder number is already in use. Please choose
another."
Me.frame_perm_bidder = Null
Exit Sub

Else
'MsgBox "No Matches - Query would run here"
End If
End If

---------------------------------------------------

That code checks that the entered bidder_ID does not already exist in
[tbl_cont_bidder] with the current [sale_ID].

So what we are looking for is to populate with the next [bidder_ID] that
does not have an associated record in [tbl_cont_bidder] for the current
[sale_ID].

Clear as mud?

 

[Rod Behr]

Try using DMax("bidder_ID","tbl_cont_bidder") + 1

That will return the highest bidder_ID + 1. It won't give you any unused
bidder_ID values lower than the maximum though.

Would this work?

 

[Allen Browne]

Use DMax() to get the maximum used number.
Add 1.

DMax() works like DLookup() does, but you won't need strWhere.