Filter records on continuous form using combo box

[bikerted]

I've searched through FAQ's to no avail for what I specifically require, which is:

On my continuous form I have receipts information and a control, rendered invisible and called "Payee ID" (I know spaces are a "no no"!). My combo box sited in the header is looking up Payee ID but also displaying other fields, including the Payee name, from a related Payee details table - principally so I can see I've selected the correct Payee where the same name appears more than once. I've also set the combo box's underlying query not to display duplicate values. What I would like to do is select a name in the comb box and for the record or records applicable to that name to be diplayed only - rather as sub-form does. Is this possible with a relatively simple bit of code?

Many thanks

Ted.

 

[Remou]

Hi
Do you mean something like (?):

Private Sub cboCombo_AfterUpdate()
Me.RecordSource = "SELECT * FROM [Customers] " _
    & " WHERE Firstname = " & Chr(34) & Me.cboCombo.Column(1) & Chr(34) _
    & " AND Lastname = " & Chr(34) & Me.cboCombo.Column(2) & Chr(34)
End Sub

 

[TheAceMan1]

How are ya bikerted . . . . .

[blue]Is this possible with a relatively simple bit of code?[/blue]

Well . . . can be be done, [blue]but not so simple[/blue] as it may appear. What you need is a [blue]RecordSource[/blue] for the form based on a query, with criteria resolved from the combobox on the form. The only real problem is [blue]openining of the form[/blue] where the combobox is initially null/empty! This is handled by criteria of the following format:

[blue]WHERE [Payee ID] = Forms!FormName![Payee ID] OR Forms!FormName![Payee ID] Is Null[/blue]

The form opens with all records available because of the OR clause, but after that, combobox selections filter accordingly . . . A simply Requery does it all!

[purple]Your thoughts?[/purple]

See Ya! . . . . . .

 

[bikerted]

Thank you both,

Aceman1 I thought about this overnight and remembered basing forms on queries before and also successfully using parameter criteria referring to a combo like this: [Form]![ReceiptsEntry]![Combo73] whilst in the onclick event of the combo using this:

Private Sub Combo73_Click()
On Error GoTo Err_ReceiptsQuery_Click

Dim stDocName As String

stDocName = "ReceiptsQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_ReceiptsQuery_Click:
Exit Sub

Err_ReceiptsQuery_Click:
MsgBox Err.Description
Resume Exit_ReceiptsQuery_Click
End Sub.

As you say it doesn't handle null in the combo and leaves the form blank - in fact when I click a value in the combo it displays the query and not the fitered form records at all! This gets further complicated by the fact that I thought (perhaps too optimistically as usual) that I could place similar "combo referrals" in each criteria cell relating to combo boxes for each field on my form. This would neatly enable the user to click one or more combos to refine the filtering of records. The the form would display the selected records as required and generate reports based on these for good measure. Is this too great a complication? Bear in mind that I tried your code and obtained a syntax error - relating to brackets I think? So I'm not getting very far after all!

Does your code go in as is, or is it after the referral to the combo box? Sorry with my brain you have to spell it out even more please!

Ted.

 

[PHV]

Seems you want something like Query By Form (QBF).
Have a look here:

http://download.microsoft.com/download/access2000/Sample/1.0/WIN98Me/EN-US/Qbf2k.exe

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bikerted]

Thanks PHV,

I downloaded the db you kindly posted, and tried the form and also to read the vb with its explanatory text. The latter was a bit too complicated for me - maybe in several months time I'll be better able to cope with it! The form didn't exactly perform how I need mine to, though some of that may be to do with the fact that the Access I'm using refers to DAO 2.1 library?

Notwithstanding all that, I don't think this form is the appropriate thing. What I would like to do is use combos as described above, but of course they won't filter the form in the way that I want, because I have them set to open the query! - Not very bright today, I'm afraid!

Any thoughts you have on this would be appreciated.

Thanks,

Ted.

 

[TheAceMan1]

bikerted . . . . .

[ol][li]Open the query in [blue]design view[/blue].[/li]
[li] On the criteria line for [blue]Payee ID[/blue], copy/paste the following ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue][Forms]![[purple][b]FormName[/b][/purple]]![Payee ID] Or [Forms]![[purple][b]FormName[/b][/purple]]![Payee ID] Is Null[/blue]

[/li]
[li]Save & close the query.[/li]
[li]Open the form is [blue]design view[/blue].[/li]
[li]Set the forms [blue]RecordSource[/blue] to the query name.[/li]
[li]In the [blue]AfterUpdate[/blue] event of the combobox, copy/paste the following:

[blue]Me.Requery[/blue]

[/li]
[li]Disable or remove the code in the OnClick event.[/li][/ol]
[purple]Peform your testing and let us know . . . .[/purple]

See Ya! . . . . . .

 

[bikerted]

Aceman1,

It works for Payee ID now. Actually - after pasting the code and closing the query - I had to paste in the combo box name where the form name was, and then the combo did the filtering correctly. I noticed, though that 1). If you click the query directly a parameter dialog comes up for the combo box and the field. Since the combo works, I guess that's ok? 2. After pasting your code,substituting my form names,closing and reopening the query in design view, I noticed that the code has been moved around a bit:

In Payee ID it has: [Forms]![SearchesEdits]![Combo73]

In a column entitled [Forms]![SearchesEdits]![Payee ID] it has: IsNull on the OR line.

This is probably fine, but to further complicate matters, I've added another combo to filter another field on the same form, but it doesn't affect the form at all - whether the Payee ID combo has data selected or not. This doesn't bode well, because I'd like to have 14 combos selecting criteria on each field - either alone or in combination with each other. Is this a tall order?

I do hope I haven't confused you too much.
Thanks,

Ted.

 

[bikerted]

Aceman1 if you're out there - or any other kind soul for that matter - I'm really stumped!

To reiterate, I would like my continuous form - now based on a query with 14 fields displayed - to have 14 combo boxes in its header from which I can filter for selected criteria on one or (if need be) all fields. At the moment I have two combos from which I have been able to separately select for criteria - but both will not function together. The way I have it set up - using OR - is probably why, but if I link each combo to the query on the same line no filtering occurs. I don't have enough lines to play with for 14 combos.

Am I barking - up the wrong tree I mean?!

Ted.

 

[TheAceMan1]

bikerted . . . . .

Sorry to get back so late!

[blue]If you click the query directly a [purple]parameter dialog comes up[/purple] for the combo box[[/blue]

This is because the query has criteria [blue]referencing a form that is not open![/blue]

[blue] . . . I noticed that [purple]the code has been moved around[/purple] a bit: . . .[/blue]

This is due to the [blue]Microsoft Jet optimizing the query[/blue] and is normal.

[blue]I've added another combo to filter another field on the same form, but it doesn't affect the form at all.[/blue]

[ol][li]Are you sure your using the right column of the query?[/li]
[li]By chance is it a date field?[/li][/ol]

See Ya! . . . . . .