find next free number - DLookup?

  • Thread starter Thread starter miss031
  • Start date Start date
M

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?
 
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?
 
Use DMax() to get the maximum used number.
Add 1.

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