Solved multiple combox (1 Viewer)

[theDBguy]

Thank you all for the replies!

I just noticed DBGuy that I can select and view the values but it doesn't filter the list. It's not acting as a filter as I would have expected. Any ideas?

Thanks again

Hmm, filter what? You may have to explain that a bit more. To filter a combobox, you will need to add a WHERE clause to the Row Source.

When I select CustomerName from Combo1, here's what I see in Combo2. What would you prefer to see instead?

 

[ProgramRasta]

Hmm, filter what? You may have to explain that a bit more. To filter a combobox, you will need to add a WHERE clause to the Row Source.

When I select CustomerName from Combo1, here's what I see in Combo2. What would you prefer to see instead?

View attachment 79663

I have a text box on my Form with some details relating to each Name1, Name 2 etc so when I selected Name1 from the second combobox I would like to display their records.
Sorry if I wasn't clear the first time, appreciate you taking the time to assist me.

 

[theDBguy]

I have a text box on my Form with some details relating to each Name1, Name 2 etc so when I selected Name1 from the second combobox I would like to display their records.
Sorry if I wasn't clear the first time, appreciate you taking the time to assist me.

Hi. We can't see what you're looking at, that's why we ask for a demo. You gave us one, and we used it. If you have more questions, you might have to repost a new demo showing the problem. But still, I tried to give you the answer earlier. Try using a WHERE clause in the combobox Row Source to apply a filter to it. Otherwise, maybe post an updated version of your db.

 

[ProgramRasta]

DBGuy, appreciate your help!

 

[theDBguy]

DBGuy, appreciate your help!

Hi. Are you sure you uploaded the correct demo? This one looks exactly like the first one you posted earlier.

 

[ProgramRasta]

Hi. Are you sure you uploaded the correct demo? This one looks exactly like the first one you posted earlier.

sorry once again for wasting your time.

 

[theDBguy]

sorry once again for wasting your time.

Hi. Take a look. I hope this is what you meant.

 

[ProgramRasta]

Hi. Take a look. I hope this is what you meant.

honestly appreciate the time taken, have a great day.

 

[theDBguy]

honestly appreciate the time taken, have a great day.

You're welcome. Good luck with your project.

 

[ProgramRasta]

You're welcome. Good luck with your project.

Hi

I have one final question that I hope you can spare the time to help with.

In the select statement you provide you selected all the records. Is it possible to select a field that is not in the table?

For example:

In an access query, I have Addnumbers (Num1, Num2) as Total (where Addnumbers is a function). If I wanted to bring Total into the combobox how would I go about it?

As always, thanks for your time.

 

[theDBguy]

Hi

I have one final question that I hope you can spare the time to help with.

In the select statement you provide you selected all the records. Is it possible to select a field that is not in the table?

For example:

In an access query, I have Addnumbers (Num1, Num2) as Total (where Addnumbers is a function). If I wanted to bring Total into the combobox how would I go about it?

As always, thanks for your time.

Hi. I think the "best" way to approach that is to use a query rather than a table for your dropdown.

 

[ProgramRasta]

Hi. I think the "best" way to approach that is to use a query rather than a table for your dropdown.

Hi

I have attached the database Customers 3 - this will be my final question on it!

1. I need to include the 'Total' from the 'Qry_Customers' as a filter in CboFilter2.
2. I'm also getting an error when filtering for null values
3. I get problems with the recordset updating the label on occasion i.e. sometimes it doesn't show the filtered amount of records, I also get error 3071, this happens when I filter on long strings

Appreciate your assistance, I've learned a lot since coming to the forums.

Cat

 

[moke123]

I think you need to change, among other things, your filter and rowsources

change the from clause to

Private Sub cboFilter1_AfterUpdate()
'thedbguy@gmail.com
'3/8/2020
Me.cboFilter2.RowSource = "SELECT Distinct  " & Nz(Me.cboFilter1, "*") & " FROM Qry_Customers"

End Sub

change the filter to

Private Sub CboFilter2_AfterUpdate()

Dim strFilter As String
'thedbguy@gmail.com
'3/8/2020

strFilter = Nz(Me.cboFilter1, "") & "='" & Nz(Me.cboFilter2, "") & "'"
 
Me.Filter = strFilter
Me.FilterOn = True
End Sub

 

[ProgramRasta]

I think you need to change, among other things, your filter and rowsources

change the from clause to

Private Sub cboFilter1_AfterUpdate()
'thedbguy@gmail.com
'3/8/2020
Me.cboFilter2.RowSource = "SELECT Distinct  " & Nz(Me.cboFilter1, "*") & " FROM Qry_Customers"

End Sub

change the filter to

Private Sub CboFilter2_AfterUpdate()

Dim strFilter As String
'thedbguy@gmail.com
'3/8/2020

strFilter = Nz(Me.cboFilter1, "") & "='" & Nz(Me.cboFilter2, "") & "'"

Me.Filter = strFilter
Me.FilterOn = True
End Sub

Thanks for taking the time to reply.

That didn't quite work.

Are you suggesting I change the Row Source type in Filter 1?

I've changed it to a query and also left it as the value list and neither work as intended.

Sorry for my lack of knowledge but you may have to spell it out for me.

Cat

 

[moke123]

The after update of combo1 sets the rowsource of combo2. You have it as select all from the table where it should be select distinct from the query -
Qry_Customers. This will include the totals column.
You dont have to change the rowsource of combo1.

The string you had for the filter was wrong. If you notice I have assigned it to a variable.
Add a debug.print as below to see what it resolves to.

strFilter = Nz(Me.cboFilter1, "") & "='" & Nz(Me.cboFilter2, "") & "'"

Debug.print strFilter

note that the filter will fail if ,for instance, the customer name contains an apostrophe.

 

[ProgramRasta]

The after update of combo1 sets the rowsource of combo2. You have it as select all from the table where it should be select distinct from the query -
Qry_Customers. This will include the totals column.
You dont have to change the rowsource of combo1.

The string you had for the filter was wrong. If you notice I have assigned it to a variable.
Add a debug.print as below to see what it resolves to.

strFilter = Nz(Me.cboFilter1, "") & "='" & Nz(Me.cboFilter2, "") & "'"

Debug.print strFilter

note that the filter will fail if ,for instance, the customer name contains an apostrophe.

Thank you for the reply!

It's still not working, I'm getting a Syntax error.

May I ask, can you please send back the database I sent today with the proposed updates so that I can have a look at what it is directly. I must be making some error along the way...

Thank you

Cat

 

[theDBguy]

Hi

I have attached the database Customers 3 - this will be my final question on it!

1. I need to include the 'Total' from the 'Qry_Customers' as a filter in CboFilter2.
2. I'm also getting an error when filtering for null values
3. I get problems with the recordset updating the label on occasion i.e. sometimes it doesn't show the filtered amount of records, I also get error 3071, this happens when I filter on long strings

Appreciate your assistance, I've learned a lot since coming to the forums.

Cat

Hi. Sorry for the delay. Based on @moke123 suggestions, please check out the attached.

 

[ProgramRasta]

Guys, I would like to personally thank both of you for your help.

This is a great website for learning and you guys are awesome for helping.

Cat

 

[theDBguy]

Guys, I would like to personally thank both of you for your help.

This is a great website for learning and you guys are awesome for helping.

Cat

Hi Cat. You're very welcome. @moke123 and I were happy to assist. Good luck with your project.

 

[moke123]