Select all in a combo

[Padraigini]

I have a combobox that is connected to a surname field, a name selected from
this combo box then determines the contents of a list boxes with two other
list boxes in cascade with the 1st. What I wish to be able to do is to have
an option to select all in the combo box so that all options will also be
available in the following list box.
Whatever is selected in the list box(es) then goes on to act as the filter
for a report
I have tried many different ways using a UNION statements and coding If
statements in VBA but nothing seems to get it just right. Any suggestions
would be greatly appreciated.
Thanks in advance.

 

[Stefan Hoffmann]

hi,

I have tried many different ways using a UNION statements and coding If
statements in VBA but nothing seems to get it just right. Any suggestions
would be greatly appreciated.

The RowSource of your ComboBox:

SELECT id, surname FROM (
SELECT -1 AS id, "<all>" AS surname FROM aSmallTable
UNION
SELECT id, surname FROM surnameTable
) Q
ORDER BY Q.id

I assume that you don't have negative ids in your table. The id column
is the bound one.

The basic filter condition:

WHERE surnameTable.id = ComboBox.Value OR ComboBox.Value = -1

Using VBA:

Private Sub ComboBox_Change()

Dim SQL As String

SQL = "SELECT * " & _
"FROM dependenTable " & _
"WHERE (idSurname = " & ComboBox.Value & ") " & _
"OR (-1 =" & ComboBox.Value & ")"
dependenComboBox.RowSource = SQL
dependenComboBox.Value = dependenComboBox.ItemData(0)

End Sub


mfG
--> stefan <--

 

[Padraigini]

Sorry if I seem a biot dense but what does aSmallTable stand for???

 

[Stefan Hoffmann]

hi,

Sorry if I seem a biot dense but what does aSmallTable stand for???

A table with only a few datasets (at least one) and not so many fields.
In some cases it is necessary to create a table for this purpose.


mfG
--> stefan <--

 

[Padraigini]

Thanks Stephan,
So do you think I should create another table??
I should have mentioned that I am new to access programming so I'm not sure
what you mean. Also is the filter condition for the form????
Maybe it would help if I gave you more information.
My table is called TimeEntry and I reference the surnames by
TimeEntry.Surnames
the list box that is dependant on the combobox (cboSurname) is called
lstArea, which has data from TimeEntry.Area.
I really appreciate you taking the time to answer this post.

 

[Stefan Hoffmann]

hi,

So do you think I should create another table??

You need a second table with at least on record for the UNION trick.
This can be any of your tables, but it should be a table with only a few
records.

Also is the filter condition for the form????

Yes, you can use it as condition for your controls.

Maybe it would help if I gave you more information.
My table is called TimeEntry and I reference the surnames by
TimeEntry.Surnames
the list box that is dependant on the combobox (cboSurname) is called
lstArea, which has data from TimeEntry.Area.
I really appreciate you taking the time to answer this post.

So you have on table TimeEntry and you want to narrow down the records
by the values in the field Surnames?

Assign

SELECT Surnames FROM (
SELECT 0 AS Sort, "<all>" AS SurnamesFROM TimeEntry
UNION
SELECT 1, Surnames FROM TimeEntry
) Q
ORDER BY Q.Sort, Q.Surnames

as your RowSource of your ComboBox cboSurnames. Take a look at your
table design, the field id in the row source above must exist in the
table TimeEntry. Otherwise you have take your primary key field(s).

Add in the property editor an event prodedure for the On Change event as
code, the code should finally look like this:

Private Sub cboSurname_Change()

Dim SQL As String

SQL = "SELECT Area " & _
"FROM TimeEntry "

If cboSurnames.Value <> "<all>" Then
SQL = SQL & _
"WHERE Surnames='" & Replace(cboSurname.Value,"'","''") & "'"
End If

lstArea.RowSource = SQL

End Sub



mfG
--> stefan <--

 

[Padraigini]

Thanks you for your patience Stephan it works perfectly!!!!!
You are a legend.
Thanks again.