Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

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

sooragssoorags Member Posts: 18
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.

Comments

  • splansingsplansing Member Posts: 140
    Try this: either skip the query entirely, and just us [UK Table] as your source object, or, if you must use a query for some reason (not readily apparent from here), drop the entire WHERE clause. I can't see any purpose to it. You're selecting all records that match the current record? Perhaps I'm missing something, but that is totally confusing.

    : 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.
    :

  • sooragssoorags Member Posts: 18
    I have tried removing the query entirely and sourcing the form and subform to the UK Table instead. This does not work how I want it to.
    I have also tried deleting the Where clause and this also doesn't work the way I want it to.


    : Try this: either skip the query entirely, and just us [UK Table] as your source object, or, if you must use a query for some reason (not readily apparent from here), drop the entire WHERE clause. I can't see any purpose to it. You're selecting all records that match the current record? Perhaps I'm missing something, but that is totally confusing.
    :
    : : 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.
    : :
    :
    :

Sign In or Register to comment.