Default 1st entry in subform to #1, auto increment next entry to #

D

David K.

Couldn't find a post that matches or makes sense to me. Sorry if this is a
repeat.

I have a form based on two tables that are used to track Non Conformances.
The main table contains the general job data such as the customer, part
number, qty made, etc. The secondary table contains all of the individual
defects such as scratches, dimensional issues, damage, etc. I did it this
way because one NC can contain multiple types of defects.

The two tables are connected by the NCNumber in a one-to-many relationship.
The NCNumbers begin at 1000 and increment by one using the following in the
following in the ON Current event of the main form. This works fine.

Private Sub Form_Current()
Me.NCNumber.DefaultValue = _
"""" & Nz(DMax("NCNumber", "tbl_NCs", strCriteria), 0) + 1 & """"
End Sub

I put the tbl_NCDetails table into my form as a subform which as I stated is
associated to the main form by the NCNumber with a one-to-many relationship.

What I would like to do is to have the first defect detail entry to default
to #1 upon activating that page in my form. As soon as I start typing the
defect info in #1 I would like the next entry to automatically insert #2, and
so on. I tried the code above that I used on the NCNumber but it doesn't
work properly. Perhaps I'm putting into the wrong event.

With everything working properly my tables will end up looking like this:

tbl_NCs (main)
NCNumber 1000.....
NCNumber 1001.....
etc.

tbl_NCDetails (supporting)
NCNumber 1000 Defect 1.........
Defect 2.........
Defect 3.........
NCNumber 1001 Defect 1........
NCNumber 1002 Defect 1.......
Defect 2.......

Hope this makes any sense to you guru's. Thanks in advance.
 
M

Marshall Barton

David said:
Couldn't find a post that matches or makes sense to me. Sorry if this is a
repeat.

I have a form based on two tables that are used to track Non Conformances.
The main table contains the general job data such as the customer, part
number, qty made, etc. The secondary table contains all of the individual
defects such as scratches, dimensional issues, damage, etc. I did it this
way because one NC can contain multiple types of defects.

The two tables are connected by the NCNumber in a one-to-many relationship.
The NCNumbers begin at 1000 and increment by one using the following in the
following in the ON Current event of the main form. This works fine.

Private Sub Form_Current()
Me.NCNumber.DefaultValue = _
"""" & Nz(DMax("NCNumber", "tbl_NCs", strCriteria), 0) + 1 & """"
End Sub

I put the tbl_NCDetails table into my form as a subform which as I stated is
associated to the main form by the NCNumber with a one-to-many relationship.

What I would like to do is to have the first defect detail entry to default
to #1 upon activating that page in my form. As soon as I start typing the
defect info in #1 I would like the next entry to automatically insert #2, and
so on. I tried the code above that I used on the NCNumber but it doesn't
work properly. Perhaps I'm putting into the wrong event.

With everything working properly my tables will end up looking like this:

tbl_NCs (main)
NCNumber 1000.....
NCNumber 1001.....
etc.

tbl_NCDetails (supporting)
NCNumber 1000 Defect 1.........
Defect 2.........
Defect 3.........
NCNumber 1001 Defect 1........
NCNumber 1002 Defect 1.......
Defect 2.......


Well, it makes sense, but doing it that way has a couple of
issues, one very serious and the other might be considered
cosmetic or even misleading.

The serious problem is that using the DefaultValue leaves
the door wide open for multiple users to get the same
number. To avoid this problem, regardless of its
probability, you should use code in the form's BeforeUpdate
event:

Me.NCNumber = _
Nz(DMax("NCNumber", "tbl_NCs", strCriteria), 0) + 1

While this makes it extremely unliky for two users to get
the same number, you will not see the number until the
record is saved.

The cosmetic problem with your approach is that the next new
record will display the same value as the record you are
currently adding. That really is not a problem because when
you start to enter data in the next new record, the number
will be recalculated correctly. OTOH, using the form's
BeforeUpdate event doesn't show the number while you are
editing so there can be no confusion about it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top