Automatically Advance Subform To New Record If Date Field Is Not The Current Date

[chemguy120]

I have a master form (Account) with a subform (Reference) that are linked via the field AccountID. What I am trying to do is, when the form is opened, have the Reference subform automatically advance to a new record only if the ReferenceDate field is not the current date. If it is the current date, then it should stay on that record. I've tried a cursory search but didn't see any solutions jump out at me. If there is a similar post and solution, please point me in the direction! If not, I sure could use some help. Please note, I'm somewhere between novice and beginner in my coding knowledge.

[Micron]

Where is ReferenceDate? Since subforms load first, if date field is on main form, you'll have to run code from main form, not subform.
If it's on the subform shouldn't be too hard. Did you try to set focus to subform first then DoCmd.GoToRecord , , acNewRec

[chemguy120]

Where is ReferenceDate? Since subforms load first, if date field is on main form, you'll have to run code from main form, not subform.
If it's on the subform shouldn't be too hard. Did you try to set focus to subform first then DoCmd.GoToRecord , , acNewRec

Yes, the ReferenceDate is on the Reference subform. The form opens to the last record. What I need to happen is that if the ReferenedDate is not the current date it should advance to a new Reference record. If it is the current date it will stay on that same record.

[Micron]

then perhaps in the subform load event:

If Me.ReferencedDate <> Date Then DoCmd.GoToRecord , , acNewRec

There could be an issue that would need to be addressed if your date field contains time values as well.

[chemguy120]

I gave it a go, but it doesn't seem to be working. It should not have time values, just the date. I do have the ReferenceDate field on a new record automatically input the current date so I do not know if that may have any impact. I didn't think it would since the default input should only trigger on a new record.

[chemguy120]

Hold the phone! I put it as an On Current event rather than On Load and it worked like a charm!

[Micron]

Possible issue with that is that the current event fires every time the selected record is changed, so that might cause an issue - which is why I didn't suggest it. Surprised that form load event didn't capture the date in the control. Perhaps insert a temporary message box to test load event?
Msgbox me.ReferencedDate

I notice that the name of the field in your posts isn't consistent but I guess since you got it working in at least one event, that's not the issue.
EDIT - If the current event becomes an issue, possible fix (if it can't be determined why load event didn't work) could be to figure out another event that will work well.

[chemguy120]

Sorry about the spelling errors. I'm working on a couple things at the same time.

I put in the message box code and the box pops up with what the ReferenceDate should display on a new record, but the form stays on the current record. I checked the table and no new reference records were created.

The full set up for the form is this:
- Account master form that just selects the correct account. All the entries on that are automatic defaults and the user does not change anything.
- Reference subform tied to the Account via the AccountID field. The subform loads to the last record and only contains ReferneceDate and ReferenceNumber fields. On a new record the ReferenceDate auto-fills with the current date and the ReferenceNumber is auto-generated. The user only interacts with the date field if necessary.

The Reference subform contains another master/sub form set:
- Shipping master form that contains fields for Tracking and PackType.
- Item subform is tied to the master via the ShipmentID field and contains a SerialNumber field and a ReferenceID hidden and locked field that auto-fills with the ReferenceID from the Reference form.

I wouldn't think there would be anything in there preventing the code from working on the Load event, but one never knows.

[Micron]

I'm cooking dinner right now (as usual) so will have to digest that afterwards. But while I'm waiting 3 minutes for the timer to go off, I'll tell you this:

I checked the table and no new reference records were created.

When you move to a new record, it's like being presented with an empty glass. If you don't put something in it, there's nothing to add to the bucket of water (records in the table) so don't look for a new record in a table just because you moved a form to a new record. Exception might be that you could see default values, but they don't actually exist until the record is saved.

[chemguy120]

When I manually forward to a new reference record the date autofills with the current date and things proceed as expected.

With the code in the Load event, it just sits on the last record instead of advancing to a new one.

With the code in the Current event, it actually moves to a new record (if the ReferenceDate does not contain the current date) and the current date autofills and focus set to the ReferenceNumber where a number auto-generates. The user then either edits the reference number if a specific one is needed or accepts the auto-generated number and moves on.

I may just keep the code in the Current event for the time being as that seems to be doing what I'm looking for. I can keep an eye out for any potential issues. I'm just intrigued as to why it doesn't work on the Load event. Worst case is to go back to remembering to manually advance to a new record each day.

[Micron]

Load event loads existing records, it doesn't start the creation of new ones. When you have default values in a field, those look like a new record is being created but until you do something to save the record (e.g. move off of it) it doesn't actually exist. The current event applies to the current record which is why I guess it allows you to move to a new one.