Microsoft Access - Autonumber groups

Asked By D
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
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
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
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
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.
Create New Account
help
Access 2007 MSACCESS.EXE consuming RAM DataBase Hi, My firm has a system developed in Microsoft Access 2003 that is deployed as .MDE's by opening and saving using Microsoft Access 2007. The system is connected to an SQL Server 2005 Database using ODBC. The Microsoft Access 2007 system is deployed on a Windows 2008 R2 64 Bit Application Server and
Access 2003 runtime conflicts with Access 2007 DataBase I have uninstalled access 2003 runtime but every time I start Access 2007 I get a message stating that it is installing MS Access 2007 which is followed by Microsoft office is configuring access 2007. It does this every time
Access queiries DataBase What formula do I use to calculate age in Microsoft Access 2007 Access Getting Started Discussions Access 2007 (1) Office (1) DateOfBirth (1) DateDiff (1) Queiries (1) Mmdd (1) Try searching on-line or at mvps.org / access for "age". Regards Jeff Boyce Microsoft Office / Access MVP DateDiff("yyyy", [DateOfBirth], Date()) - IIF(Format
Access 2007 Trial and Access 2007 Runtime Conflict DataBase I have MS-Office 2007 Small Business Edition under Vista installed on my laptop. It included Access 2007 trial. I do not need the trial or the full version of Access 2007 on this machine. I have the full Access 2007 on my desktop and do
MDB (Access 2002-2003) file format in Access 2007 DataBase Access Learner. Access 2007. If I create a database in Access 2002-2003 file format in Access 2007 and then split the mdb file into BE and FE, can I use copies