If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Change lists of one listbox based on selection of other listbox?
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? |
#2
|
|||
|
|||
Change lists of one listbox based on selection of other listbox?
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" wrote in message ... 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? |
#3
|
|||
|
|||
Change lists of one listbox based on selection of other listbo
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" wrote: 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" wrote in message ... 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? |
#4
|
|||
|
|||
Change lists of one listbox based on selection of other listbo
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" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|