Solved Pass a textfield value to a query (1 Viewer)

[yamus]

Hi
I am making an access app to administer computer equipement inventory
I have a form with textfield to search equipements by serial number
I also have a built access query used for this search
I used the expression builder to pass the textfield to the corespondent query field criteria
An error message appears
'Too few parameters'
Any help please

 

[pbaldy]

The error implies you're trying to use the query in a recordset or something similar? If so, the simplest solution is to wrap the form reference in the Eval() function.

 

[theDBguy]

Hi. Welcome to AWF!

Also, please see if this generic function helps. Good luck!

Code Snippets

 

[yamus]

The error implies you're trying to use the query in a recordset or something similar? If so, the simplest solution is to wrap the form reference in the Eval() function.

The error implies you're trying to use the query in a recordset or something similar? If so, the simplest solution is to wrap the form reference in the Eval() function.

I tried this like you suggested
Eval([forms]![form1]![txtField])
I wraped the whole thing in Eval() but nothing has changed
Same error is keep occuring

 

[pbaldy]

The argument must be in quotes:

Eval("[forms]![form1]![txtField]")

 

[yamus]

The argument must be in quotes:

Eval("[forms]![form1]![txtField]")

I tried it again and now i get the "with block variable not set" error

 

[theDBguy]

I tried it again and now i get the "with block variable not set" error

Hi. By any chance have you looked at the link I posted earlier? Just curious...

 

[arnelgp]

you can just add a Criteria to your Query with any name, eg:

Criteria: [p1]

now, on you form (click event of the button, etc.), set the parameter using:

DoCmd.SetParameter "p1", Me![txtField]

you can then open the query/report/form based on the Query:

DoCmd.OpenQuery "yourQueryName"

 

[yamus]

Hi. By any chance have you looked at the link I posted earlier? Just curious...

Hi
I checked your link. It s just that i am new to access and and i am kinda in hurry to make the app. I don't inderstand generic functions
It would be very generous of you if you show me a real example
Thanks

 

[arnelgp]

see this sample.

 

[yamus]

see this sample.

Hello
I understood the example very well but when i tried to execute the query within vba using recordset i had the 'with block variable not set error'
the reswt of your code is without change

 

[arnelgp]

you only use SetParameter on opening Queries, Reports, Forms but not recordset.
you need different approach there:

Set db = CurrentDb
Set qd = db.QueryDefs("Query1")
qd.Parameters(0) = Nz(Me.txtSearch, "ZZZZZ")
Set rs = qd.OpenRecordset(dbOpenDynaset)
Set qd = Nothing
Do Until rs.EOF
    MsgBox rs!FirstName
    rs.MoveNext
Loop

 

[theDBguy]

Hi
I checked your link. It s just that i am new to access and and i am kinda in hurry to make the app. I don't inderstand generic functions
It would be very generous of you if you show me a real example
Thanks

Hi. If you copied the generic function into your project, you can replace this:

Set rs = db.OpenRecordset("Query1", dbOpenDynaset, dbSeeChanges)

with this one:

Set rs = fDAOGenericRst("Query1", dbOpenDynaset, dbSeeChanges)

Hope that helps...

 

[yamus]

The error implies you're trying to use the query in a recordset or something similar? If so, the simplest solution is to wrap the form reference in the Eval() function.

Now i tried what you suggested. but a message errors that says

Hi. If you copied the generic function into your project, you can replace this:

Set rs = db.OpenRecordset("Query1", dbOpenDynaset, dbSeeChanges)

with this one:

Set rs = fDAOGenericRst("Query1", dbOpenDynaset, dbSeeChanges)

Hope that helps...

Hi
I tried it and it worked fine

I appreciate your help. Thank you so much
I'd like to take your permission to post the generic function use with particular instruction for users who use access in other languages( i use french version) beacuse i encountred a problem while using the form reference. The expression builder names 'form' as 'formulaire' in french. And when using the generic function, an error occured about that access did not find the reference for 'formulaire'. So i changed it back to 'form' in the exoression builder and it worked fine
Once again, i am grateful for your help
Thank you and good luck

 

[theDBguy]

Now i tried what you suggested. but a message errors that says

Hi
I tried it and it worked fine

I appreciate your help. Thank you so much
I'd like to take your permission to post the generic function use with particular instruction for users who use access in other languages( i use french version) beacuse i encountred a problem while using the form reference. The expression builder names 'form' as 'formulaire' in french. And when using the generic function, an error occured about that access did not find the reference for 'formulaire'. So i changed it back to 'form' in the exoression builder and it worked fine
Once again, i am grateful for your help
Thank you and good luck

Hi. Glad to hear you got it sorted out. You can repost the code in French, but I would request a link back to the original source, please. Good luck with your project.

PS. Once you have posted it, you can email me a link to your post, so I can also add it to my site. Cheers!

 

[arnelgp]

you can use the generic code provide by the moderator.
but if your parameter is not generic, say, [p1], [p2], it will cause
problem.
since you know that you use qbf on your query, so you use the
"generic" function, in which case it is not generic anymore since
you know before hand that the parameter is in the
form "Forms!formname!controlname".