Query Parameters question (1 Viewer)

[Rakier]

This may be a simple problem, but I'm stuck right now.

I have two different user forms. They each have a text box called strSearchCriteria. What I want to do is run a query based on the value of the strSearchCriteria box. I further want to limit the results of the query to be items that are used by the user.

To further explain:

FrmUser1 is for the user who will have an ID of 1 and frmUser2 is the form for the user with an ID of 2.

I can build individual queries for each of the user forms and they work fine. However, I have 3 more users and was hoping to limit the number of queries I had to build.

Is there away to pass the form name to the query? Right now, the criteria field is set like [forms]![frmUser1].[numID]. However, I would like to use the same query for all of the forms since they look up similar information dependant on what form they are being generated from.

Any ideas?

 

[Rob.Mills]

If you want to limit what records are viewed by a certain user what you're going to have to do is have a field in the data table for the UserID. If you've got user-level security setup then you can set your criteria to CurrentUser()

 

[Rakier]

I don't want to limit the records viewed by the user in that way. What I have is several department heads that have different categories of items that they use.

What I want to do is let them enter search criteria and click on a button that will pull up items in a list. These items need to be limited by their category and their user. Several of these items are utilized in multiple departments, so the user id or ids have to be sent to the query along with the search criteria and the department.

For example:

Item A is a Chemical used by User 1 and 4. The user Id for the item is set to 14

Item B is a Chemical used by User 1 only. The user Id is 1.

Item C is a Clerical supply used by User 4. The user id is 4.

Item D is a Chemical used by User 4 only. The user id is 4.

What I want to do is have User 1 enter a partial description of the item. Then they click on the item category, say Chemical. I then want it to list Item A and B. When User 4 does the same, it will list Item A and Item D.

Like I said before, I can get it to work fine by using individual querys, but I was hoping for some method to use the same query and have the form send the value of the form name and field.

Can't seem to get it to work.

 

[Rob.Mills]

Well give this a try. Say the textbox on the form where they enter the user id is txtUserID. Make the criteria

Like "*" & Forms!FormName!txtUserID & "*"

 

[Rakier]

I do it that way, however that makes me have to build seperate queries for each form.

What I'm trying to do is pass the form name, along with the text box name as a parameter to the query. This way, two (or 5) different forms can use the same query, just passing the name of the form with the name of the text box.

 

[Rob.Mills]

Ok. I think I got you. I don't know for sure this will work in a query but give it a shot. Otherwise I don't know where to go with this.

Like "*" & Screen.Activeform!txtUserID & "*"

The textboxes will have to be named the same on each form. And like I said I know you can use screen.activeform in VBA, just don't know if it will work in a query.

 

[Rakier]

It seems to work fine.

Thanks for the help. I never would have thought of that!