Using MS Access/Increment with a button

Advertisement


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

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,
Scott<>
http://scottgem.wordpress.com

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

QUESTION: Hi Scott,

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.

Tables:
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.

Queries:
qryForm - appends to tblAssign
qryPart - appends to tblAssign

Form:
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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

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

QUESTION: Scott,

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

Answer
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,
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.