I get an error on my form because my query keeps on changing (1 Viewer)

[soorags]

I have made a form that works fine called frmNz. However each time, the query qryNew keeps on changing, either becoming blank or too complex. This keeps on happening. I keep changing it to the SQL coding that I wish but when I save it, after, I go back into it and it has changed and the form isn't working correctly.

Take a look and see if you can tell the problem.

The SQL code that I have used for the query is:

SELECT [UK Table].[Business Name], [UK Table].[Business Type], [UK Table].[Address], [UK Table].[City], [UK Table].[Country], [UK Table].[Telephone Number], [UK Table].[Website Address], [UK Table].[Email Address]
FROM [UK Table]
WHERE ((([UK Table].[Business Name]) Like [Forms]![frmNz]![txtBusinessName] & '*' Or [Forms]![frmNz]![txtBusinessName] Is Null) AND (([UK Table].[Business Type]) Like [Forms]![frmNz]![txtBusinessType] & '*' Or [Forms]![frmNz]![txtBusinessType] Is Null) AND (([UK Table].[Address]) Like [Forms]![frmNz]![txtAddress] & '*' Or [Forms]![frmNz]![txtAddress] Is Null) AND (([UK Table].[City]) Like [Forms]![frmNz]![txtCity] & '*' Or [Forms]![frmNz]![txtCity] Is Null) AND (([UK Table].[Country]) Like [Forms]![frmNz]![txtCountry] & '*' Or [Forms]![frmNz]![txtCountry] Is Null) AND (([UK Table].[Telephone Number]) Like [Forms]![frmNz]![txtTelephoneNumber] & '*' Or [Forms]![frmNz]![txtTelephoneNumber] Is Null) AND (([UK Table].[Website Address]) Like [Forms]![frmNz]![txtWebsiteAddress] & '*' Or [Forms]![frmNz]![txtWebsiteAddress] Is Null) AND (([UK Table].[Email Address]) Like [Forms]![frmNz]![txtEmailAddress] & '*' Or [Forms]![frmNz]![txtEmailAddress] Is Null));

When I have entered this code and saved the query, I exit and go back to it and it has changed, either becoming too complex or non-existent.

To create the original query I selected all fields to be in it apart from "Website?", "Email?" and "BusinessID" from the UK Table.
For the criteria under each field the formula below was entered.

Like [Forms]![frmNz]![txtBusinessName] & '*' Or [Forms]![frmNz]![txtBusinessName] Is Null

This was used for each field's criteria."txtBusinessName" is the name of the textbox on the form. This was changed as required for each fields criteria, e.g.

Like [Forms]![frmNz]![txtWebsiteAddress] & '*' Or [Forms]![frmNz]![txtWebsiteAddress] Is Null

That is how the query was created, but when save and revisited it is altered being full of too much coding or empty.

How can I fix this? It is attached. I have tried creating new queries to work the same but the same thing happens to them. Also when I try to copy the form twice, the copied versions aren't exact and get the same problem. I want two copies of the original working frmNz.Can anybody help me?

Gurdip

 

[Wiz47]

For some reason, you had an actual query instead of a table for the subform. I changed that to a record based on your query2. Right now, it shows all of the records for the table. You still need to go in and change that query to mimic the one you had here so it will filter the records based on the combo boxes.

On edit: You also have duplicate entries in your combo boxes. To correct that, put the word DISTINCT right after the SELECT statement in each of the queries that are the control source for those boxes.

 

[soorags]

The attachment sent back.

The attachment that you have sent back to me is the same as before, their subforms are based on qryNew. Do you mean that I should change the subform to be based on query2 and then recode the criterias to the original formula? I have tried this before but it doesn't work.

Gurdip.

 

[Wiz47]

Sorry, I uploaded the old one - here is the new one using query2. If you add the WHERE statements that filter the form - like you did in the original post, it should work because that is the standard way to do it.

 

[soorags]

Actually I have not ried that before, should I?

Gurdip.

 

[soorags]

There is nothing attached.

Gurdip.

 

[Wiz47]

There is nothing attached.

Gurdip.

I just looked at the post and the attachment is there.

 

[allan57]

Gurdip

Create a sub form and use your SQL code as its record source, then use the sub form in your main form instead of the 'Query.qryNew' for your search results.

 

[Wiz47]

Gurdip

Create a sub form and use your SQL code as its record source, then use the sub form in your main form instead of the 'Query.qryNew' for your search results.

That's what I did in the attachment. My post should have an attachment that is about 57kb.

 

[soorags]

When I go to save the query2 after changing it it doesn't let me save it. Also I do not understand the form based on query 2. My form lets me enter minimum information about companies to retrieve full data in the subform. I do not understand how the form based on query 2 works.

Gurdip.

 

[Wiz47]

I just checked and the attachment has been downloaded 2 times. So it is here. Please download it and use it as your example. It seems to me that you are not following the instructions that we have given you. I can't help you beyond what I've already done if that is the case. I don't mean to be abrupt, but I'm not sure what else I can do at this point.

 

[soorags]

I have downloaded the file and tried to change the query but the file is a read-only file and doesn't let me save changes. It is read only. I do not know how to change it from read only.

 

[Wiz47]

I have downloaded the file and tried to change the query but the file is a read-only file and doesn't let me save changes. It is read only. I do not know how to change it from read only.

I just downloaded it at work, went into the query - added an ORDER BY line and saved it.

Go to the Database Window, right mouse click on the Query, then right click and select Design View, then right click and select SQL view. Make the changes (add the WHERE lines) and save it. There should be no problems.

 

[soorags]

I have downloaded the database but when I enter it, it says that it is read-only.
It does not let me save any changes to any data or object definitions in the database.

 

[soorags]

I have tried and it doesn't let me save.

 

[boblarson]

Close the database and go to the database file in a Windows Explorer, right-click on it and if it has a box checked that says something like this came from a non-trusted source, uncheck the box and then reopen the database. I had a client have that problem yesterday. I hadn't seen it before. Must be some security policy setting.

 

[Wiz47]

Close the database and go to the database file in a Windows Explorer, right-click on it and if it has a box checked that says something like this came from a non-trusted source, uncheck the box and then reopen the database. I had a client have that problem yesterday. I hadn't seen it before. Must be some security policy setting.

Thanks for the explanation, Bob. For the life of me, I couldn't understand why he couldn't edit the query when I could.