auto fill a field in subform with the recordset count

  • Thread starter Thread starter judy jones via AccessMonster.com
  • Start date Start date
J

judy jones via AccessMonster.com

I want to automatically fill a field in my subform with the record number
that shows up in the bottom of the subform record navigation.

What I have is a form/subform that is used for both updates and new
entries. The links between the 2 tables (forms) is working well. But to
save the user a field entry (& possible mis-entry), I want the subform to

autofill FIELD1
with the record number (shown at the bottom of the subform)
IF
field2 changes
AND
FIELD1 = zero

background:
FIELD1 in the subform does not have a tab stop, so if the user is
attempting to create a new entry in the subform, they will enter a change
in FIELD2. When FIELD2 changes (but before tabbing to the next field)
FIELD1 changes to a zero (because it is defined as numeric & a new table
entry is being made). It is at this point that I want to autofill FIELD1
with the record number.

So a 2nd question is:
Does the autofill code (as described above) go in the code builder of On
Change of the Event tab for FIELD2? by the way, FIELD2 is a combo-box.

thanks for any help,
judy
 
Judy

The "number that shows up in the bottom ... navigation" window is a counter,
not a record ID. As you have probably already found, the number of subform
records can differ for each main form record.

It sounds a little like you are trying to come up with a "sequence number",
or a "custom autonumber". If so, try checking at Google.com and/or the
mvps.org/access website using these expressions.
 
this is correct.
Once I come up with that expression. . .

Does the "custom autonumber" code (for FIELD1) go in the code builder of On
Change of the Event tab for FIELD2? by the way, FIELD2 is a combo-box.
 
Judy

You'll need to decide which event on your form you want to have "trigger"
the sequence/custom numbering routine. If I were using a combo box, I'd use
the AfterUpdate, and then, only if there wasn't a sequence number already
created.
 
Back
Top