Form still prompts for parameter after query deleted (1 Viewer)

[Reese]

I had created a query that I was attempting to reference a text field in a form that was open when the query is run. It wasn't working and whenever I opened the form it would prompt for the parameter before opening the form.

I ended up deleting the query to try again from scratch. But the form still asks for the parameters whenever I open the form. Why is Access still prompting me for the parameters?

 

[Carol_Berlin]

A bit more information would be helpful, for example

- Where ist the query? Is the form based on it?

At the moment is sounds as if the form, or at least one field in it is based on a query which delivered information, even if it were incorrect.

When you deleted the query, the field cried out for the information (parameters) that would have been handed over by the query.

Solution?
- Make a copy of the form first and try the following out using that copy
- Look at the properties of the field in question and delete the link to the query.

Carol

 

[Reese]

The form isn't based on the query, actually it's the opposite. The link was designed to look up a value from a field in a form. Still, I double checked the form and there's no link remaining--at least one that I can find.

Unfortunately I can't post the full SQL code since I deleted it, but the query was to pull a value from the form using the following criteria:

Forms![ZooMobile Event Booking].ClientIDTxt

I included in the form a button command to run the query but when I tried to run the query from said button, Access still prompted me with the standard "Enter Parameter Value" for the above value. I decided to just scrap the thing and start over; deleted the command button in the form and then deleted the query itself.

But the form still prompted me for the parameters.

I'll try to upload a slimmed down version of the database later this afternoon, if that would help.

 

[Carol_Berlin]

The thing that makes me wonder is that the parameters are asked for when you open the form. That suggests to me that either there is an autoopen macro running or one of the fields contains a formula which tries to read data out of a non existant object, for example a query.

 

[Reese]

Yes, I agree with you, except I didn't create it that way, nor can I find evidence of it that way after deleting the button and query.

I also found two new things:

The request for parameters appears when opening everything that is somehow related to the form that I had set the query to run from (except for the table itself). That includes the ZooMobile Event Booking form, the ZooMobile Cost Subform (which is within the Event Booking), the ZooMobile Cost Query (which the Cost Subform is based on) and the ZooMobile Booking Form (which the Event Booking form is a sub form of).

Also, when the parameter prompt appears, if I leave it blank and hit OK or Cancel, a error notification appears saying "The expression On Current you entered as the event property setting produced the following error: Return without GoSub."

I've attached a slimmed-down version of the database to this post. Thanks for the help.

 

[Carol_Berlin]

HA! The error sounds good. Is there a button on it called debug or similar? If so, hit it and see where you land.
And I would look at the properties of field in the form that you would like to be filled or altered using the ex query. Is there anything in the data source property?

 

[Galaxiom]

Download V-tools and use the Total Deep Search with the term that is appearing in the parameter dialog. It will show every reference to that term in the parts of the project you select in the configuration.

 

[boblarson]

Your problem looks to be this in the SQL of the query:

PARAMETERS [Forms]![ZooMobile Event Booking].[ClientIDTxt] Text ( 255 );

remove the Parameter from the query parameters dialog or just delete that line from the SQL View of the query.

 

[GanzPopp]

This also often occurs when the form is still trying to order or filter the dataset on that specific removed field. Remove these references in the properties of the form.

 

[Reese]

Thank you everyone for all of your suggestions and responses. However none seem to work.

HA! The error sounds good. Is there a button on it called debug or similar? If so, hit it and see where you land.

Unfortunately there was no debug button just "OK" and "Help", which says: "This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired.".

In experimenting I deleted the ClientIDTxt field to see if that would help. It didn't so I re-created it. Now the error message doesn't appear but the request for parameters still does.

Download V-tools and use the Total Deep Search with the term that is appearing in the parameter dialog. It will show every reference to that term in the parts of the project you select in the configuration.

I downloaded every version of the V-tools for 2007 and none of them could be opened. It keeps saying "The database is in an unrecognized format... may have been created with a later version...etc." I downloaded them from http://www.skrol29.com/us/vtools.php#download. Is that the correct site?

Your problem looks to be this in the SQL of the query:

PARAMETERS [Forms]![ZooMobile Event Booking].[ClientIDTxt] Text ( 255 );

remove the Parameter from the query parameters dialog or just delete that line from the SQL View of the query.

The query has been deleted and I haven't created another one yet. Where can that SQL be hiding? Or should I try re-creating it? I'm worried that this will complicate the issue, especially since I can't remember the exact coding that I used.

This also often occurs when the form is still trying to order or filter the dataset on that specific removed field. Remove these references in the properties of the form.

