You are here:

Using MS Access/Access 2007 pulling unique number from table

Advertisement


Question
QUESTION: Afternoon....I am creating a database to hold check information...I would like to see if it is possible for the check number from the check number table to update the check table using the relationship of clientid from both tables and sequentially incrementing the check number + 1 from the last check number used starting with the listed checknumbers in the table.

I have a table for check numbers per client  

client 1 could start with 0001
client 2 could start with 1000
client 3 start with 2000.

The Data Entry form is where the checks will be keyed and housed on the checks table.


Example
client 2 has one check where the checknumber.clientid = check.clientid and the first check number is 1000
Client 2 then has a second check where the checknumber.clientid = check.clientid and the check number is the last check previously used on the check table + 1 so the check number for check 2 is 1001

Please let me know if having a copy of the database would assist and I can send.

Thank You in advance for all your assistance

ANSWER: Please see my blog (ScottGem.wordpress.com) on Sequential Numbering. It has techniques to help you do what you want.

If you have any questions after reviewing the blog let me know.

---------- FOLLOW-UP ----------

QUESTION: Thank You for the information, I have read the sequential numbers blog, but the scenarios presented do not seem to match what I am trying to do..

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

I have 3 tables holding the information
checks table (holds the check information)
client table (holds the clientid and client name)
checknumber table (holds the clientid and starting check number for the client)

and I have 2 forms
Data Entry form (where the check information will be entered)
inquiry form (where they can search for individual check information)


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.


example:

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.

Answer
I don't understand why you think the techniques in my blog don't apply! They deal with exactly what you want to do with one slight modification, the starting number.

To accommodate that I would have this line of code:

lngStartNumber = DLookup("[StartCheckNumber],"tblCheckNumbers","[ClientID] = " & Me.clientID)-1

Then

Me.CheckNumber = Nz(DMax("[CheckNumber]","tblCheckRegister","[ClientID] = " & Me.clientID),lngStartNumber)+1

Finally

Me.Dirty = False

to save the record. This is basically what my blog tells you.

The reason for the -1 for lngStartNumber is because you are still going to increment by 1 if its a new client.

You would then need the table of check numbers for the starting number.

If you prefer to store the last number used in a separate table, that is also a viable technique. its not a technique I prefer however. That involves using a DLookup to pull the last used number and then an Update query to save the new number.

In either case, you have to be careful that you increment the number just before you save the record.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.