CODEWITHSUNDEEP
ms-accessms-access-2010
Tyler Frankling
Tyler Frankling

Reputation: 11

Selecting data from a table and auto-filling fields

I'm working on a small little project, at the moment just a test for a larger project, but I've hit a block that I can't find a solution too.

I have a list of items in a table, and they contain information such as the item name and the price. I have another table with sales. I have a form that will allow you to select a item name, however, then I would like the rest of the form to be auto-filled with information from the items table.

I can make a message box appear when the item name field has been changed, however, through my searching, I am unable to find a way to search the items table for the value of the field, then read the price value, then insert the price value into a field in this form.

First table "items": itemname: text price: currency

Second Table "sales": itemname: text price: currency date: time/date

I hope I have explained myself well enough, if not, just ask and I will try to clarify.

Upvotes: 1

Views: 35853

Answers (3)

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

Most answers here are posting way too much code and all kinds of SQL statements. As such, these answers are creating world poverty.

The most simple approach is to let the wizard build a combo box on your form to select a given item. This combo box will thus based on the items table. The first column of the combo box will be the PK (autonumber ID) of the items table. This combo box will thus be bound to the ItemID column in sales. The wizard will also “hide” the id, but you WANT to include the other columns in this combo box such as price etc. And you likely should have the 2nd column of this combo box the description of the item from the item table. As noted, you ALSO want to include the price column.

Thus, after you select a item, then to have auto matic fill out the price column, use this code:

This code goes in the AFTER update event of the item combo box:

Me.Price = me.ItemComboBox.column(2)

So when you select a item, the price field will to auto filled for you. And note how you only had to write one line of code.

So all of this can be done using built in wizards and one line of code.

Upvotes: 1

James Sinclair
James Sinclair

Reputation: 407

Add an event procedure (code builder) to the dropdown box's onchange event. Right click the dropdown in design view and choose properties. then on the event tab in the properties window click in the on change line and click the '...' button.

In the code for this event you'll need to query the DB (using the dropdown box's index or ID field) to pull the items details into a recordset. The query would look somethign like

"SELECT * FROM Items WHERE ItemID = " & dropdownboxname.value

theres plenty of examples of how to do this on the web

Then you can update the various textboxes in the form with the required fields in the recordset. again theres plenty of examples of this on the web

EDIT:in response to comments below

you'll need to do somethign along the lines of...

Dim rsItems AS DAO.Recordset
Set rsItems = CurrentDB.OpenRecordset("SELECT * FROM Items WHERE ItemID = " & dropdownboxname.value)
If not rsItems.EOF Then
    textbox1.text = rsItems![fieldname1]
    textbox2.text = rsItems![fieldname2]
end if
Set rsItems = nothing

let me know if thats any help ;-)

Upvotes: 0

Tim Lentine
Tim Lentine

Reputation: 7862

Have a look at the Northwind datatabase (northwind.mdb) that ships with Access. They have several examples of how to update a form based upon a combobox changing values.

Also, you might want to look at the table design in the Northwind Database as well. Generally speaking, it is a bad idea to use the product name as the key field in both of your tables. What will happen (and it will eventually) is that the product name is going to change for one of the products and you'll have to update all of the tables that reference that product name.

The better design is to use a key field (I'd recommend an AutonumberField) in your products table and then reference the key field in the sales table. This way, if the product name changes you only have to make the change in one location, not many.

Here's a sample table layout to illustrate my point:

Table Items:

ItemID (Autonumber - Primary Key on the table)
ItemName (Text - Name of product)
Price (Currency)

Table: Sales

ItemID (Integer - Foreign Key to Items.ItemID)
Quantity (Integer - # of units ordered)
Price (Currency)
OrderDate (Date/Time)

Upvotes: 3

Related Questions