If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
filtering table data on a form with combo boxes
I need to have a form that has two combo boxes that a user can select options
from, and the records that meet that criteria will appear in datasheet form below the combo boxes. I've gotten some really complicated coding feedback from people, but I'm so new at A2003 (The first time I've been in Access was to try to do this, I've gone through some basic online training classes, but they don't come close to helping with this) I don't even know where to put the code, or how to change it to meet my criteria. I've also tried to do it using queries instead of coding, by copy and changing a query someone posted, but when I create a form or subform based on the query I get an error message "compile error. in query expression 'IIf(IsNumeric(tblStyles.StyleName),AppLoadString( tblStyles.StyleName),tbleStyles.StyleName)'" This error appears twice, and then another box comes up that says "Invalid use of Null" The Form Wizard continues and then says "Forms!SNM Type Form!Combo22" and won't create a new form. So far, all I've been able to do is create my table, and I managed to make two combo boxes in a form that get their data from two corresponding fields on my table. I figured out how to make them do the "SELECT DISTINCT" thing so that they don't show repeating values when you click on the pull down tab. I've tried "disecting" different example of forms that do what I want mine to do, but I don't know enough about access to be able to create mine from scratch based on someone else's form. I think I might need simple, step by step instructions. Thanks so much in advance |
#2
|
|||
|
|||
filtering table data on a form with combo boxes
Okay im pretty new to Access, too, BUT i think i created a form this past
week that does what you want (a little more complex, but the same). So you have a form, and in Design View, your table is in the "Detail" section? And you want your two combo boxes to filter the table? Ill give it a try at helping you out since no one has posted yet. I think the first step will be go to Design View, and the right click to go to Properties of your first combo box. Go to the Event tab, and go to AfterUpdate and click the "..." , then click code builder to go to VisualBasic. Place this code (inserting your combo box name and field/column value from your table that matches your table). Private Sub YOURcombo_AfterUpdate() Dim strWhere As String If Not IsNull(Me.YOURcombo) Then strWhere = strWhere & "([field title combo matches to in table] = """ & Me.YOURcombo & """) " End If Me.Filter = strWhere Me.FilterOn = True End Sub I'll leave it at this so far, to see if i am helping, and to ensure you can get this done. If all works with this step, your combo should filter the table on your form. Adam Ocean ENGINEER. "NukeEng85" wrote: I need to have a form that has two combo boxes that a user can select options from, and the records that meet that criteria will appear in datasheet form below the combo boxes. I've gotten some really complicated coding feedback from people, but I'm so new at A2003 (The first time I've been in Access was to try to do this, I've gone through some basic online training classes, but they don't come close to helping with this) I don't even know where to put the code, or how to change it to meet my criteria. I've also tried to do it using queries instead of coding, by copy and changing a query someone posted, but when I create a form or subform based on the query I get an error message "compile error. in query expression 'IIf(IsNumeric(tblStyles.StyleName),AppLoadString( tblStyles.StyleName),tbleStyles.StyleName)'" This error appears twice, and then another box comes up that says "Invalid use of Null" The Form Wizard continues and then says "Forms!SNM Type Form!Combo22" and won't create a new form. So far, all I've been able to do is create my table, and I managed to make two combo boxes in a form that get their data from two corresponding fields on my table. I figured out how to make them do the "SELECT DISTINCT" thing so that they don't show repeating values when you click on the pull down tab. I've tried "disecting" different example of forms that do what I want mine to do, but I don't know enough about access to be able to create mine from scratch based on someone else's form. I think I might need simple, step by step instructions. Thanks so much in advance |
#3
|
|||
|
|||
filtering table data on a form with combo boxes
As this is your 1st form - try and simplify it but so that it still does what
you need. 1st Create 2 querys based on the table - this is better than using the table Your main form it based on one query Your subform is based on the other Your main form is (I think) alredy done. Create the subform from the 2nd query and show "all" records in the datasheet Save the forms Open the 2nd query in design view In the first column you want to filter - In the criteria row - select the build option and point the the first combo of you "main" form In the 2nd row do the same but point to the 2nd combo on your main form Save Open the main form in design view In both combos open the proerties box and in the AfterUpdate row you want to requery the subform Me.SubFormName.Requery That should work - for more information do a search for Query By Form (or just QBF) Good luck -- Wayne Manchester, England. "NukeEng85" wrote: I need to have a form that has two combo boxes that a user can select options from, and the records that meet that criteria will appear in datasheet form below the combo boxes. I've gotten some really complicated coding feedback from people, but I'm so new at A2003 (The first time I've been in Access was to try to do this, I've gone through some basic online training classes, but they don't come close to helping with this) I don't even know where to put the code, or how to change it to meet my criteria. I've also tried to do it using queries instead of coding, by copy and changing a query someone posted, but when I create a form or subform based on the query I get an error message "compile error. in query expression 'IIf(IsNumeric(tblStyles.StyleName),AppLoadString( tblStyles.StyleName),tbleStyles.StyleName)'" This error appears twice, and then another box comes up that says "Invalid use of Null" The Form Wizard continues and then says "Forms!SNM Type Form!Combo22" and won't create a new form. So far, all I've been able to do is create my table, and I managed to make two combo boxes in a form that get their data from two corresponding fields on my table. I figured out how to make them do the "SELECT DISTINCT" thing so that they don't show repeating values when you click on the pull down tab. I've tried "disecting" different example of forms that do what I want mine to do, but I don't know enough about access to be able to create mine from scratch based on someone else's form. I think I might need simple, step by step instructions. Thanks so much in advance |
#4
|
|||
|
|||
filtering table data on a form with combo boxes
Adam,
I have entered the code as you said, but when I select something in the combo box, nothing happens "Adam" wrote: Okay im pretty new to Access, too, BUT i think i created a form this past week that does what you want (a little more complex, but the same). So you have a form, and in Design View, your table is in the "Detail" section? And you want your two combo boxes to filter the table? Ill give it a try at helping you out since no one has posted yet. I think the first step will be go to Design View, and the right click to go to Properties of your first combo box. Go to the Event tab, and go to AfterUpdate and click the "..." , then click code builder to go to VisualBasic. Place this code (inserting your combo box name and field/column value from your table that matches your table). Private Sub YOURcombo_AfterUpdate() Dim strWhere As String If Not IsNull(Me.YOURcombo) Then strWhere = strWhere & "([field title combo matches to in table] = """ & Me.YOURcombo & """) " End If Me.Filter = strWhere Me.FilterOn = True End Sub I'll leave it at this so far, to see if i am helping, and to ensure you can get this done. If all works with this step, your combo should filter the table on your form. Adam Ocean ENGINEER. "NukeEng85" wrote: I need to have a form that has two combo boxes that a user can select options from, and the records that meet that criteria will appear in datasheet form below the combo boxes. I've gotten some really complicated coding feedback from people, but I'm so new at A2003 (The first time I've been in Access was to try to do this, I've gone through some basic online training classes, but they don't come close to helping with this) I don't even know where to put the code, or how to change it to meet my criteria. I've also tried to do it using queries instead of coding, by copy and changing a query someone posted, but when I create a form or subform based on the query I get an error message "compile error. in query expression 'IIf(IsNumeric(tblStyles.StyleName),AppLoadString( tblStyles.StyleName),tbleStyles.StyleName)'" This error appears twice, and then another box comes up that says "Invalid use of Null" The Form Wizard continues and then says "Forms!SNM Type Form!Combo22" and won't create a new form. So far, all I've been able to do is create my table, and I managed to make two combo boxes in a form that get their data from two corresponding fields on my table. I figured out how to make them do the "SELECT DISTINCT" thing so that they don't show repeating values when you click on the pull down tab. I've tried "disecting" different example of forms that do what I want mine to do, but I don't know enough about access to be able to create mine from scratch based on someone else's form. I think I might need simple, step by step instructions. Thanks so much in advance |
#5
|
|||
|
|||
filtering table data on a form with combo boxes
Wayne,
I also tried it your way, and when I choose something from the combo box, I get this error message "Microsoft Office Access can't find the macro 'Me'. The macro (or its macro group doesn't exist, or the macro is new but hasn't been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under." I've saved everything, but you didn't say to create a Macro, so I didn't, should I also have the macro that Adam suggested? and if so, how do I put both that AND the requery Me that you gave me in the even builder? "Wayne-I-M" wrote: As this is your 1st form - try and simplify it but so that it still does what you need. 1st Create 2 querys based on the table - this is better than using the table Your main form it based on one query Your subform is based on the other Your main form is (I think) alredy done. Create the subform from the 2nd query and show "all" records in the datasheet Save the forms Open the 2nd query in design view In the first column you want to filter - In the criteria row - select the build option and point the the first combo of you "main" form In the 2nd row do the same but point to the 2nd combo on your main form Save Open the main form in design view In both combos open the proerties box and in the AfterUpdate row you want to requery the subform Me.SubFormName.Requery That should work - for more information do a search for Query By Form (or just QBF) Good luck -- Wayne Manchester, England. "NukeEng85" wrote: I need to have a form that has two combo boxes that a user can select options from, and the records that meet that criteria will appear in datasheet form below the combo boxes. I've gotten some really complicated coding feedback from people, but I'm so new at A2003 (The first time I've been in Access was to try to do this, I've gone through some basic online training classes, but they don't come close to helping with this) I don't even know where to put the code, or how to change it to meet my criteria. I've also tried to do it using queries instead of coding, by copy and changing a query someone posted, but when I create a form or subform based on the query I get an error message "compile error. in query expression 'IIf(IsNumeric(tblStyles.StyleName),AppLoadString( tblStyles.StyleName),tbleStyles.StyleName)'" This error appears twice, and then another box comes up that says "Invalid use of Null" The Form Wizard continues and then says "Forms!SNM Type Form!Combo22" and won't create a new form. So far, all I've been able to do is create my table, and I managed to make two combo boxes in a form that get their data from two corresponding fields on my table. I figured out how to make them do the "SELECT DISTINCT" thing so that they don't show repeating values when you click on the pull down tab. I've tried "disecting" different example of forms that do what I want mine to do, but I don't know enough about access to be able to create mine from scratch based on someone else's form. I think I might need simple, step by step instructions. Thanks so much in advance |
#6
|
|||
|
|||
filtering table data on a form with combo boxes
Sorry im not one of the sites experts, but maybe in my last post the" [field
title combo matches to in table] " wasnt clear. For instance, i have a combo named 'cboRigName' which pertains to 'Rig Name' column in my table, so i put [Rig Name] into that line of my last posts code. My combo boxes are in the Form Header. your table is in your form (Detail section) though, right? For my form, i have text boxes in the Detail section of my continuous form (form-properties-format-default view-continuous form) which have their control source (text box-properties-data tab-control source) set to the column of my table i want displayed. Creating a text box for each column of your table and placing them next to each other in the detail section, and moving the form footer up to make the detail section small makes your form display the table. This may also be something i missed. In the form footer, create a text box named 'txtFormFilter' and put this text inside of it: =IIf(Form.FilterOn,Form.Filter,Null) Hope im helping. Let me know whats going on, or if i need to clear anything up. Adam. "NukeEng85" wrote: Adam, I have entered the code as you said, but when I select something in the combo box, nothing happens "Adam" wrote: Okay im pretty new to Access, too, BUT i think i created a form this past week that does what you want (a little more complex, but the same). So you have a form, and in Design View, your table is in the "Detail" section? And you want your two combo boxes to filter the table? Ill give it a try at helping you out since no one has posted yet. I think the first step will be go to Design View, and the right click to go to Properties of your first combo box. Go to the Event tab, and go to AfterUpdate and click the "..." , then click code builder to go to VisualBasic. Place this code (inserting your combo box name and field/column value from your table that matches your table). Private Sub YOURcombo_AfterUpdate() Dim strWhere As String If Not IsNull(Me.YOURcombo) Then strWhere = strWhere & "([field title combo matches to in table] = """ & Me.YOURcombo & """) " End If Me.Filter = strWhere Me.FilterOn = True End Sub I'll leave it at this so far, to see if i am helping, and to ensure you can get this done. If all works with this step, your combo should filter the table on your form. Adam Ocean ENGINEER. "NukeEng85" wrote: I need to have a form that has two combo boxes that a user can select options from, and the records that meet that criteria will appear in datasheet form below the combo boxes. I've gotten some really complicated coding feedback from people, but I'm so new at A2003 (The first time I've been in Access was to try to do this, I've gone through some basic online training classes, but they don't come close to helping with this) I don't even know where to put the code, or how to change it to meet my criteria. I've also tried to do it using queries instead of coding, by copy and changing a query someone posted, but when I create a form or subform based on the query I get an error message "compile error. in query expression 'IIf(IsNumeric(tblStyles.StyleName),AppLoadString( tblStyles.StyleName),tbleStyles.StyleName)'" This error appears twice, and then another box comes up that says "Invalid use of Null" The Form Wizard continues and then says "Forms!SNM Type Form!Combo22" and won't create a new form. So far, all I've been able to do is create my table, and I managed to make two combo boxes in a form that get their data from two corresponding fields on my table. I figured out how to make them do the "SELECT DISTINCT" thing so that they don't show repeating values when you click on the pull down tab. I've tried "disecting" different example of forms that do what I want mine to do, but I don't know enough about access to be able to create mine from scratch based on someone else's form. I think I might need simple, step by step instructions. Thanks so much in advance |
Thread Tools | |
Display Modes | |
|
|