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
|
|||
|
|||
"How to filter form records based on combo box value?
I have a form opening with a Select Query displaying ALL records. I placed
an Unbound combo box that looks up values in a separate table on the form. What I want to do is... Select a value from the combo box and filter the form for records using the selected value in the combo box. The field name is Competitor in the form's underlying query. The combo box is named Comp. No matter what I do I get zero (0) records retrieved after update of the combo box. Any help would be greatly appreciated. Stu |
#2
|
|||
|
|||
"How to filter form records based on combo box value?
I'm wondering if you might have a table somewhere with a "lookup" data field
involved in this description. Lookup data fields store one value, but display another (looked up) value. This can lead to confusion in forms, queries, etc. Regards Jeff Boyce Microsoft Office/Access MVP "hdfixitup" wrote in message ... I have a form opening with a Select Query displaying ALL records. I placed an Unbound combo box that looks up values in a separate table on the form. What I want to do is... Select a value from the combo box and filter the form for records using the selected value in the combo box. The field name is Competitor in the form's underlying query. The combo box is named Comp. No matter what I do I get zero (0) records retrieved after update of the combo box. Any help would be greatly appreciated. Stu |
#3
|
|||
|
|||
"How to filter form records based on combo box value?
In the AfterUpdate event of the combobox, put this code:
Me.FilterOn = True Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = " & Me.Comp This assumes the bound column of the combobox is numerical. If it's text, put this: Me.FilterOn = True Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = '" & Me.Comp & "'" HTH, Barry "hdfixitup" wrote: I have a form opening with a Select Query displaying ALL records. I placed an Unbound combo box that looks up values in a separate table on the form. What I want to do is... Select a value from the combo box and filter the form for records using the selected value in the combo box. The field name is Competitor in the form's underlying query. The combo box is named Comp. No matter what I do I get zero (0) records retrieved after update of the combo box. Any help would be greatly appreciated. Stu |
#4
|
|||
|
|||
"How to filter form records based on combo box value?
Barry,
I did as instructed and got a Syntax error message. I replaced MyTable with tblInterchange as defined in Tables of my database in both MyTable instances. I noticed on the Navigation Bar at the botom of form that the word Filtered appears, but ALL records are displayed and not the selected value in the combo box. I remove the applied filter and get ALL records with NO Filtered showing. It seems we are very close. Syntax Error??? The form's underlying query is based on tblInterchange and opens with ALL records displayed. I added the combo box (Comp) on the form and it's Row source is a Select query (tblCompetitiveLine) with two fields. The first field on the grid is a numeric field and the Bound field. The combo box on the form displays the value in the second field (Comp). Hope this helps. Truly apprciated. "Barry Gilbert" wrote: In the AfterUpdate event of the combobox, put this code: Me.FilterOn = True Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = " & Me.Comp This assumes the bound column of the combobox is numerical. If it's text, put this: Me.FilterOn = True Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = '" & Me.Comp & "'" HTH, Barry "hdfixitup" wrote: I have a form opening with a Select Query displaying ALL records. I placed an Unbound combo box that looks up values in a separate table on the form. What I want to do is... Select a value from the combo box and filter the form for records using the selected value in the combo box. The field name is Competitor in the form's underlying query. The combo box is named Comp. No matter what I do I get zero (0) records retrieved after update of the combo box. Any help would be greatly appreciated. Stu |
#5
|
|||
|
|||
"How to filter form records based on combo box value?
Oops. My mistake. Change it to:
Me.FilterOn = True Me.Filter = "Competitor = " & Me.Comp Barry "Barry Gilbert" wrote: In the AfterUpdate event of the combobox, put this code: Me.FilterOn = True Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = " & Me.Comp This assumes the bound column of the combobox is numerical. If it's text, put this: Me.FilterOn = True Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = '" & Me.Comp & "'" HTH, Barry "hdfixitup" wrote: I have a form opening with a Select Query displaying ALL records. I placed an Unbound combo box that looks up values in a separate table on the form. What I want to do is... Select a value from the combo box and filter the form for records using the selected value in the combo box. The field name is Competitor in the form's underlying query. The combo box is named Comp. No matter what I do I get zero (0) records retrieved after update of the combo box. Any help would be greatly appreciated. Stu |
#6
|
|||
|
|||
"How to filter form records based on combo box value?
Tried again; Another syntax error (missing operator)
"Barry Gilbert" wrote: Oops. My mistake. Change it to: Me.FilterOn = True Me.Filter = "Competitor = " & Me.Comp Barry "Barry Gilbert" wrote: In the AfterUpdate event of the combobox, put this code: Me.FilterOn = True Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = " & Me.Comp This assumes the bound column of the combobox is numerical. If it's text, put this: Me.FilterOn = True Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = '" & Me.Comp & "'" HTH, Barry "hdfixitup" wrote: I have a form opening with a Select Query displaying ALL records. I placed an Unbound combo box that looks up values in a separate table on the form. What I want to do is... Select a value from the combo box and filter the form for records using the selected value in the combo box. The field name is Competitor in the form's underlying query. The combo box is named Comp. No matter what I do I get zero (0) records retrieved after update of the combo box. Any help would be greatly appreciated. Stu |
#7
|
|||
|
|||
"How to filter form records based on combo box value?
Is the Competitor field numeric? If it's text, it should be:
Me.Filter = "Competitor = '" & Me.Comp & "'" You need to add the single quotes. Barry "hdfixitup" wrote: Tried again; Another syntax error (missing operator) "Barry Gilbert" wrote: Oops. My mistake. Change it to: Me.FilterOn = True Me.Filter = "Competitor = " & Me.Comp Barry "Barry Gilbert" wrote: In the AfterUpdate event of the combobox, put this code: Me.FilterOn = True Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = " & Me.Comp This assumes the bound column of the combobox is numerical. If it's text, put this: Me.FilterOn = True Me.Filter = "SELECT * From MyTable WHERE MyTable.Competitor = '" & Me.Comp & "'" HTH, Barry "hdfixitup" wrote: I have a form opening with a Select Query displaying ALL records. I placed an Unbound combo box that looks up values in a separate table on the form. What I want to do is... Select a value from the combo box and filter the form for records using the selected value in the combo box. The field name is Competitor in the form's underlying query. The combo box is named Comp. No matter what I do I get zero (0) records retrieved after update of the combo box. Any help would be greatly appreciated. Stu |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Filtering records on a form using multiple combo boxes | Kevin Kraemer | Using Forms | 15 | February 8th, 2010 10:44 PM |
Filter sub form with multiple combo boxes on main form | strive4peace | Using Forms | 2 | March 22nd, 2006 07:27 PM |
Need to stop form from creating new records in table | Scott | General Discussion | 0 | December 15th, 2005 05:56 PM |
Open another form based on the same records as in the first form | Anders Berlin | New Users | 1 | October 1st, 2004 12:40 AM |
dlookup | miaplacidus | Using Forms | 9 | August 5th, 2004 09:16 PM |