Filter a form by combo box selection "Andy Roberts" <andy[ at ]blue-bean.co.uk> 12/31/2008 4:02:40 PM I have a continuous form based on a query which pulls TenderID and TenderSite from tblTenders and ClientName (linked by ClientID) from tblClients, so I get... 2134 London Client1 2163 Liverpool Client 2 2222 Cardiff Client 1 2345 Derby Client 3 no problem so far. I want a txt box in the form header above the Tender ID so I can just type a tender number and the form will filter the results to only show that tender. I also want a cbo box above the client so I can select a client from the dropdown (which isn't a problem as this works) and the form will filter showing only those tenders for that selected client. Pulling together the txt box and cbo box is fine, but the code required for the after_update events is driving me insane! -- Regards Andy ___________ Andy Roberts Win XP Pro Access 2007 Liverpool, UK

Re: Filter a form by combo box selection "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 12/31/2008 5:31:59 PM Change the form's RecordSource query so that it has a WHERE clause in it like this: WHERE ClientID = Forms!NameOfYourForm!ComboBoxName OR Forms!NameOfYourForm!ComboBoxName Is Null Then use this code for the combo box's AfterUpdate query: Private Sub ComboBoxName_AfterUpdate() Me.Requery End Sub -- Ken Snell <MS ACCESS MVP> http://www.accessmvp.com/KDSnell/ "Andy Roberts" <andy[ at ]blue-bean.co.uk> wrote in message news:j82dnS0n2qgpCsbUnZ2dnUVZ8vidnZ2d[ at ]bt.com... [Quoted Text] >I have a continuous form based on a query which pulls TenderID and >TenderSite from tblTenders and ClientName (linked by ClientID) from >tblClients, so I get... > > 2134 London Client1 > 2163 Liverpool Client 2 > 2222 Cardiff Client 1 > 2345 Derby Client 3 > > no problem so far. > > I want a txt box in the form header above the Tender ID so I can just type > a tender number and the form will filter the results to only show that > tender. > > I also want a cbo box above the client so I can select a client from the > dropdown (which isn't a problem as this works) and the form will filter > showing only those tenders for that selected client. > > Pulling together the txt box and cbo box is fine, but the code required > for the after_update events is driving me insane! > > -- > Regards > > Andy > ___________ > Andy Roberts > Win XP Pro > Access 2007 > Liverpool, UK >

Re: Filter a form by combo box selection "Andy Roberts" <andy[ at ]blue-bean.co.uk> 12/31/2008 7:48:06 PM Thanks Ken I have this as the Row Source for the cbo.. SELECT [qryFindClient].[ClientID], [qryFindClient].[ClientName] FROM [qryFindClient] ORDER BY [ClientName]; The Record Source for the form is currently set to qryT3TenderList I've tried to add the following, but get an error... WHERE ClientID = Forms!frmT3TenderList!cboFilterClient OR Forms!frmT3TenderList!cboFilterClient Is Null What should the record source actually say? -- Regards Andy ___________ Andy Roberts Win XP Pro Access 2007 Liverpool, UK "Ken Snell (MVP)" <kthsneisllis9[ at ]ncoomcastt.renaetl> wrote in message news:%23bkSo22aJHA.2124[ at ]TK2MSFTNGP04.phx.gbl... [Quoted Text] > Change the form's RecordSource query so that it has a WHERE clause in it > like this: > > WHERE ClientID = Forms!NameOfYourForm!ComboBoxName > OR Forms!NameOfYourForm!ComboBoxName Is Null > > Then use this code for the combo box's AfterUpdate query: > > Private Sub ComboBoxName_AfterUpdate() > Me.Requery > End Sub > > -- > > Ken Snell > <MS ACCESS MVP> > http://www.accessmvp.com/KDSnell/ > > > "Andy Roberts" <andy[ at ]blue-bean.co.uk> wrote in message > news:j82dnS0n2qgpCsbUnZ2dnUVZ8vidnZ2d[ at ]bt.com... >>I have a continuous form based on a query which pulls TenderID and >>TenderSite from tblTenders and ClientName (linked by ClientID) from >>tblClients, so I get... >> >> 2134 London Client1 >> 2163 Liverpool Client 2 >> 2222 Cardiff Client 1 >> 2345 Derby Client 3 >> >> no problem so far. >> >> I want a txt box in the form header above the Tender ID so I can just >> type a tender number and the form will filter the results to only show >> that tender. >> >> I also want a cbo box above the client so I can select a client from the >> dropdown (which isn't a problem as this works) and the form will filter >> showing only those tenders for that selected client. >> >> Pulling together the txt box and cbo box is fine, but the code required >> for the after_update events is driving me insane! >> >> -- >> Regards >> >> Andy >> ___________ >> Andy Roberts >> Win XP Pro >> Access 2007 >> Liverpool, UK >> > >

