Nov 14 2010, 05:28 PM
I have looked at the already made posts about how to do this and it seems simple enough but for some reason I just can not get it to work for me. I think i am misunderstanding something that maybe can be clarified by someone. I am making a form for an inventory. I have a table called Inventory which contains InvNum and ItemName. On my form, i would like to create a listbox containing all of the InvNum and when i select one, it will populate the text box to show the ItemName associated with that InvNum. I have tried to make afterUpdate macros, but got lost kind of quick. I also tried to use a SELECT statement in the control source looking something like SELECT ItemName FROM Inventory WHERE InvNum = [List6].Value; (List6 being the Listbox containing all of the InvNum). This is my first time using access so i am sure i am messing up something simple. Any advice would be greatly appreciated. Thank you.
Nov 14 2010, 05:34 PM
Have the RowSource of your list box contain not only the InvNum, but also the ItemName (even if you don't actually display the ItemName in the list box).
Use something like the following as the Event Procedure for the list box's AfterUpdate event:
Private Sub MyListBox_AfterUpdate()
Me!MyTextbox = Me!MyListbox.Column(1)
(Note that the Column collection starts numbering at 0, so Column(1) refers to the value in the second column of the selected row)
Nov 14 2010, 06:03 PM
Ok, i sort of solved my problem, i just expanded my list box to show the ItemName as well as ItemNum, but I ran into another problem. In my inventory table, i also have price. I am trying to make a form that would simulate a customer transaction, where they would come with a couple items, which on it had the item number. I would select the InvNum and it would bring up the ItemName and price, and then i could enter in another ItemNum(because its unlikely all my customers only buy 1 type of product). I am thinking having a listbox for the InvNum would make that difficult, but the example i saw had a listbox for the InvNum so i was trying to use that.
Nov 14 2010, 06:07 PM
Include the Price in the list box's RowSource as well, and refer to it as Me!MyListBox.Column(2)
Nov 14 2010, 06:20 PM
Yea i know how to include price, but what i dont know how to do is create multiple lines for the InvNum, ItemName, and Price so that i can include multiple items.
Nov 14 2010, 07:08 PM
Typically, the model for invoices is a many-to-many: you have a table with one row for each invoice, and a second table with one row for each product. Since each invoice can contain one or more products, and since each product can appears on one or more invoices, that's the many-to-many relationship, which is resolved by a third table.
You use a form and subform: the parent form is usually bound to the Invoices table, while the form being used as the subform is bound to the resolution table. On the subform, a list box (or, more usually, a combo box) with the Products table as its RowSource allows you to select which product(s) appear on the invoice.
See whether the Northwind sample application that comes with Access helps: there, you've got tables Orders and Products, and resolution table Order Details, with the Order Details form tieing everything together.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here