I get an error on my form because my query keeps on changing

[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 saved and revisited it is altered being full of too much coding or empty.

How can I fix this? I have tried creating new queries to work the same method 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 originally working form frmNz as well.Can anybody help me?

Gurdip.

 

[TheAceMan1]

How are ya soorags . . .

If you used the [blue]primarykey[/blue] field of the table your where clause would reduce to:

[blue]WHERE ([[purple][b][i]PKname[/i][/b][/purple]] = [Forms]![frmNz]![[purple][b][i]PKname[/i][/b][/purple]][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[soorags]

What difference will that make? I don't know what that code does as well. Where do I place it. Can you elaborate?

Gurdip.

 

[TheAceMan1]

soorags . . .

Well . . . does [blue][UK Table][/blue] have a [purple]PrimaryKey?[/purple]

If the above is true, whats the datatype of the [purple]PrimaryKey?[/purple]

I find it hard to believe your not aware of this special key field . . .

See Ya! . . . . . .

 

[soorags]

The UK Table has a primary key called Business ID. The data type for the primary key is autonumber. Do you mean I should use this code for the criteria of the primary key field in the query? Do you mean I should use this code for the primary key field's criteria alone?

Gurdip.

 

[TheAceMan1]

soorags . . .

Consider this . . . in your post origination your [blue]where clause[/blue] zero's in on all fields . . . [blue]therefore looking for as unique a record as can be found![/blue]. Since [purple]the PK is as unique as you can get for any record[/purple] . . . why not! (its all encompassing!) . . . I hope you see this! . . . one of the major reasons for a PK! The [blue]PK[/blue] identifies this record and all its fields in one shot! . . . as you've shown it to be!

Since it won't hurt to check it out, save the query under an alternate name and change the where clause to what I suggested earlier . . .

[blue]WHERE ([[purple][b]Business ID[/b][/purple]] = [Forms]![frmNz]![[purple][b]Business ID[/b][/purple]][/blue]

. . . let me know if results were satisfactory . . . I expect you'll be pleased . . .

See Ya! . . . . . .

 

[JoeAtWork]

soorags - it looks like you are trying to build a strange paradox. You have a form that is bound to a query which is in turn filtered by the form. Or in other words, you are trying to move to the correct record on the form based on what is currently on the form.

Simply put, don't use bound fields of the form as filter criteria for your query! You are asking the query to be it's own filter! This is circular logic!

If you need the form to open to a certain record based on search criteria, use a seperate form to enter the criteria, e.g.

WHERE [UK Table].[Business Name] Like [Forms]![[COLOR=red]frmSearchBy[/color]]![txtBusinessName] & '*' Or [Forms]![[COLOR=red]frmSearchBy[/color]]![txtBusinessName] Is Null

 

[soorags]

This is the coding in the new query, is this what you mean?

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 ([Business ID] = [Forms]![frmNz]![Business ID])

 

[soorags]

JoeAtWork, I am trying to create a form on which I can enter some data about a business in any of the fields and then press a search button. This will then retrieve the data for the business and display it. I am looking to get the data to be displayed on the same form so that it can be used from there for another purpose. I do not understand what is being said.

This is an example of the operation that I want the form to do. If I enter 'Cattle' as the Business Type in the Business Type textbox, and then submit, I would like the form to retrieve all businesses that have the business type 'Cattle'.
Or, if I type 'Cattle' as the Business Type and 'United Kingdom' as the Country and submit, the form should retrieve all 'Cattle' companies from the United Kingdom. How can I display these records in an effective way?

 

[soorags]

TheAceMan1, I have tried that coding but it does not work. I enter information into the form and submit but no results show. Do you mean for me to put a primary key textbox onto the form?
The system is to be designed so that company data can be retrieved easily, just by typing the Name or Address for example.

 

[TheAceMan1]

soorags . . .

I managed to sneak in a post at work but apparently it didn't take. In any case since your talking an explicit search feature, my prior posts are nullified! . . . What you need is code that will [blue]and[/blue] together all fields with valid data. Your headache is trying to do this within the same form! What you need is a search form (triggered by a button) to make this feasable. The search form collects the data and sets up a WHERE clause that replaces or appends existing query/sql in the row source of the form (as prescribed by you)!

See Ya! . . . . . .

 

[JoeAtWork]

If you want to search from within the same form, then use unbound controls for your search criteria. So for example if you want to search by Business Name, you would actually need two Business Name textboxes, one unbound (let's call it txtNameSearch) and one bound (we'll call it txtBusinessName).

So your search criteria would then be

WHERE [UK Table].[Business Name] Like [Forms]![frmNz]![txtNameSearch] & '*' Or [Forms]![frmNz]![txtNameSearch] Is Null

A possible way of doing this is put all your bound controls in a subform of frmNz. You could make frmNz completely unbound. You could change the borders, background, etc. of the subform so it completely blends in with frmNz and to the user it looks just like one big form.