Subform Timing Issue?

[Fliption]

Well I have gotten beyond the tough part of my project only to run into a quirk that I never expected.

I have a form where a user can input a zipcode and then a radius distance in miles. The subform then shows all of the zipcodes within the radius distance from the original zipcode. Once a user inputs a zipcode, a query is taking the latitude and longitude of that zipcode and calculating distances using a trigonometry formula. The subform is based on the results of this query. The problem that I'm having is that once I open the main form the subform doesn't yet have the zipcode data and is giving this error:

"The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplfying the expression by assigning parts of the expression to variables"

Once I have the main form open and can input data, the subform will open and display the data if I open it as a separate form. It will not work while embedded in the main form. One solution that I can do but I really don't want is to have the zipcode and radius input on a separate form that the user could then push a "submit" button to get the subform data to open. I wanted to check to see if anyone else had any ideas before I go that route. I was wondering if there is a way to make a subform "inactive" or something like that to give the main form time to open and receive input.

Thanks for any input. (I'm keeping my fingers crossed that someone doesn't tell me to handle these calculations with code . I don't think I have time to fool with that right now and I am soo close.)

 

[KeithG]

Sounds like you need to requery your sub form after the zip code is updated. Can you post an example of your db?

 

[Fliption]

I'm having an issue with an error message when the main form is opened. So if I could keep the subform and underlying queries from running when the main form is open, then you're right, requerying the subform after the zipcode update would be a necessary step. Any idea how to make a subform "inactive"?

Unfortunately, the DB is extremely large and contains proprietary information (doh!). I would have to recreate the problem with a smaller db in order to post. Thanks for your reply.

 

[grnzbra]

Perhaps you could use a default zipcode and radius and make the subform invisible. Then, when the user enters a zipcode and radius, make the form visible as well as generate the list.

 

[Fliption]

Nice Idea. For a brief moment I thought that might work. When I tried it, I realized I had forgotten why I placed the word "timing" in the title of this thread. Even with a default value I still get this error. It's as if the queries in the subform are trying to run before it recognizes the default zipcode value in the main form. I hope I'm being clear. It's like when I open the main form it is doing things in this order:

1) run query underlying subform
2) populate default value into zipcode input control

That's why I thought the only way to do this is to either separate the input into another form OR somehow deactivate the subform and keep it from refreshing when the main form is open.

 

[gemma-the-husky]

your last comment is probably what is happening. Access is event driven, so forms carry on processing code without stopping.

So if you are in form A, and open form B, and want to suspend operation of form A until form B closes : -

either open form B as a dialog form

which will make you deal with form B first

OR

after opening form B put

while isopen("formB")
doevents
wend

which will pause form A, while letting you do other things

you will need to find a function that test whether a form is open - can't do that for you at the moment, but there will be loads on this forum.

 

[Fliption]

Would this work if the 2 forms in question are a mainform and a subform? When I open the main form, I don't want the subform to try to pull data until the main form has completely loaded.