Microsoft Access - Autonumber groups

Asked By D on 10-Mar-11 08:50 PM
Access 2007:  I have a Reservations database with a field for Table Number (2 digit) and and field for Bidder Number (4 digit).  There will 40 tables with 10 seats each.  Every person at each table will have an autogenerated Bidder Number based on their Table Number (first 2 digits of Bidder Number):  e.g., Table 01 will have Bidder Numbers 0101 thru 0110, Table 02 will have Bidder Numbers 0201 thru 0210.  The Table Numbers will be manually input for each person, what I need to happen is the Bidder Numbers Autogenerate for each person when their Table Number is input.  Suggestions?

 
Pat Hartman replied to D on 11-Mar-11 02:12 PM
In the AfterUpdate event of the table number field:

BidderNumber = TableNumber & Format(Nz(DMax("SeatNum","tblBidders","TableNum = " & Me.TableNum),0) +1,"00")

The Nz() takes care of adding the first bidder.  The DMax() would return null and Nz() converts the null to zero.  Then 1 is added to the maximum seat number found to generate the next seat number.
The Format() gives you a leading zero if necessary.

This does not account for gaps caused by moving people from table to table.  You'll need more complicated code if you want to look for gaps.
D replied to D on 11-Mar-11 02:37 PM
Thank you for your response.  It seemed easy enough, but I got the followingerror message "The expression AFter Update you entered as the event property setting produced the following error:  The object doesn't contain the automation object 'Me.'.

Pat Hartman replied to D on 13-Mar-11 04:14 PM
To place code in the AfterUpdate event, click at the right edge of the box on the builder button.  If you are given a choice select the code option.  The builder builds the procedure shell and you place the suggested code inside it.  Please post your code if you have any further problems.

D replied to D on 14-Mar-11 06:20 PM
Thank you so much.

However, we have now decided performing this function on the form is not the right place to do it. 

Can this function be performed in a query:  e.g., an expression or something that when a Table Number is entered while in the Query the Bidder Number is auto-generated as previously described???

Thanks in advance.