 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008Searching Check Boxes
Hi Could someone please assist? I currently have a form with various check boxes. I was wondering if it was possible to create a form on top of this and have a list of all the check boxes that when a combination were checked would bring up the records with the same combination behind on the main form. Am I asking too much from access by requesting this. If some one does know the answer please reply in basic terms as I easily get confused. For the form you bring up on top (Form1), create a query as its Record Source. Include all the check boxes on Form1 in the query. In the Criteria row of the query, put the name of the control on Form1 the check box is bound to: Field: Check1 Check2 Criteria: Forms!Form1!txtCheck1 Forms!Form1!txtCheck2 and so on.  SignatureDave Hargis, Microsoft Access MVP > Hi > [quoted text clipped - 6 lines] > If some one does know the answer please reply in basic terms as I easily get > confused. Dave Let's say that I'm useless at this and have not done it before. How then would I do it? Speak to me like the idiot that I am. I have some more basic knowledge but is it possible for step by step process please. Thanks > For the form you bring up on top (Form1), create a query as its Record Source. > Include all the check boxes on Form1 in the query. [quoted text clipped - 16 lines] > > If some one does know the answer please reply in basic terms as I easily get > > confused. You mentioned two forms. One that is open and has the check boxes on it. The other is a form you want to open and filter records so only those that have the check box fields check that match the check boxes on the first form. Is that correct? Tell me what you have done so far, so I know where to start. SignatureDave Hargis, Microsoft Access MVP > Dave > [quoted text clipped - 24 lines] > > > If some one does know the answer please reply in basic terms as I easily get > > > confused. I have the form with all the check boxes on and the form which holds the database information. Do I need to create a search button on the database form? That's as far as I have got so far!! > You mentioned two forms. > One that is open and has the check boxes on it. [quoted text clipped - 33 lines] > > > > If some one does know the answer please reply in basic terms as I easily get > > > > confused. No, you don't need a search button on the database form. If you are using a table as the record source for the database form, you need to change it to a query. Then for each check box field in the query, you need to enter the name of the form and the control on the check box form that relates to that check box field. For example, lets say that in your table, you have a field named CheckOne. One the check box form lets say you have a control named txtCheck01 and that when you check that check box, you want all the records that have the field CheckOne checked in the table to be included in the records for the database form. So in your query, you would find the column for the field CheckOne. In that column in the Criteria row, you would reference the check box on the check box form by putting the form name and control name it relates to. For example, it this case, it would be: Forms!CheckBoxFormName!txtCheck01 You would do the same for each check box on the check box form. SignatureDave Hargis, Microsoft Access MVP > I have the form with all the check boxes on and the form which holds the > database information. Do I need to create a search button on the database [quoted text clipped - 39 lines] > > > > > If some one does know the answer please reply in basic terms as I easily get > > > > > confused. Unfortunately this isn't actually only bringing up the entries that collaborate with the entries I'm searching for! What I'm rtrying to do is filter the results using the check boxes. > No, you don't need a search button on the database form. > If you are using a table as the record source for the database form, you [quoted text clipped - 59 lines] > > > > > > If some one does know the answer please reply in basic terms as I easily get > > > > > > confused. Let's say your search parameters form is named frmFind, and the form with the filtered recordset is frmMain. frmFind is unbound, and frmMain is bound to qryMain, which is based on the table containing the records you want to filter. You can open frmFind, select the check boxes, then click a button to open frmMain. For this example, the check boxes have the same names on both form. frmMain is based on the query qryMain, which includes the fields (columns) Check1, Check2, etc. In the Criteria row for Check 1: Forms![frmFind]![Check1] Similarly for Check2: Forms![frmFind]![Check1] After selecting the check boxes on frmFind, click a command button to open frmMain. The command button wizard can help with this. If you would rather not use the wizard, add code something like this to the command button's Click event: DoCmd.OpenForm "frmMain" Me.Form.Visible = False Hiding the form is optional. Try it without first. Since frmMain is based on qryMain, and qryMain has criteria based on the frmMain selections, frmMain will display only the records with check boxes matching those on frmFind. If you have frmMain open and want to perform another search, a command button on frmMain can unhide frmFind: Forms![frmFind].Visible = True If you are going with this approach, after selecting the check boxes on frmFind you probably need to see if frmMain is already open. If it is, requery it; if not, open it. The command button on frmFind, rather than simply opening frmMain as described above, will instead have something like: If CurrentProject.AllForms("frmMain").IsLoaded Then Forms![frmMain].Requery Else DoCmd.OpenForm "frmMain" End If > Unfortunately this isn't actually only bringing up the entries that > collaborate with the entries I'm searching for! What I'm rtrying to do is [quoted text clipped - 84 lines] >> > > > > > I easily get >> > > > > > confused. Sorry Bruce Could you please simplify this process. As I am unsure of what you mean. Thanks > Let's say your search parameters form is named frmFind, and the form with > the filtered recordset is frmMain. frmFind is unbound, and frmMain is bound [quoted text clipped - 126 lines] > >> > > > > > I easily get > >> > > > > > confused. You have to describe something of the database's purpose and structure. In particular, what is the table in which these Yes/No fields occur, and what choice is being made by checking the box? All of the replies in this thread have been in terms of made-up table, form, query, and field names. Rather than me or Dave continuing to attempt to explain by assigning the database a hypothetical purpose, and the table and check boxes arbitrary names, state in real-world terms what you hope to accomplish. If you are unusre what a reply means, be specific about what puzzles you rather than asking volunteers to attempt a new explanation starting from the beginning. People reply to questions in this forum because they are willing to help (as many of us have been helped in the past), but since the generic reply is not getting it done, you as the person with the question have to provide enough details that the response can be specifc. > Sorry Bruce > [quoted text clipped - 156 lines] >> >> > > > > > I easily get >> >> > > > > > confused. Hi Bruce Basically I need to be able to determine weather someone is an expert in a particular field. So far I have around 50 check boxes which I need to be able to filter. I need to search for certain experts for a guideline group. The check boxes are to say that the person is actually an expert in a specific field. I hope this gives some more information about what I need to achieve. Thanks Richard > You have to describe something of the database's purpose and structure. In > particular, what is the table in which these Yes/No fields occur, and what [quoted text clipped - 170 lines] > >> >> > > > > > I easily get > >> >> > > > > > confused. Your database design almost surely is not correct. Rather than all of those check boxes (and Yes/No fields) you would do better to store the information in a related table. Working within the limitations of your current design, you stated earlier that you "have the form with all the check boxes on and the form which holds the database information". A form does not hold information (except temporarily while the form is open). Tables hold information. The form "with all the checkboxes on" should be an unbound form (I will call it frmSearch) if you are using it to set up the record source for "the form which holds the database information". The form that holds the database information (I will call this form frmMain) is bound a query based on the table that holds the Yes/No fields and the rest of the information. I will call this table tblMain. Back to the unbound form frmSearch. Each check box corresponds to a Yes/No field in tblMain. I will call the table fields Check1, Check 2, etc., and the check boxes on frmSearch chkCheck1, chkCheck2, etc. Add a command button to open frmMain. The wizard can help with this. You need a query based on frmMain. If you do not have this query, go to the database window and create a query in design view. Choose the table tblMain. Add all of the fields from the table, one by one. In the Criteria row below Check1 (the name I am giving to your first Yes/No field), put this: Forms![frmSearch]![chkCheck1] If you checked the check box chkCheck1 on frmSearch, the query will return only the records for which Check1 is True (or Yes). Do the same for the rest of the Yes/No fields. Save the query (I will call it qryMain). Base frmMain on qryMain. That is, use qryMain as its Record Source. To use, select the desired check boxes on frmSearch, and click the command button to open frmMain. The query (qryMain) will look at the check boxes on frmSearch, and will return only the records that match the frmSearch selections. If you checked Yes in chkCheck1 and chkCheck2 on frmSearch, and left the rest of the check boxes unchecked, qryMain will return only the records in which Check1 and Check2 are Yes and the rest of the Yes/No fields are No. Remember, frmMain is based on qryMain, so on frmMain you will see only the records that are in qryMain. > Hi Bruce > [quoted text clipped - 216 lines] >> >> >> > > > > > I easily get >> >> >> > > > > > confused. Hi Bruce I have done exactly what you suggested and it seems not to return any information what so ever. It just says filtered 1 of 1 and does not display any othe information than the check box i have checked. > Your database design almost surely is not correct. Rather than all of those > check boxes (and Yes/No fields) you would do better to store the information [quoted text clipped - 260 lines] > >> >> >> > > > > > I easily get > >> >> >> > > > > > confused. Then you have not done exactly what I suggested. I have written some detailed responses, but you come back with a sentence or two. I need some information so that I am not making up the names of forms, fields, tables, and queries. What is the name of the search form? Provide the names three of the fifty check boxes on the search form. What is the name of the table in which the information is stored? Provide the names of the three fields that correspond to the three check boxes on the search form. What is the name of the query in which the criteria (e.g. Forms![SomeForm]![SomeTextBox]) are entered? > Hi Bruce > [quoted text clipped - 314 lines] >> >> >> >> > > > > > I easily get >> >> >> >> > > > > > confused. Sorry Bruce The forms are: frmMain frmSearch The query is qryMain The table is tblMain Three of the check boxes on tblMain are Business & Enterprise, Careers Counselling, Commissioning/Contracting they are the same on the search form. I really do appreciate all of your help. Thank You Mr Bruce Richard > Then you have not done exactly what I suggested. > [quoted text clipped - 271 lines] > >> >> >> >> > > on > >> >> >> >> > > the Open qryMain in design view. In the Criteria row of the Business & Enterprise column you should have: Forms![frmSearch]![Business & Enterprise]. Similarly, in the Criteria row for Careers Counseling: Forms![frmSearch]![Careers Counseling] In the Criteria row for Commissioning/Contracting: Forms![frmSearch]![Commissioning/Contracting] frmMain is based on qryMain (qryMain is its Record Source). Remember, frmSearch is unbound (it has no Record Source). It has check boxes with the names Business & Enterprise, Careers Counselling, and Commissioning/Contracting. I would set the Default Value of all check boxes on this form to No. frmSearch also has a command button to open frmMain. The wizard can help with that. If you check Yes for [Careers Counseling] on frmSearch and leave the rest as No (blank), then click the command button to open frmMain, since frmMain is based on qryMain, and qryMain checks for values on frmSearch, frmMain will load with only the records in which [Careers Counseling] is Yes and all other Yes/No fields are No. This means all Yes/No fields in tblMain (and on frmMain) have to be either Yes or No. They can't be blank. Setting the Default Value of the Yes/No check boxes to No on frmMain should let you accomplish this for new records, but will not change existing records. You could add IsNull to the Or row under the Criteria row in query main if some of the Yes/No fields are blank, but your query is already very cumbersome if it has criteria for 50 Yes/No fields. By the way, you really should avoid special characters such as ampersands and slashes in field and control names. Such names should consist of letters, numbers, underscores, and nothing else (including spaces). Access can get confused otherwise. > Sorry Bruce > [quoted text clipped - 338 lines] >> >> >> >> >> > > on >> >> >> >> >> > > the Hi Bruce Everytime I try to select a field it says "Control can't be edited it's bound to unknown field". Is there any reason why this would? Have I done something wrong? Please help!! ALso all the check boxes are greyed out!!! Thanks for your time Richard > Open qryMain in design view. In the Criteria row of the Business & > Enterprise column you should have: [quoted text clipped - 278 lines] > >> >> >> >> boxes > >> >> >> >> on Select a field where? Are you talking about selecting a control (such as a text box) on a form? If so, which form? Grayed out on frmSearch or frmMain? If on frmSearch, try setting their Default Value to 0, as I suggested. > Hi Bruce > [quoted text clipped - 333 lines] >> >> >> >> >> boxes >> >> >> >> >> on I had done that earlier and it still isn't working. It's on frmSearch that it state what I said earlier. I am tempted just to give up and not use Access as it doesn't seem to do what I need. I don't understand. On why on frmMain the check boxes stay checked. Then on frmSearch they won't let me tick them and they won't let me do anything even in the text boxes on the form. What should I do? > Select a field where? Are you talking about selecting a control (such as a > text box) on a form? If so, which form? Grayed out on frmSearch or [quoted text clipped - 274 lines] > >> >> >> >> >> form > >> >> >> >> >> with Also When I open up qryMain nothing is in there. But when I go to design view within qryMain all the information is there could this be my problem. > I had done that earlier and it still isn't working. It's on frmSearch that > it state what I said earlier. I am tempted just to give up and not use [quoted text clipped - 274 lines] > > >> >> >> >> > > > >> >> >> >> > Could you please simplify this process. Design view shows you fields and other information, but no records. If there are no records when you open qryMain there will be no records in a form bound to qryMain. My other reply has details. > Also When I open up qryMain nothing is in there. But when I go to design > view within qryMain all the information is there could this be my problem. [quoted text clipped - 332 lines] >> > >> >> >> >> > >> > >> >> >> >> > Could you please simplify this process. You had done what earlier and it still isn't working? Create a new qryMain based on tblMain. Add all of the fields from tblMain, but do nothing else unless you want to choose a field by which to sort. Switch to Datasheet view for the new query. Do you see all of the records? If you see all of the records, use the new qryMain as the Record Source for frmMain. Are you clear on how to do that? If you have successfully used the new qryMain as the Record Source for frmMain, you will see all of the records when you switch to Form View for frmMain. Do you understand what I'm saying by "switch to Form View"? If this has worked so far, is each check box on frmMain bound to a Yes/No field in qryMain? Do you understand what I'm asking? We'll get to frmSearch later, but for now remember: **It is an unbound form (it has no Record Source). The check boxes on it are therefore unbound. That is, they DO NOT HAVE A CONTROL SOURCE.** Are you clear on what I am saying? If frmSearch is unbound, and each of the check boxes on it are unbound, that is good. The next thing you need to do is change the Default Value of each check box to False. Are you clear on how to do this? If so, switch to Form View. If the check boxes are gray, click them to add a check mark, then click them again to make it go away. I don't know why this may be necessary, but in my experience it seems to be the case. I have asked several specific questions. You must answer them if I am to help. I'm not trying to be condescending by asking if you understand, but rather I need to know if you understand the terminology. I am willing to explain, but not to guess. To tell me that "it" isn't working when I have made a number of suggestions tells me nothing. Also, you must follow my instructions about starting over with a new qryMain. >I had done that earlier and it still isn't working. It's on frmSearch that > it state what I said earlier. I am tempted just to give up and not use [quoted text clipped - 320 lines] >> >> >> >> >> >> form >> >> >> >> >> >> with If this has worked so far, is each check box on frmMain bound to a Yes/No field in qryMain? Do you understand what I'm asking? No I don't understand. I don't have yes/no fields on frmMain I have check boxes. Exactly the same check boxes that are on the frm Search. How do I know if the frmSearch check boxes are unbound? How do I find out about a control source? I have also started a new qryMain do I need to put in the code you said earlier Forms![frmSearch]![Commissioning/Contracting]? I have not changed anything in frmSearch at this time. I really appreciate your help with this probably really simple Access Task. Regards > You had done what earlier and it still isn't working? > [quoted text clipped - 276 lines] > >> >> >> >> > > >> >> >> >> > Basically I need to be able to determine weather someone is an > If this has worked so far, is each check box on frmMain bound to a Yes/No > field in qryMain? Do you understand what I'm asking? > > No I don't understand. I don't have yes/no fields on frmMain I have check > boxes. Exactly the same check boxes that are on the frm Search. Tables and queries have fields. Data are stored in tables; queries provide a way to sort, filter, and otherwise manipulate the data. Forms have controls, which include text boxes, check boxes, labels, lines, and about anything you can put onto a form. This is very important: A form cannot store data. It can only serve as a sort of portal to the data stored in a table. An unbound form can be useful in lots of ways, but not for data storage. The data are not in the form, then are in the table. Let's say you have a table consisting of a FirstName field and a LastName field. You build a form with that table as its Record Source. Now you add two text boxes to the form; set the Control Source of one text box to FirstName and the other to LastName. Switch to Form View. Add a first name and a last name. Use the buttons at the bottom of the form to go to a new record. Add another first name and last name. Just those two names. Now if you look at the table you will see two records, one for each first name/last name combination. I asked before if you know what I mean by Record Source, Control Source, and Form View. You need to answer the questions. > How do I know if the frmSearch check boxes are unbound? How do I find out > about a control source? > > I have also started a new qryMain do I need to put in the code you said > earlier > Forms![frmSearch]![Commissioning/Contracting]? NO! Make sure it shows you all of the records in tblMain first. If that works we can start to filter it. > I have not changed anything in frmSearch at this time. We'll concentrate on frmMain for now; frmSearch can wait. > I really appreciate your help with this probably really simple Access > Task. > > Regards Please review allof the questions I have asked, and believe that I am asking for good reasons. >> You had done what earlier and it still isn't working? >> [quoted text clipped - 320 lines] >> >> >> >> >> > Basically I need to be able to determine weather someone is >> >> >> >> >> > an > If this has worked so far, is each check box on frmMain bound to a Yes/No > field in qryMain? Do you understand what I'm asking? [quoted text clipped - 340 lines] >> >> >> >> >> > Basically I need to be able to determine weather someone is >> >> >> >> >> > an |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.