By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,287 Members | 1,524 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,287 IT Pros & Developers. It's quick & easy.

Setting up a subform in a search form

P: 2
I am very very new to access trying to get a database set up for a new company I work for. So I apologize for the long post. I am not sure what all information is needed to help me and I don't want to leave anything out that may be important. Any information helps. tips ideas step by step instructions. :) anything and everything lol
I am using access 2013.
I have a table that includes all employee information.
I have queries set up to pull certain types of information.
I am creating a search form but am having some trouble getting it set up.

My First and Main concern:
On my search form I would like textbox for First name, Last name and employee number. (so a box where you can enter your search criteria)

Then I would like a dropdown menu box that has options for contact, emergency, personal, company and timesheet.

And I think I will need a "search" button to bring up the data I am looking for.

Now I can set all of those up to go along with my queries for each option of the dropdown menu but when I click the button it will bring up the query in a new tab. What I am hoping to do is bring up the information from a query on the same form that enters the search criteria. I know this is possible with a subform (I think) but I have not figured out how.

Secondary less important issue:
My table includes all employee information. I have a query set up for each type of information. For example I have one that just shows contact info, one for emergency, one for personal, one for company. Right now my search button will open the correct query based off of the selection made from the corresponding drop down menu. The navigation pane looks very messy though with so many queries. Is it normal for a data base to have a lot of those created? or is there a way I can set up one query with all info to just show certain columns based on the dropdown menu?
Jan 14 '15 #1
Share this Question
Share on Google+
3 Replies

P: 104
You only need one query for the form you are working on. There are multiple ways to filter the results you show on the form such as referring to a particular control on the form itself in the query criteria row (syntax: [Forms]![FormName]![ControlName]) or taking advantage of the Filter property of the form using VBA:
Allen Browne Flexible Search Form.
I would recommend setting the Default View property on the Format tab of the form to Continuous Forms and putting your controls used to filter the results in the header section. In the detail section you can show any or all of the fields. Make sure you give the controls in the header good names because you will be referring to them in the query.
The first option is much easier to implement but it is limited in that all of the fields must have criteria for it to return any results. If you use this method you also need to put After Update events on all of the controls to re-query the form's query.
The second option is quite a bit more complicated if you don't know VBA, but if I understand what you are trying to do correctly this type of form isn't really that simple for a beginner. I can remember struggling trying to learn how to make these forms. Anyway I hope this helps. Let us know if you have any questions, but try to be specific. These forums work much better for narrow topics.
Jan 15 '15 #2

P: 2
Thank you so much for your response. I have this posted on a few different sites I have been using for help and your answer has made the most sense to me. So again thank you super much!

From the website you posted the first option is sort of what I have set already. Only as I mentioned it opens the query in a new tab and not on the form itself. Using that way I do not see how I can set it up with just one query but as you said the second option looks very hard and complicated for my area of expertise. (which is very beginer lol) I did open the sample database which brings up my question....

It looks like exactly what I need to do. but under the results each time you search it shows the same fields. I created one query that includes all of the employee info and am going to need to be able to select lets say contact and have it show name, phone number address. then ill change to company and will need results with start date, starting pay etc. Is that possible with this example VBA? I do not want to get halfway through and find it is not.
I hope I am making sense. I know this is not a narrow topic so I appreciate the insight.
Jan 15 '15 #3

P: 104
You would be surprised how much you can really take control of anything in Access using VBA, but I feel you are getting ahead of yourself. I'm starting to get a better idea of what you are trying to do. I think you want to make several different pop up forms that show exactly the information you need for each different scenario. You can use one 'master' form so to speak to collect your filter criteria and include different buttons on that form to open up your specific pop ups. For example click one button to open the contact info form, click another button to open the company info form. Each of these forms can have their own query, but I would try to keep it simple and create a master query with all of the info you need, then use your forms to show only what is necessary. Btw are all of these forms based off of a single table? If so, then you definitely only need one query. However you decide to proceed just be consistent and aware of what your saved queries' purpose is, and always stick to a logical naming convention for everything.
There are a couple things at work here that you need to be familiar with. First of all, you have an underlying query that the form is based on (or possibly multiple forms), but you can also have different queries that serve as the Row Source for your controls. These queries are not saved queries that will show up in the navigation pane, but they are stored within the form, or more specifically the control itself.
I'm not really sure what you mean by the query opens up in a new tab of its own. I think you have something wrong here. Do you mean the form opens in a new tab?
Jan 15 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.