asked on

How can I use a combo selection box multiple times in a form?

I have one Combo Search box that that uses the code below (Code#1) to search 2 Subforms on a form. It works for ONE selection only.
I have to use a button to close and reopen the form in order to make another selection with the combo box. Is there something that I can add
in Code#! to allow me to make additional selections with the combo box without having to close and reopen the form with a button?

Code#1
Private Sub CmbSearch_AfterUpdate()
If Len(Form_DetailMonth.Filter) > 0 Then
Form_DetailMonth.Filter = Form_DetailMonth.Filter & " AND " & "Expr21 = '" & Me.CmbSearch & "'"
Else
Form_DetailMonth.Filter = "Expr21 = '" & Me.CmbSearch & "'"
End If
Form_DetailMonth.FilterOn = True
If Len(Form_DetailAllCustomer.Filter) > 0 Then
Form_DetailAllCustomer.Filter = Form_DetailAllCustomer.Filter & " AND " & "Expr21 = '" & Me.CmbSearch & "'"
Else
Form_DetailAllCustomer.Filter = "Expr21 = '" & Me.CmbSearch & "'"
End If
Form_DetailAllCustomer.FilterOn = True
End Sub

I use this button to close and reopen the form:
Code#2
Private Sub Command26_Click()
DoCmd.Close
DoCmd.OpenForm "OpenSalesOrders_Detail"
End Sub

Fabrice Lambert

Hi,

Put your code in the onChange event:

Private Sub CmbSearch_OnChange()
 If Len(Form_DetailMonth.Filter) > 0 Then
 Form_DetailMonth.Filter = Form_DetailMonth.Filter & " AND " & "Expr21 = '" & Me.CmbSearch & "'"
 Else
 Form_DetailMonth.Filter = "Expr21 = '" & Me.CmbSearch & "'"
 End If
 Form_DetailMonth.FilterOn = True
 If Len(Form_DetailAllCustomer.Filter) > 0 Then
 Form_DetailAllCustomer.Filter = Form_DetailAllCustomer.Filter & " AND " & "Expr21 = '" & Me.CmbSearch & "'"
 Else
 Form_DetailAllCustomer.Filter = "Expr21 = '" & Me.CmbSearch & "'"
 End If
 Form_DetailAllCustomer.FilterOn = True
 End Sub

Open in new window

ES-Components

ASKER

I tried your suggestion. It made sense. It still does not work. I attached the database. Look at the Form "OpenSalesOrders_Detail"
and the combo box on there.

Thanks!!!
OpenSales-.accdb

Paul Cook-Giles

ES-Components, I'd like to help with this question, but the db you posted doesn't have any data in it... just the links to the DetailedOpenOrders and OpenSalesDollarSummary tables. :)

Fabrice Lambert

It still does not work

Please, elaborate.

Side notes:
Indent your code, it help with readability.
Use meaningfull names for your variables, controls and columns (or aliases) in your queries, it help understanding.
Use "Option Explicit" at top of all your module, to enforce variables declaration.

Dale Fye

Where is the combo on your form?

Is the recordset you want to filter part of a continuous form, or is it displayed in a subform?

BTW, when you reply to an experts response, it is very helpful if you include their name in the response so we know who you are replying to.

Dale

PatHartman

Fabrice, the Change event of the control runs every time a new character is typed. It is not the correct event to use for this code.

ES,
I would do this completely differently. It looks like you are trying to actually change the properties of a form and THEN open it but to do that, you would need to open the form in design view and then save the changes. This is not something that should ever be done in an application that is delivered for use by others. It prevents the distribution as .accde and prevents the use of the Access Runtime..

You can do it without code, by simply leaving the calling form open. You can hide the calling form rather than closing it if you don't want it to be visible.

Me.Visible = False

Then in the other forms, Do NOT use the filter property. Change the RecordSource query.

Select ... From ... Where SomeField = Forms!yourform!yourcombo

ES-Components

ASKER

Okay,
Paul, I have attached another copy of the file with a copy of the two linked tables so you can see what they are.
The form where the combo is, is called "OpenSalesOrders_Detail". Dale, the Combo box called "Search The Forms"
is located in the Header section of the "OpenSalesOrders_Detail" form

To all respondents, I am just trying to get something to work for our company. I am not an Access guru by any means, and certainly
not a programmer. It just seems to me that the one Combo List Search box should be able to re-select another choice in the drop down
without having to close an reopen the form. Perhaps you can't do what I am attempting to do. Any help from Paul, Fabrice, Dale, or Pat is greatly appreciated.

Thank you all in advance for any help in solving this minor problem!
Rick
OpenSales-.accdb

ASKER CERTIFIED SOLUTION

Paul Cook-Giles

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ES-Components

ASKER

Thank you Paul. Your suggested code works perfect. It never occurred to me to check the filter and that you would have to
requery the subforms. Thank you again for the detailed explanation and your suggested code.
Much appreciated!!
Rick

Paul Cook-Giles

Glad we could help!
P

PatHartman

I am not an Access guru by any means,

That is why I suggested a non-code solution. There is nothing inherently "better" about writing code and it is often less efficient than using queries although in this situation there won't be any perceptible difference in performance. It is a matter of what you can understand easily and recreate on your own.

Just FYI, you will have less difficulty understanding code if you rename controls to have meaningful names BEFORE writing any code that references them and giving calculated columns real names in queries. Expr21 looks like a name generated by Access for some calculated field in a query.