Subforms Asking for Parameters and Not Loading

[kd2017]

I have a main form with 2 subforms, call them A and B. subform B's master field is linked to a textbox on the main form, that text box's control source is the ID field of subform A. I want subform B to display the details of the row selected on subform A.



Subform B has a couple combo boxes that have rowsources with criteria that reference a field of the main form.

When I was building this form everything was working smoothly and as I expected it to. I ran a compact and repair and now it's broken. When I load the main form it asks for a series of parameters, and finally displays an error. What's going on? how might I fix this?

[ranman256]

you get parameter boxes by:
either a field is misspelled,
or
it does not exist in the query
or
the path to the text box is not correct. Use the BUILDER to the form object paths correct.


Instead of using Dlookup, use a combo box and subform.
user picks the Item in the combo (ID, or name)
the subform is linked to the combo and fills in automatically. No Dlookup needed.

[kd2017]

The paths are correct. And I always use the builder to set them. They were working fine until the compact and repair.

The only dlookup is the one in the top left corner of the form and it's irrelevant, I've deleted it and still errors.

[kd2017]

Okay, I think maybe this is an Access bug? I opened the VBA editor, closed it, and now all is working again... I've made no changes to this form..?????

Any insights? If I'm doing something wrong I don't want this crap to pop up when the db is in actual use.

[Micron]

The fact that it's working may be temporary. Suggest you close down, reopen and test a few times. Sometimes, data is held and reopening the form works (like from open to design back to open views) and compiling will clear the data from memory as far as I know. The return without gosub and parameter prompt for JobHome.jID are usually caused by the same fact - that a subform loads before the parent form, and that isn't a bug. So when controls or code on a subform attempt to resolve references on the main form (which isn't open at the time) you'll get one or both of those messages. Usually the gosub message is caused by this fact, plus having code in an event that also attempts to resolve a control reference on a subform (such as the Current event). As for the other prompts, probably the same reason, but I can't tell from your pics where those fields are.

[kd2017]

I'm trying to work out the sequence of events in my head...
The rowsource of the subform's combo box is dependent on the current record in the parent form. So given that a subform loads before a parent form, then how about I keep the rowsource of subform's comboboxes blank and set property pragmatically from the parent form's ... OnCurrent event? or load event?

[EDIT]
And another thought: Can a combobox's rowsource query reference a field on it's OWN form without error or if it must reference a form should it be a DIFFERENT form that is guaranteed to always be loaded before its own form?

[Micron]

That would be the approach I'd take unless maybe the fields on the parent form and subform are bound, in which case, can the subform combo not make use of the linking child property and the parent form field be the master? If not, then yes, in a main form event have the subform requery itself. If you use the load event, it will only fire once. If you use the Current event, then it will fire each time the form opens as well as when you navigate to another record on the main form or requery/refresh the main form. You would have to decide on which is appropriate for your situation and know how to reference a subform from a main form. Here is one syntax for it provided a nav form isn't in play here
Forms("frmMyForm").Controls("subformcontrolname"). Form.Requery

Note that subformcontrolname is the name of the control on the main form that contains the subform.