Using MS Access/Increment with a button


QUESTION: I have a split form with the following fields:
Part Number
Group Number
Serial Number

The Part Number is always unique and I fill it with a combo box.
The Group Number can be used many times.
The Serial Number is unique and fills with a combo box that also auto fills Series.

My problem is I would like Group Number to default to the last number used so I can assign several part numbers and Serial numbers to the same Group Number. But then when I click a New Button I get a new form and Group Number incrementing by one.

My button does bring up a new form but I cannot work out the number part. I can get the Group Number to increment but then it I can't use the Group Number more than once.

Any help would be appreciated.

ANSWER: Can you explain what this form is for and the relations with your tables?

If you just need to increment the Group Number you might find my blog on sequential numbering helpful.

Hope this helps,

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


Thank you for getting back to me. The database is to assign a part number to a group number, and a group number to a serial number. Each part number is unique, but several serial numbers (or forms) can be used to make a part. The group number will group like serial numbers together. Pretty simple but there are thousands of part numbers and I am trying to make data entry easier.

tblPart -(Fills from Excel) Part Number.
tblForms -(Fills from Excel) Serial Number, Series.
tblGroup - GroupID, Group Number
tblAssign - ID, Part Number, Group Number, Serial Number, Series.

qryForm - appends to tblAssign
qryPart - appends to tblAssign

frmAssign - Split form.
Part Number - is a combo box.
Group Number - I would like this to increment on a click of a "New Form" button, but first default to the last number since several Serial Numbers may be assigned to the same Group Number.
Serial Number - Combo box
Series - Auto-fills from Serial Number combo box.

If I delete the Group ID and use Group Number as AutoNumber I can get it to increment, and once I did get it to default to the last number. But I can't get it to increment with the "New Form" button.

ANSWER: You didn't answer me completely. But let me see if I'm understanding you. tblAssign is actually a junction table in a many to many relationship. So you are selecting a part #, assigning it a group and giving it a serial number, correct? If the Series is auto assigned it should NOT be in tblAssign as it is already connected to the Serial Number.

If I'm following the work flow here, you need to create a new group and then assign it to several Part numbers at the same time assigning a serial number to each part in the group, correct?

Then what I would do is create a main form bound to tblGroup. Use an autonumber field for GroupID. I don't know if you need anything else in tblGroup to describe a group.

Then have a subform on the form bound to tblAssign. Have a hidden control for GroupID, and combos for Part Number and Serial Number. Link the Subform to the Mainform on GroupID. Then you add a new record in the main form, which will increment the GroupID for you. As you add records to the subform, The GroupID will be automatically populated.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

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


I am trying what you suggested and I am confused on what is the hidden control for GroupID. Is that the button?

No the hidden control is on the subform so the subform can be linked to the main form. There is no need for a button in this example. As you add records to the subform (selecting Part and serial numbers), the GroupID (currently displayed on the main form) will populate automatically.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




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


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

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

Brooklyn College BA

©2017 All rights reserved.