Prev: access 2007 read-only
Next: Help with row movement/filtering, and function calls
From: Padraigini on 3 Jul 2008 07:02
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.
From: Stefan Hoffmann on 3 Jul 2008 07:31
hi,

Padraigini wrote:
> 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 <--
From: Padraigini on 3 Jul 2008 07:50
Sorry if I seem a biot dense but what does aSmallTable stand for???

"Stefan Hoffmann" wrote:

> hi,
>
> Padraigini wrote:
> > 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 <--
>
From: Stefan Hoffmann on 3 Jul 2008 08:01
hi,

Padraigini wrote:
> 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 <--
From: Padraigini on 3 Jul 2008 09:30
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" wrote:

> hi,
>
> Padraigini wrote:
> > 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 <--
>
 |  Next  |  Last
Pages: 1 2
Prev: access 2007 read-only
Next: Help with row movement/filtering, and function calls