You are here:

Using MS Access/Access 2007 pulling unique number from table


Hello and thank you for all your assistance....I was not able to ask a follow-up question - so I posted a question

Backstory:I am creating a database that will hold check information. The starting checknumber is based on clientid and I need to sequentially increment that starting number based on the last number used for the client from the checks table.

Numerous users can use this database at 1 time

when entering check information on the data entry form, I would like to search for the last check number used in the checks table, based on the clientid listed in the clientid field on the form and increment that last number used by 1 to create a new check number using the first check number available for the client from the checknumber table.


ClientID A last check used was 1000000000 and a new check is being entered for client A so the new check number would be 1000000001.

Client B is having their first check entered so the new check number would be the starting check number 2000000000 without incrementing.

Table and form structure
tblChecknum (holds the clientid and starting check number for the client)
ClientID (Text)
CheckNum (Number) Field size Double
tblChecks (holds the check data)
Client_ID (Text)
Check_Number (Number) Field size Double
frmDataEntry (check entry form)
Client_ID (Name: Client_ID)(control source: tblchecks.Client_ID)
Check_Number (Name: CheckNumber) (control source: tblchecks.Check_Number)

Here is the code:
Here is the code that runs prior to saving..
Private Sub Command100_Click()
On Error GoTo Err_cmdCloseForm_Click
lngStartNumber = DLookup("[CheckNum]", "tblCheckNum", "ClientID = '" & Me.Client_ID & "'") - 1
Me.CheckNumber = Nz(DMax("[CheckNumber]", "tblChecks", "[Client_ID] = '" & Me.Client_ID & "'"), lngStartNumber) + 1

Me.Dirty = False

The issue now is the check numbers are not incrementing. The check numbers are always the starting check number.Here is the resulting check number in the tblChecks Client A I put in two checks both check numbers were the same as the starting check number 1000
Client B I also put in two checks both check numbers were the same as the starting check number 2000

Any assistance would be greatly appreciated.
Thank You

The problem is here:

Me.CheckNumber = Nz(DMax("[CheckNumber]", "tblChecks", "[Client_ID] = '" & Me.Client_ID & "'"), lngStartNumber) + 1

It should be:

Me.CheckNumber = Nz(DMax("[Check_Number]", "tblChecks", "[Client_ID] = '" & Me.Client_ID & "'"), lngStartNumber) + 1

The field name in tblChecks has the underscore. So the expression:
DMax("[CheckNumber]", "tblChecks", "[Client_ID] = '" & Me.Client_ID & "'")
will always return a null, therefore substituting lngStartNumber.

I'm also concerned that the Check_Number field is a Double instead of a Long Integer. If you want to use double instead of Long Integer, then change lngStartnumber to dblStartNumber and make sure you Dim the variable properly.  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.