conditional autonum 1

[betachristopher]

I am creating an access database that will be used to create unique account numbers. This is all being done with local tables, no linking to tables on a server. This is a single user database. I am not familiar with VBA although I'm guessing I will need to be to do this.

In the data entry form that I have made, there are two combo boxes, each linking to a different table. One table contains Regions (example: Midwest), and the other table contains Client Types (example: Commercial). Each record in these tables has a unique ID (Midwest = 1, Commercial = 1). I am using both the unique ID and the Description columns in the combo boxes. When a new record is created in this form it is added to an Account table. What I I need your help with is creating a unique account number that uses the IDs from the Region and Client Type tables as the first two numbers in the account number. Example, if Midwest(1) is chosen as the region and Commercial(1) as the client type, I want the account number to be 110001. If Midwest(1) is chosen as the region and Private(2) is chosen as the client type, the account number should be 120001. How can I use autonum or something similar that creates this number? Thank you for your help.

 

[lameid]

I would not do this.

Simply I would add an autonumber to the Client table.

If people want to pretentend like the account number includes the region and client type you could put in in a query for display purposes...

Display Account: [ClientType] & [Region] & Format([Account],"0000")

The problem with this of course is that it only allows for 10,000 clients formated like this (0 to 9,999).

Putting multiple pieces of information in a filed like that goes against Data Normalization rules. Mainframes often did this as a way to cheat and add fields to a fixed system. Most modern database systems can add fields or columns to a table without that much difficulty.

You could also always display the client type and region with the account if you wanted.

 

[betachristopher]

I am looking for a solution similar to thread702-1218778 the difference being I am using two combo boxes.

 

[lameid]

The easiest and maybe not the fastest is to use the below procedure.

Sub cmdGetNewAccount_Click

ME!txtAccount = Me!cboRegions & Me!cboClientType & _ 
     Dmax("Val(Mid(Account,3,Len(Account)-2))", _ 
     "Client", "Account Like """ & Me!cboRegions & Me!cboClientType & "*""")

End Sub

The above assumes you are going to click a button to populate the account.

The button is named cmdGetNewAccount.
The comboboxes are named cboRegions and cboClientType.

Account is the name of the field you are storing the data in and the table is Client.

If you make a button named as such, get its properties, change the on click event to [Event Procedure], and hit the little button next to the drop down you should see the top and bottom line of code above. Just put in the stuff in the middle and save. It should work unless you named things differently in which case just replace them.

 

[betachristopher]

Thank you lameid! Things are working as needed except that it is returning a 3 digit number instead of 5 digit. It creates the new records as 111, 112, etc, but I need them to be 110001, 110002.

 

[lameid]

Woops... forgot the format...

Sub cmdGetNewAccount_Click

ME!txtAccount = Me!cboRegions & Me!cboClientType & _ 
     Format(Dmax("Val(Mid(Account,3,Len(Account)-2))", _ 
     "Client", "Account Like """ & Me!cboRegions & _ 
     Me!cboClientType & "*"""),"0000")

End Sub

 

[betachristopher]

THANK YOU! This is what I needed exactly. The only thing I added was a +1 to the last line to get it to increment each new recrod:

Me!cboClientType & "*""")+1,"0000")

Thank you so much. I really appreciate your help and patience.