Form Query locks up when opened in Design view

[Receiverbuilder]

First of all Hello.

This problem has really got me stumpped. I have searched ton's of forums and haven't found anything that has helped.

I am updating a working database with some more user friendly form queries. I have set the query up to the main data table, created the query, and the form, then linked the various query fields to the appropriate fields on the form (all unbound form fields).

Now up to this point there are some minor issues but nothing that I can't fix on my own. When you access the form input various search parameters in the form fields and run the query it works just fine.

Now when I try to go back into the query in Design view it crashes access! I don't even get an error message access simple locks.

Solutions I've tried.

• I assumed at first there was an issue with my query fields linked to the form. So I rebuilt the query from scratch went over each and every field link command and got the same result multiple times.
• I ran the analyze and optimize tool
• I have changed the Yes/No fields and adjusted them to True/False thinking I was having an issue with Null fields in the query

Nothing has worked. The form and query still function exactly as designed I just cant open the Design view on the query???? It has to be an issue with the query as the form opens in Design view.

Any help will be appreciated.

 

[Big Pat]

I've had *something* like this once or twice, but rather than a query, it was a form that wouldn't open either in design view or form view, no matter what I tried. Eventually someone suggested that I create a totally new mdb. and import all of my objects into it. This worked for me!

If no-one else suggests a better solution, that might be worth trying. Depending on how complex the query is, it might be worth importing all the *other* objects but re-creating this particular query from scratch in the new mdb.

Good luck!

 

[vbaInet]

What happens when you try to open the query via the Record Source of the form?

It may well be corruption in one or more of the tables. So I would go with Big Pat's suggestion of:

1. Creating a new database shell
2. Import all the objects into the new db shell (except the query)
3. Copy the sql statement from the old query (in the old db) and paste it into the SQL VIEW of a new query in the new db shell
4. Run, Save it, Run it again, Close.
5. Now try to open the query in design view.

 

[Receiverbuilder]

What happens when you try to open the query via the Record Source of the form?

It may well be corruption in one or more of the tables. So I would go with Big Pat's suggestion of:

1. Creating a new database shell
2. Import all the objects into the new db shell (except the query)
3. Copy the sql statement from the old query (in the old db) and paste it into the SQL VIEW of a new query in the new db shell
4. Run, Save it, Run it again, Close.
5. Now try to open the query in design view.

Thanks for the reply's. Big Pat and vbaInet.

First off the Record Source for the form was blank but when I set it up as the qrysearch (the one having the issue) and tried opening the qrysearch design view from the record source access locked up.

Then I tried creating a blank database and importing all the objects from the old database except for the query having the issue. I then set up a new query and copied and pasted the SQL statements into the new query. Ran it then tried opening it in the design view and it locked up.

A few oddities I have noticed. I can open the SQL view window without access crashing but not the design view. Currenlty I am going through with a new query and linking the query fields to the form one at a time. After each one is linked i save it shut it down and try to open it in design view. At the time of this reply I have 5 fields linked and so far I have been able to open the query in design view each time.

That has me really curious as to what is going on when I go through and link all the fields in the query to the form and then close it. That somehow seems to be corrupting the query?? I have never heard of access having a limit on how many fields you can have linked to a form based query but is there a limit or a point where the number of field refrences makes the query unstable or corrupts it?

PS. My appoligies if it helps it is running in Access 2003

 

[boblarson]

It would be interesting to see if we can reproduce the behavior ourselves. Any chance you can post a copy of the database here? If you do, make sure to supply bogus data for any fields which could be personally identifiable or have to do with your company which can't be made public.

 

[Receiverbuilder]

Hi Bob. Unfortunately according to our SOW everything but the auto generated numbers is propriatary or confidential to our customer or our company. However I can post up the SQL code for the query in question.

So I went through and linked each query field to a form field saved, closed the query, and then tried to open it in design view. It worked if I build the form query like that the query does not lock up access.

Unfortunately when I tested the query by inputing some query criteria in the form the query would not eliminate any records in the database. It would return every record in the database as the result of the query. Which means there was an issue with the Is Null formula.

So this is what I am putting in to link the query fields to the form.
Forms![formname]![formfieldname] Or Form![formname]![formfieldname] Is Null

I have found the the second sequence (Or and everything that follows) is necessary to allow empty fields. So users can search for customer name, or carrier, or etc... but they dont have to have both.

 

[boblarson]

Ouch! That is a wicked query. Personally, I would remove every criteria from that and put it into a function you call when opening the report. It would then only use what has been set as criteria instead of using such an outrageous query.

 

[robertjain19184]

Reply

What happens when you try to open the query via the Record Source of the form?

It may well be corruption in one or more of the tables. So I would go with Big Pat's suggestion of:

1. Creating a new database shell
2. Import all the objects into the new db shell (except the query)
3. Copy the sql statement from the old query (in the old db) and paste it into the SQL VIEW of a new query in the new db shell
4. Run, Save it, Run it again, Close.
5. Now try to open the query in design view.

you are give us such a nice information.
------------------------



Thanks and Regards

 

[Receiverbuilder]

Ouch! That is a wicked query. Personally, I would remove every criteria from that and put it into a function you call when opening the report. It would then only use what has been set as criteria instead of using such an outrageous query.

Sorry for the late reply got swamped with other duties. At the risk for sounding ignorant how would one go about doing that you lost me abit on how to implement this solution.

If i am following you correctly set up a report? Wouldnt that have to run off of a query?

 

[boblarson]

Sorry for the late reply got swamped with other duties. At the risk for sounding ignorant how would one go about doing that you lost me abit on how to implement this solution.

If i am following you correctly set up a report? Wouldnt that have to run off of a query?

1. You create a query but leave off any criteria.

2. You base the report on the query.

3. You open the report and passing a WHERE CLAUSE which filters the data for the report (that is where you apply the criteria).

See here for a sample of how you can have a generic report but open it with different criteria. And this one shows you how to deal with dates and null selections.