Re: Filter a form by combo box selection "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 1/1/2009 2:09:07 AM The WHERE clause looks correct in syntax.... what error are you getting? Can you post the entire SQL statement for the qryT3TenderList query? -- Ken Snell <MS ACCESS MVP> http://www.accessmvp.com/KDSnell/ "Andy Roberts" <andy[ at ]blue-bean.co.uk> wrote in message news:RPydnYHaXMgXUcbUnZ2dnUVZ8jOdnZ2d[ at ]bt.com... [Quoted Text] > Thanks Ken > > I have this as the Row Source for the cbo.. > SELECT [qryFindClient].[ClientID], [qryFindClient].[ClientName] FROM > [qryFindClient] ORDER BY [ClientName]; > > The Record Source for the form is currently set to qryT3TenderList > > I've tried to add the following, but get an error... > WHERE ClientID = Forms!frmT3TenderList!cboFilterClient OR > Forms!frmT3TenderList!cboFilterClient Is Null > > What should the record source actually say? > > -- > Regards > > Andy > ___________ > Andy Roberts > Win XP Pro > Access 2007 > Liverpool, UK > "Ken Snell (MVP)" <kthsneisllis9[ at ]ncoomcastt.renaetl> wrote in message > news:%23bkSo22aJHA.2124[ at ]TK2MSFTNGP04.phx.gbl... >> Change the form's RecordSource query so that it has a WHERE clause in it >> like this: >> >> WHERE ClientID = Forms!NameOfYourForm!ComboBoxName >> OR Forms!NameOfYourForm!ComboBoxName Is Null >> >> Then use this code for the combo box's AfterUpdate query: >> >> Private Sub ComboBoxName_AfterUpdate() >> Me.Requery >> End Sub >> >> -- >> >> Ken Snell >> <MS ACCESS MVP> >> http://www.accessmvp.com/KDSnell/ >> >> >> "Andy Roberts" <andy[ at ]blue-bean.co.uk> wrote in message >> news:j82dnS0n2qgpCsbUnZ2dnUVZ8vidnZ2d[ at ]bt.com... >>>I have a continuous form based on a query which pulls TenderID and >>>TenderSite from tblTenders and ClientName (linked by ClientID) from >>>tblClients, so I get... >>> >>> 2134 London Client1 >>> 2163 Liverpool Client 2 >>> 2222 Cardiff Client 1 >>> 2345 Derby Client 3 >>> >>> no problem so far. >>> >>> I want a txt box in the form header above the Tender ID so I can just >>> type a tender number and the form will filter the results to only show >>> that tender. >>> >>> I also want a cbo box above the client so I can select a client from the >>> dropdown (which isn't a problem as this works) and the form will filter >>> showing only those tenders for that selected client. >>> >>> Pulling together the txt box and cbo box is fine, but the code required >>> for the after_update events is driving me insane! >>> >>> -- >>> Regards >>> >>> Andy >>> ___________ >>> Andy Roberts >>> Win XP Pro >>> Access 2007 >>> Liverpool, UK >>> >> >> > >