You are here:

Using MS Access/Sequential numbering with additional criteria

Advertisement


Question
QUESTION: I am creating a database for event registration and I have found the instructions from your blog to create straight sequential numbering very helpful. I used this code and it assigned numbers correctly:
Me.txtAssignedNumber = Nz(DMax("[AssignedNumber]", "datatable"), 0) + 1
Me.Dirty = False

Then I wanted the number to begin at 1 again for the second event registration. I used this code and now I get a syntax error:

Me.txtAssignedNumber = Nz(DMax("[AssignedNumber]", "datatable","[Event]= '"& Me.[txtEvent]&"'"), 0) + 1
Me.Dirty = False

The Event field in the table is a lookup field with 4 Alpha-numeric options. I am not sure what I have done incorrectly.
Thank you in advance for your help.

ANSWER: First, I do NOT recommend using lookup fields on the table level. This is one of the reasons. What is the data type of the Event field? What is the RowSource of the lookup field? What type of control is txtEvent?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

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

QUESTION: I hope I am understanding the terminology and answering correctly. The data type of the Event field is "text" (listed on the design view of the table). The RowSource of the lookup field is " "OIIC-2013";"SoIC-2013";"WBIC-2013";"TIC-2013";"CBIC-2013" " (as seen in the properties of that control when the form is opened in design view). The txtevent control is a ComboBox (as listed on the properties).

Answer
Yes you understood the terminology correctly. There are some problems with that setup that I'll go into in a moment.

But if the info is correct then this code:
Me.txtAssignedNumber = Nz(DMax("[AssignedNumber]", "datatable","[Event]= '"& Me.[txtEvent]&"'"), 0) + 1
should work. What is the exact error you received.

Now the reasons the setup is not a good idea is what happens when you add events? Each time you add an event you need to change the table and the form. What you should do is create an Events table like so:

tblEvents
EventID (PK Autonumber)
EventCode
EventName
EventDate
other info about the event.

Then Change the Event field in datatable to EventID>Long Integer. Remove the lookup from the field and use the Combobox wizard to create a combo on your form that lookups up the Event code from tblEvents.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA  

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.