Issues with Opening a Related Form While Still a "New Record"

[Sa'El]

• Form A has a button to open a New Record, which opens Form B in Dialog mode (if it matters)
• On Form B, I have an expression in the form header that displays the name that is currently being entered
  
  • When the name is entered, the expression displays the entered name for that record appropriately
  
  
• On this same Form (Form B), there are several Forms for the user to choose from (we'll call them Form Cs)
  
  • Form Cs are actually subforms of Form B with the fields of Form B removed
  • Parent/Child linking is matched on each of Form C
  • Like on Form B, Form Cs also have an expression that displays the name in the header

When I open an existing record, when I navigate to Form Cs, I am in the right filtered record as is validated by the expression in the form header.



But here's the problem: When I open a New Record and enter information in Form B, most of the Form Cs update to the info in the New Record, but a couple do not...although that's not true all the time. Bear in mind, that the New Record is likely not established yet (still in "Dirty" I guess). There is no pattern as to why most of the Form Cs update most of the time. Additionally, it seems that some of the Form Cs are more successful than others, but it's not like some are 100% and others are 0%, it's more like you have "Likely" and "Unlikely". Just now I opened a new record, and when I clicked the "Unlikely" Form C, I noticed a failed linking. I open and closed that form (while in Dirty) and no bueno. I then click a "Likely" Form C and it linked fine. Then went back to the "Unlikely" and then the linking worked.

Again, the Parent/Child links are done, and for an existing record, the filter is fine and once it initially links to the parent form, naturally the link sticks. I was wondering if it was a lag or something, but it doesn't seem to be a matter of time, just that the "starter" isn't firing well on some of the Form Cs.

[ItsMe]

I tried following along with your description of how your forms are set up. What I gather is that your are using one or more unconventional approaches to display data in your form(s).

Bottom line, the Field that is the Main Link needs to be updated/saved at the table level before the Child Link field knows what is going on. AFAIK, linking is done at the table and not at the form Layer. The value of the control(s) do not matter, bound or unbound. All the, "expression in header", "multiple Cs forms", and "Likely vs. Unlikely" are not really relevant to saving a record to the table.

Focus on what you are doing or not doing to save the record from Form B. Navigating from one record to the next (from within Form B) will save the record to the table. If there is a control in form B that is bound to the Main Link Field, the act of losing focus of that control should commit the value to that field in the table. Otherwise, you will need some code to save the record.

If Me.dirty = True then
Me.Dirty = False
End if

[Sa'El]

(Thanks for the reply)

*Gasp* Unconventional? Hmmm...

I am converting is a questionnaire, which has scores of fields and multiple sections. The initial information is Form B, and the remaining sections are the Form Cs (1-1 relationship with Form B, and as such, they're subforms). Thus from a conceptual standpoint, you wouldn't leave the record of Form B as there are related information for that same record (Form Cs) yet to be entered. Ideally I wouldn't have Form Cs, but there are too many fields and the database needs normalizing. That's the reasoning behind not leaving the record of Form B. This also explains why it isn't practical (from the end user's standpoint) to go to another record in Form B without finishing up Form Cs related to that record first.

Nonetheless, I completely understand what your saying as far as the database is concerned. I am new to coding, so I could use some more detail as to what that code is getting at (I kinda know about dirty, but not what to do with it). Or here's another way: Let's say the linking field between Form B and Form Cs is called "PrimeLink". Would you mind guiding me on after the PrimeLink field is entered and when it is update, loses focus, or whatever is needed, that I can save the record through code?

Thanks.

[ItsMe]

I would save the record and then expect the record to be available to the child link. The first step is to identify an event that will trigger some code. For testing purposes I will use a click event in a Command Button. Then, after your code is tested and you know it works well, place the code in an event that will provide better automation (the user does not have to click a button to fire the code. The code fires because the user simply sets focus elsewhere, etc.).

I would expect my subform to display new data after the new data was saved to the table and after I re-queried the subform's container.

so in a click event I would place

If Me.dirty = True then
Me.Dirty = False
End if

'and then requery

Me.SubFormContainerName.Requery

[Sa'El]

Haha...When I saw the code, I thought that was a template to add more code to it, not the actual code itself (D'uh!). Using the ole identity crisis routine, eh?

Thanks for your patience. Lost Focus worked fine on a mandatory field to update the table. Afterward I tried doing long focus on the form itself (since through a click it opens up a modal form), but that didn't work, though it's okay.

I appreciate the help...and just in time too! Take care.

[ItsMe]

Sure thing. Glad you were able to find a solution. I may actually look into this some more and see where and when the Main/Child link gets refreshed. Many times my subforms do not use the link because my main forms are usually unbound. So the .Requery may not be needed after all when the link is established.

[Sa'El]

Cool. For the record, requerying the subform is not necessary, though I should clarify that the subform I had was not a Form C (Form Cs are separate modal forms called through a button), thus it was a coincidence I had a subform on Form B, but I did check it out nonetheless.