Enter Parameter Value -> But Parameter is present

[outofpractice]

Hello all! I'm having an issue with a DB that I'm working on which I can't figure why it is happening. So here is what is going on:

I have a form: frm_WeeklyInformationEntry and on this form are two fields titled: Weekly_Start_Date & Weekly_End_Date.

I have a table: tblDocSent that has fields: Sent_Date and Doc_Sent - basically a user will enters data into this table via a form with the date & number of documents sent.

I have a query on the tblDocSent table which has criteria for the Sent_Date field of:

>=[Forms]![frm_WeeklyInformationEntry]![Weekly_Start_Date] And <=[Forms]![frm_WeeklyInformationEntry]![Weekly_End_Date]
To only bring back the Doc_Sent fields that are within the range indicated on the frm_WeeklyInformationEntry form.

So - with this query I have another form created called frm_DocSentDaily . This is a subform on frm_WeeklyInformationEntry. The purpose of this is - the weekly information entered needs to include the weekly count from the tblDocSent.Doc_Sent field.

So that this information doesn't have to be looked up - the subform runs the query (and I requery the subform once the Weekly_End_Date field is updated) and the subform then sums all of the Doc_Sent for a given week and is displayed to the user. This is working as I want it to (and I know that I should just be using a calculated field instead of re-entering the data into the weekly form. This is the first somewhat elaborate DB I'm making in about 2+ years so I have some inefficiencies in it currently!)

This process works. It works when I open the form, when I update the Weekly_End_Date field, when I cycle through the records on the form, or if I just run the query by itself (as long as the form is open with the Start/End dates populated) - it is giving me the accurate results I want.

Except for the issue. Seemingly random when I open up the form and the query tries to execute, I receive a prompt "Enter Parameter Value" for both [Forms]![frm_WeeklyInformationEntry]![Weekly_Start_Date] and [Forms]![frm_WeeklyInformationEntry]![Weekly_End_Date]

I'll open/close the query and it won't run - always gives the error. Then it will just seem to start working again.

This last time it started giving the error, it was pretty persistent so I renamed frm_WeeklyInformationEntry to frm_WeeklyInformationEntry2 then I copied it and pasted another one into the DB naming the copy back to the original name of frm_WeeklyInformationEntry - when I opened this copied version of the form the query worked.

It doesn't seem to have any reason to why it stops working - at least not that I can identify. I can open up the form 20 times in a row and it will execute properly when it is working. The only thing I can think of but have not been able to test yet is - it seems as though this may sometimes happen when the DB is completely closed for a while and then first opened up and accessing the form.

So - wanted to see if anyone has any ideas or experience with this Enter Parameter Value error appearing when it shouldn't be. It's becoming frustrating to not be able to figure out why it appears then disappears.

 

[sneuberg]

Have you done a compact and repair?

 

[JHB]

..
Except for the issue. Seemingly random when I open up the form and the query tries to execute, I receive a prompt "Enter Parameter Value" for both [Forms]![frm_WeeklyInformationEntry]![Weekly_Start_Date] and [Forms]![frm_WeeklyInformationEntry]![Weekly_End_Date]
..

How do you open the form, by code or just clicking it in the navigation window?
Where/when/how does the query tries to execute?

 

[Galaxiom]

The problem is caused by timing. The subform is loading and hence running the query before the textboxes on the main form have loaded.

Try adding textboxes to the subform header/footer with their ControlSource referring to the textboxes on the main form. Then use the subform textboxes as the parameters in the query.

 

[sneuberg]

The problem is caused by timing. The subform is loading and hence running the query before the textboxes on the main form have loaded.

Try adding textboxes to the subform header/footer with their ControlSource referring to the textboxes on the main form. Then use the subform textboxes as the parameters in the query.

I tried to replicate the OP's problem in the attached database by introducing a time delay (for next loop) in the opening of the main form. The subform does open and load before the main form as can be seen by the msgboxes I put in the events but this doesn't seem to cause this problem. I even put an extra DoCmd.OpenQuery "qryDocSent" in the subform open event and that doesn't prompt for the parameters.

 

[outofpractice]

sneuberg - I did a compact last night, and while I haven't had a lot of chances to mess with this yet today the error has not occurred yet (So hopefully that fixed it)

Galaxiom - Thanks for the suggestion. If the error starts happening again I'm going to give this resolution a try. If this happens I'll reply back to this thread on the results.

JHB - Currently I am just double clicking on the form in the navigation window. Once start cleaning the database up and get it ready for daily use I'll add a switchboard with a button on it to open the form - the button will just execute a macro to open the form and go to a new record.

Thanks everyone for their feedback. I'm really hoping that the compact fixed the issue, but if it didn't at least I have something else I can try to see if that might nip this problem.

 

[outofpractice]

How do you open the form, by code or just clicking it in the navigation window?
Where/when/how does the query tries to execute?

I have a macro to Requery the subform in the On Exit event for the Weekly_End_Date field of the main form. I did it this way because that should mean the dates have been entered - the query re-executes which then displays the total count for the date span. Maybe not the most elegant things but it is working (Except when the issue that this thread is about happens)

 

[Galaxiom]

Load timing problems can be a bit random depending on the order of loading of the controls on the main form which AFAIK cannot be defined. I have had situations where an arrangement worked properly until I replaced a control, presumably making it load later than it had in the previously working arrangement. I imagine a C&R could alter the load order too.

The most common problem I encounter is with the main form Current Event where multiple subforms all need to be loaded for the main form to work properly. I get around this by adding a Boolean variables to the main form and checking them for True before the problem procedure is allowed to run.

The subform Load Events are used to change their associated Boolean to True so the main form knows that the subforms are all loaded.

The same principle can potentially be used in reverse to stop the subform from doing something until the main form Load has completed.