Query from an Unbound Text Box with function builder

[dtrynoski]

I have an unbound text box on a form I created titled frmDS_BULK_DATA_VALIDATION, the unbound text box is titled Text7. I'm trying to create a simple query which populates whatever is entered in the unbound text box within a field on the query titled ISS_ID. So I used the expression builder to put the following within the ISS_ID Field:

"[Forms]![frmDS_BULK_DATA_VALIDATION]![Text7].[Text]"

created a macro which runs this query and attached it to a botton on my form. When I try to run it however, I get the following message:

Enter Parameter Value:
[Forms]![frmDS_BULK_DATA_VALIDATION]![Text7].[Text]
Then a blank field where I can enter data

I'm assuming my query isnt reading the form correctly but I can't figure out whats up, please advise..

Thanks

 

[PNGBill]

It looks like you are telling the form control to use a string for it's control source and it is asking what that means.

You can use a function as a form control source eg =fncMyFunction(). Assuming you created a public function named fncMyFunction().

Access has functions available including DLookup which allows you to get a field from a table to populate your form control. I guess it could also fet a field from a query.

Check DLookup on your F1 (help)

 

[dtrynoski]

Thanks for the response, I tryed the Dlookup function and it still doesnt seem to be able to find the text I entered in the unbound text box since it basically gives me the same message to enter a parameter value.

 

[PNGBill]

Sorry, I misread your first post and was advising how to put a value from a query into a form unbound control.

To get the value from the form control to a query...

If you have a Button on your form then you can have code in the On Click event of the button to do the task you want.

The code will put the data in the form control into the sql and run same.

Can you post the sql ? - use the hash key in the forum to post code.

 

[dtrynoski]

I ended up at least figuring out why that original error was occuring. Turns out I had some random VB code in my DB that was causing it to misread something. But now I'm having another error. I assigned an action to the button that essentially runs the query when pressed, BUT.. It returns a blank result, oddly enough, I can get it to return the intended result if I randomly click off the botton somewhere on the form. Thoughts?

 

[PNGBill]

But now I'm having another error. I assigned an action to the button that essentially runs the query when pressed, BUT.. It returns a blank result, oddly enough, I can get it to return the intended result if I randomly click off the botton somewhere on the form. Thoughts?

What do you mean ? do you click in the body of the form and the query runs ?

 

[dtrynoski]

Yes exactly. It almost seems like when I click the button to run the query, it thinks the text box is empty, but when I click the bottom of the form, it somehow reads whats in the text box and runs the query as intended. Is there some sort of event I have turned on?

 

[Alansidman]

Here's a tutorial on building search forms to execute queries.

http://www.datapigtechnologies.com/flashfiles/searchform.html

How does your approach stack up to this?

Alan

 

[PNGBill]

Yes exactly. It almost seems like when I click the button to run the query, it thinks the text box is empty, but when I click the bottom of the form, it somehow reads whats in the text box and runs the query as intended. Is there some sort of event I have turned on?

Are you sure the code is in the command button On Click Event ?

Can you post the whole code ?

 

[dtrynoski]

Are you sure the code is in the command button On Click Event ?

Can you post the whole code ?

Yes, I believe I've drilled down the problem to it being something in the way I'm querying the data. I followed the instructions in the video posted and I'm able to get the form commands working as intended now, the only problem now is I keep I getting blank results when I shouldnt.
Heres my code;

Query criteria where I want the data from my unbound text box:
[Forms]![frmDS_BULK_DATA_VALIDATIONtst]![Text0].[Text]


Event code on botton which runs the query:
Private Sub Command3_Click()
DoCmd.OpenQuery "qryAdhoc", acViewNormal
End Sub


Is there some sort of other command or setting I need on my unbound text box??

 

[dtrynoski]

I should also add, this is an ODBC db table I'm searching off of.

 

[Alansidman]

This looks like you have one to many items in your criteria. The syntax if you are not using a subform is:

Forms![FormName].[ControlName]

where the ControlName is the name of the unbound textbox. I am confused by your extra control.

Alan

 

[dtrynoski]

I tryed that query as well with no luck. I ended up getting it to work, I selected the Compact & Repair db option and its now returning values. Not exactly sure why that changes things. Thanks everyone for your help though. Much appreciated.

 

[PNGBill]

Query criteria where I want the data from my unbound text box:
[Forms]![frmDS_BULK_DATA_VALIDATIONtst]![Text0].[Text]

What is the red part of your code pointing to ?

If this is a table field name or form control name you really shouldn't use "Text".
Apart from the Reserved Word possibility it is pretty un imaginable.

txtFirstName, txtHighNum, txtWorkDay can mean something and this helps a lot when you have to sort out issues in your code later.

Alansidman's question is worth responding to.
Sometimes bad code can work and later when it causes an issue, it is hard to find the problem.

 

[dtrynoski]

That part of the query was something that I added through the expression builder. I've since removed it but it was still working despite that part, it was in the "Expression Values" box.

Thanks again for all your help.

 

[PNGBill]

Good news