Tech Support Guy banner
Status
Not open for further replies.

Solved: Search Forms in Access 2010

3K views 10 replies 3 participants last post by  Rockn 
#1 ·
I saw some past threads about creating search/filtering forms in Access 2010, but I'm still having some trouble.

My database has several tables which I've combined in a union query, and I'd like to create a form that will basically search a user name and show me all of the records associated with that name in a subform. So far, I've got an option box for user name, but the subform only shows me a list of all users, not just the one chosen in the option box.

How do I get the subform to list only those records associated with the selected name?

Thank you very much for your help!
 
#3 ·
Thanks for getting back to me so quickly. This is exactly where I'm running into trouble. Apparently, I've forgotten how to do all of that... It's been a long time for me.

So, I've built a query to pull data from the union query, and in the criteria of user name I have this expression "[Forms]![Licensing_Search]![Combo7].[OnChange]". So it's looking to that option field in the form and requerying based on changes. Is that right?

I'm also lost on the OnChange event to requery the form...
 
#4 ·
Personally I would use the "After Update" event procedure of the Combo7 list box.
use the following code
me.subformname.requery
where subformname is the actual name of your subform, the Access VBA Editor will offer you a list of controls which should include the subform name when you type the period (.).

Your Query Criteria should be
[Forms]![Licensing_Search]![Combo7]
 
#6 ·
Success! Thank you!

Now... If I want to create two search fields - say, another for the serial number of the computer - do I need to create a second query? I entered the same information for Combo9, but it's looking to the same query as Combo7, and I think it's just creating a conflict. Is there a way to include "If USERNAME is Null then..." for the query criteria in the serial number field?

To give you some background, I'm creating a simple tool for my technicians to see what users and computers are licensed for which software. My plan is tie this into SharePoint later, once I have all the forms and queries built.

Thanks again for your help!
 
#7 ·
I've tried a few IIF statements, but I'm not sure I'm heading in the right direction with those - and I can't seem to get them to work...

What would you say is the best way to go about creating multiple search fields on this form?

So far the USERNAME search is running perfectly. Thanks for your help with that!
 
#9 ·
It might be better to use the iif(isnull([USERNAME]), "something goes here", "or something else goes here") in a Column heading and then apply the Criteria to that new column, or split it in to 2 columns each with their own Criteria.
To have Access find more than one field that must match their criteria they go on the same criteria Row, if you want it to find both, or either one or the other field matching the Criteria they should go on separate Rows.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top