Numbering Question

[accessuser1308]

My current database contains two tables. The first has a number field
RDTBarcode. The second table is linked to this table via a lookup field,
also labled RDTBarcode. The second table also has a number field
DaughterBarcode.

I have a form built off of Table1 that contains a subform built off of
Table2. The subform data is linked to the main form data via the RDTBarcode
fields. I would like the DaughterBarcode field to begin numbering at 1 for
each RDTBarcode and increase sequentially with each record that is added for
the single RDTBarcode. Is this possible? Any help would be greatly
appreciated.

Thank you

 

[JString]

Off the top of my head... if Table1 and Table2 are linked by the unique field
'RDTBarcode', Table2 could have an additional unique field set to an
autonumber that increases sequentially.

 

[JString]

Also, if you want to do it this way, make sure that RDTBarcode is set to
allow duplicates in Table2 only.

 

[John W. Vinson]

My current database contains two tables. The first has a number field
RDTBarcode. The second table is linked to this table via a lookup field,
also labled RDTBarcode. The second table also has a number field
DaughterBarcode.

I have a form built off of Table1 that contains a subform built off of
Table2. The subform data is linked to the main form data via the RDTBarcode
fields. I would like the DaughterBarcode field to begin numbering at 1 for
each RDTBarcode and increase sequentially with each record that is added for
the single RDTBarcode. Is this possible? Any help would be greatly
appreciated.

Thank you

You'll need to use a bit of VBA code in the form to do this. One simple way is
to put code in the subform's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!DaughterBarcode = NZ(DMax("[DaughterBarcode]"), "Table2", _
"[RTDBarcode] = " & Parent![RTDBarcode])) + 1
End Sub

This will look up the highest existing DaughterBarcode (if there is one) for
the main form's current RTDBarcode, return 0 if there is no such value, and
add 1 to that.

Note that Lookup Fields can be deceptive. What you see is NOT what you get.
See http://www.mvps.org/access/lookupfields.htm for a critique.