I hope when you say "I am creating another table for users to enter in
data", you also plan to create a form based on that table. You should never
allow users to enter data directly into tables; that's what forms are for.
Also, drop-down lists are combo boxes, not list boxes. Most of what is
described below will work with list boxes as well as combo boxes, but a list
box doesn't give you a "drop down".
Basically, what you want to do is create a form based on your table, add two
combo box controls to the form, and set the ControlSource and RowSource
properties of the combo boxes. The ControlSource property of a combo box
indicates which field in the underlying table the control is bound to; this
is the field in the table that will be updated with the value in the
control. The RowSource property of a combo box indicates the source of the
items you see in the drop-down list. The RowSource property can be a table,
but is most often a SELECT query based on a table, something like:
SELECT [part_number] FROM [my_parts_table] ORDER BY [part_number]
(you'll see something like this in the RowSource property of the control).
This specifies the list to display in the combo box as well as the sort
order. The combo box wizard can take you through the steps to set up the
RowSource property, which you can then view in the Properties window for the
control if you want to see what Access did. For the parts combo box, you
will need a RowSource that returns a list of each unique part number. If
you have some kind of master parts table that contains each valid part,
that's the table you'd want to use as the RowSource for the parts combo box.
The ControlSource of the parts combo box, on the other hand, should be set
to the "part no." field in the table you're creating. Likewise, the
ControlSource of the reasons combo box should be set to the "reason code"
field in the same table.
Since you only want to view reasons that are valid for the current part
selection, you don't want the reasons combo box to have a RowSource
initially; you're going to set it when a part selection is made. So far you
won't have needed to enter any VBA code, but now you will have to. Click on
the "part number" combo box and in its Properties window select the Events
tab. Find the event named After Update, and in the drop-down next to it
select [Event Procedure]. Then click on the small button next to it with the
three dots; this will open the code window. You will see that the
AfterUpdate event has already been started for you. In between the two lines
Private Sub and End Sub add the following lines:
If Not IsNull(Me.cboPartNumbers) Then
Me.cboReasons.RowSource = "SELECT [reason code] FROM [reasons] WHERE
[part no.] = '" & Me.cboPartNumbers & "'"
Else
Me.cboReasons.RowSource = ""
End If
Here, cboReasons is the name of the reasons combo box. The Else section
clears the RowSource of the combo box if no part number is selected.
Changing the RowSource property of a combo box automatically re-populates
the control. As I mentioned before, you will need to use your own table,
field, and control names in place of the ones I used in the examples. The
brackets are used by Access to denote tables and fields; they are necessary
because some table and field names contain spaces or may conflict with
certain Access-reserved words (such as Date, which a lot of people like to
use as a field name). If your table and field names don't contain spaces and
you are careful not to use Access-reserved words, you generally don't have
to have the brackets (but Access will put them in for you at times, so don't
get confused). It looks like your field names contain spaces, so you'll need
the brackets.
What we're doing with this code:
= '" & Me.cboPartNumbers & "'"
is concatenating the value of the currently-selected part number into the
SELECT statement, so we only see the reasons associated with that part
number. The resulting SELECT will look something like this:
SELECT [reason code] FROM [reasons] WHERE [part no.] = 'XYZ'
If the part number is a text data type, you need to put quotes around the
value in the SELECT statement; if it's a number data type, you don't need
the quotes and the statement would become
Me.cboReasons.RowSource = "SELECT [reason code] FROM [reasons] WHERE
[part no.] = " & Me.cboPartNumbers
I hope this at least points you in the right direction. As you can see,
there are a lot of details involved. It's not a difficult process, but it
does take some understanding of Access - both forms and VBA coding. If
you've never coded Access before, I would suggest that you pick up a book on
Access programming; what you want to do is fairly standard and is covered in
most books (it's usually called "cascading" combo boxes). These newsgroups
are good for getting specific questions answered, but complete tutorials on
concepts such as setting up forms and cascading combo boxes are a bit much.
We here don't know all the details about your database, such as table
structures, table and field names, etc., so it's nearly impossible to give
complete, workable code. You can also search through the back-posts in these
newsgroups for terms such as "cascading" and find more examples, some
probably better than mine. As you proceed, feel free to post back with
specific questions. Good luck!
Carl Rapson
NeedHelp said:
Can you please clarify what you wrote below. I have never used code before
and am confused. Do I type in everything that you wrote, substituting in
my
own names? and do I only substitute my own names in the brackets or other
places. Also, the last part (& lstParts & "'") is confusing. What is this
suppose to be?
I know I am being confusing so here is what I have, maybe you can answer
me
better. I have a table "reasons" with two fields "reason code" and "part
no."
I am creating another table for users to enter in data. the "part no"
field
will be a drop down list with all the part numbers. Next to it is a
"reason
code" field with a drop down list as well. I want to modify this list to
only
include reasons that are associated with that part number.
Thank you!
Carl Rapson said:
In the AfterUpdate event of the "part number" list box, modify the
RowSource
property of the "reason" list box as appropriate:
lstReasons.RowSource = "SELECT [field] FROM [reasons] WHERE
[part_number]='"
& lstParts & "'"
Of course, use your own table, field, and control names.
Carl Rapson
NeedHelp said:
If I have 2 list boxes, one that contains part numbers and one that
contains
reasons why that part may be defective, how to do make it to where the
list
given in the "reason" list box changes based on the part number
selected
in
the "part number" list box?
