Dependent ListBox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In tryin to Base the value of a control on another control change, When a
user select the client code in "Code" listbox, the "Client" listbox must show
the name of that client, and viceversa... The Access help dont shows an
example of this.

I try an OnChange event of Listbox "client", it it send a error:

Me!
Code: 
 = Select CardCode from Card_DATA Where CardName = Me![Client]
 
Taking a look at the query you are trying to use, it appears that both items
are in the same table. This means that they should have a one-to-one
relationship. That being the case, you don't need two listboxes and,
therefore, don't need to synchronize two listboxes. Set the Row Source of
just one listbox to

Select CardCode, CardName From Card_Data Order By CardCode;

Set the listbox's ColumnCount to 2 and set the column widths as needed to
fit the data. The BoundColumn would probably be one, which means that the
CardCode would be the value of the listbox when you make a selection (not
multiselect, if the listbox is multiselect, you have to step through all of
the selected items and get each individual value.) If you need to refer to
the CardName instead of the CardCode, you would use the listbox's Column
property.

Example:
strCardName = Me.Listbox.Column(1)

The Column property is zero based, so 0 is the first column, 1 the second,
etc.


If desired, you could create a button that would change the sort order and
requery the listbox in the button's Click event. The only difference in the
Row Source would be which field name appears after the Order By clause.

Example:
If Right(Me.Listbox.RowSource, 5) = "Code;" Then
Me.Listbox.RowSource = "Select CardCode, CardName From Card_Data Order
By CardName;"
Else
Me.Listbox.RowSource = "Select CardCode, CardName From Card_Data Order
By CardCode;"
End If

When you change the RowSource the listbox should automatically Requery. If
it doesn't, add the line

Me.Listbox.Requery

after each of the RowSource lines above.
 
Yes, you're right, but... im using this form as a search front end that shows
a report, the user should search by code or name, but as I have it now, the
user can introduce a code from one client, and the name of another... and
this produce an error.

I need that the code and name be of the same client.



Wayne Morgan said:
Taking a look at the query you are trying to use, it appears that both items
are in the same table. This means that they should have a one-to-one
relationship. That being the case, you don't need two listboxes and,
therefore, don't need to synchronize two listboxes. Set the Row Source of
just one listbox to

Select CardCode, CardName From Card_Data Order By CardCode;

Set the listbox's ColumnCount to 2 and set the column widths as needed to
fit the data. The BoundColumn would probably be one, which means that the
CardCode would be the value of the listbox when you make a selection (not
multiselect, if the listbox is multiselect, you have to step through all of
the selected items and get each individual value.) If you need to refer to
the CardName instead of the CardCode, you would use the listbox's Column
property.

Example:
strCardName = Me.Listbox.Column(1)

The Column property is zero based, so 0 is the first column, 1 the second,
etc.


If desired, you could create a button that would change the sort order and
requery the listbox in the button's Click event. The only difference in the
Row Source would be which field name appears after the Order By clause.

Example:
If Right(Me.Listbox.RowSource, 5) = "Code;" Then
Me.Listbox.RowSource = "Select CardCode, CardName From Card_Data Order
By CardName;"
Else
Me.Listbox.RowSource = "Select CardCode, CardName From Card_Data Order
By CardCode;"
End If

When you change the RowSource the listbox should automatically Requery. If
it doesn't, add the line

Me.Listbox.Requery

after each of the RowSource lines above.

--
Wayne Morgan
MS Access MVP


Rookie said:
In tryin to Base the value of a control on another control change, When a
user select the client code in "Code" listbox, the "Client" listbox must
show
the name of that client, and viceversa... The Access help dont shows an
example of this.

I try an OnChange event of Listbox "client", it it send a error:

Me!
Code: 
 = Select CardCode from Card_DATA Where CardName = Me![Client][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
That was the reason for the sort order button, to allow the user to search
either way by sorting either column. Another option would be to have the two
columns but hide one of them. Adjust the listbox to show the value they want
by adjusting the column widths and the sort order to only display the column
they want to see and sort on that column. This would only required adding a
Me.Listbox.ColumnWidths line to each of the If statement parts in the
previous post.

With what you are indicating, the second listbox will only have one item in
it after a selection has been made in the first list box or vice-versa. If
you wanted to just display that single item, you could use a calculated
textbox set next to the listbox to display the value from the hidden column
once a selection has been made. To do this, change the control source of the
textbox to display the appropriate column in each part of the previously
mentioned If statement.

Example:
=Listbox.Column(1)
or
=Listbox.Column(0)

Instead of a command button to toggle between the two views, an option group
with two radio buttons on it would also work well. The user would choose one
or the other, depending on which value they wanted to search on.

For your report, once the user picks the ID or Name they are looking for,
you would use either to filter the form. Since they are a one-to-one
relationship, either value could be used for the reports filter and the
report will show the same results.

This will work as long as there aren't two cards that have the same name. If
there are, then the two listboxes option won't work either. Instead you may
need a third column or some other identifying field to distinguish between
them. The ID column will work instead of a third column, if the user has
access to information that links the two together so that they know which ID
is the one they want associated with the duplicate name. In this case, you
couldn't hide one of the columns, they would both have to show. However,
this isn't a problem is duplicate card names isn't a possibility.
 
Back
Top