That's what I've been trying to do but I have no idea where those references could possibly be. I've looked through all of the fields in the forms (of course I may have missed a few so I'll try again).

I know that I'm being a real pain but could someone please use the copy that I uploaded and try your suggestions on it? Perhaps I am doing them wrong, and without seeing it in person I'm having a hard time ensuring that I'm doing what you are all suggesting correctly.

 

[rzw0wr]

I downloaded your database.
Every field name on every form I looked at has an alias.

This is making debugging very difficult if not impossible.
Client_ID = ClientIDTxt

I did not find the problem.

Dale

 

[Reese]

Every field name on every form I looked at has an alias.

What do you mean by alias? Is that standard or have I been doing something wrong?

 

[rzw0wr]

Alias means to rename something.
Your field names should never be renamed to something else.

Client_ID is the field name and you have ClientIDTxt for the name on the property sheet other tab.
Access looks at the Name property in a form and not the field name.

Dale

 

[Reese]

I was not aware of that. I assumed that the text box name and the field name were unrelated in that regard. Though why hasn't that caused issues before this, then?

Unfortunately it still doesn't solve my current problem, does it? I just made the change for that particular text box and it didn't seem to make a difference.

The thing I don't understand is why Access is still assuming that there needs to be such a parameter if I deleted the query that required it.

Does anyone else have any suggestions? Otherwise I may have to go to my last back up--which is several days difference--and try to fill in the changes from my current database (except for the faulty form/query). From now on I think I'll be backing up the file every hour.

 

[Reese]

Actually, backtracking here.

Alias means to rename something.
Your field names should never be renamed to something else.

Client_ID is the field name and you have ClientIDTxt for the name on the property sheet other tab.
Access looks at the Name property in a form and not the field name.

If that's true, then why doesn't Access automatically do that? I did a test, creating a new text box from a field that I hadn't placed in the form yet, Insurance_Needed. Access automatically assigned the name of that text box as Text32.

I know that Access named it that before I chose the control source, but then why doesn't Access automatically change the text box's name as soon as I choose a source? Do I have to manually change the Name every time?

 

[rzw0wr]

Reese, I feel for you.
I have had this problem more that once.
Finding the problem with having fields aliased is bad enough.
You do have 2 VBA references to ClientIDTxt in search forms.
Open your code window, select the search icon at the top of the editor and type in Clientidtxt. search the entire project.
You may have to take your editor window out of full screen mode to get the find to work. I have to mine.

Dale

 

[rzw0wr]

Reese, if you drag and drop a field from your Add Existing Fields sheet Access will keep that name.
If you make a text box and bind it manually to an existing field Access will retain the text box original name.
Access does not play around with text box names like it does with table, queries and field names.

I always double check my field text box names on forms just to make sure they are the same as my field names.

Dale

 

[Reese]

Thank you for the help. I'll see what I can do. A follow-up question, though:


How does Access tell the difference between the table's field and the text box's name? I have several points in my VBA that tell Access to set the text box values (not the field values) to certain things based on certain conditions, then once the form is saved, the values go into the appropriate fields in the tables.

(I did this because I noticed that if I used the field name then the new value won't be visible in the associated text box until I enter said text box. E.g. using Me.AudPriceTxt.Value = "0" allows the new value to automatically be visible in the form while using Me.Auditorium_Cost.Value = "0" causes the new value to only become visible in the form upon clicking on or tabbing into the text box. From a user perspective this is confusing and annoying.)

Knowing what I know now, I’ll have to change everything in my VBA. But how can I ensure that Access knows when I am referring to the form’s text box and not the table’s field?

 

[rzw0wr]

The questions you are asking are getting a little out of my pay grade.

How does Access tell the difference between the table's field and the text box's name? I have several points in my VBA that tell Access to set the text box values (and not the field values) to certain things based on certain conditions.

Access looks at the name of a control. If you refer to that control you use, in this case, it's name. Access knows that Client_ID and ClientidTxt are the same. Just the name is different. In the forms only, where you have the name changed is the only place you can refer to ClientidTxt . In the queries and tables the name id still Client_ID. Are you starting to see why developers rarely change a field's name?

I understand this may be confusing, I am confused try to explain it.
There are little rules in Access that you learn by breaking them.
Just like the normalization table rule.
Break that rule too much and your life as a database developer just went from bad to worse.

Dale

 

[Reese]

Access looks at the name of a control... In the forms only, where you have the name changed is the only place you can refer to ClientidTxt

Yep, I understand. But if I change all of the text box names to match the names of the fields (which, apparently I need to do), will Access still know that I am referring to the text box name and not the field name?