Autolookup Query 2

[ralphus]

I have ooked everywhere for help on this and can ony find how to do it for a form!!
I am trying to set up a table so that when a mode number is seected other fieds will fill in it consists of:

Model_Table

Model_No [primary key]
Till_Roll_Size
Ribbon
Cash_Drawer

Purchase_Table

Product_Id [autonumber primary key]
site_name
serial_number
model_number
till_roll
ribbon_size
drawer
date_of_purchase
length_of_guarantee
Maintenance

I want it so that when someone selects a model in the purchase table it fills in till_roll ribbon_size and drawer with the data from the model_table.

I have set up a query containing:
model_number [purchase_table]
till_roll_size [model_table]
ribbon [model_table]
cash_drawer [model_table]

what do i need to do next? please help im going out of my mind cheers

 

[lupins46]

The reason you can't find a solution is that it is not possible to do this in a table. Tables are simply not meant to be used like this. Tables are just where data is stored; data entry and all user actions should be done using forms.

 

[BNPMike]

You need to have the key and the foreign key in the query (or at least one critical one - I can't remember which). Certainly it will work with both Model_Number fields present. If necessary experiment. It's all explained in Access Help.

 

[ralphus]

erm dunno what to say really 2 different replies saying different things. Er i have achieved the query, i have started on my forms. So either way how would i make it so that once the user has seected the mode number the other fields would change as well, if this makes any sense

 

[lupins46]

One reply (mine) is talking about tables; the other is talking about queries.

This is what you said:
"I am trying to set up a table so that when a mode number is seected other fieds will fill in "

As I indicated, this is impossible.

Using an autolookup query you can LOOK UP the related details from a different table. This does NOT add the values to your Purchase table. It just looks them up and displays the data that is on the Model table.

If you want to add the data to your Purchases table you need to use a form and write code that takes the looked-up values and writes them into textboxes which are bound to the correct fields in the table. Note that if you include fields with the same name in a query you need to rename one of them to avoid confusion and error.

 

[willir]

ralphus

Hmmmm.
Well, lupins46 has given you the specific answer you requested.

But perhaps we can dig further and look at your design. In short, perhaps you can take advantage of a relational design, and your original problem become moot...

Model_Table
Model_No [primary key]
Till_Roll_Size
Ribbon
Cash_Drawer

Purchase_Table
Product_Id [autonumber, primary key]
site_name
serial_number
model_number
till_roll
ribbon_size
drawer
date_of_purchase
length_of_guarantee
Maintenance

I want it so that when someone selects a model in the purchase table it fills in till_roll ribbon_size and drawer with the data from the model_table.

Are the fields on Purchase table in blue...
till_roll
ribbon_size
drawer
...duplicates of the values from the Model table?
Till_Roll_Size
Ribbon
Cash_Drawer

Based on your objective, I suspect they are. You want to populate the values from the Model table into the Purchase table, or you seem to be using the Model table as a template.

In general, when creating a relational database, you want to avoid duplicating data -- more maintenance, more data, and in general, more headaches.

Okay, there are times when "we" want to duplicate the data from one table to another. Here are some examples...
- The values in the Model table change. The Model number remains the same, but the value of Till_Roll_Size, Ribbon, Cash_Drawer change. Something similar to a pricing table.
- A person can "edit" the values after selecting the Model.
- You query the Project table a lot for the aforementioned a lot, and don't want to include the Model table in the query. Be careful here -- the convenience of simplifying a query may result in more headaches later when maintaining duplicate data.

Let say that none of these conditions apply.

Then, you do not need the fields...
till_roll
ribbon_size
drawer
... on the Purchase table.

Instead, you can...
- Use a subform for the Model table. When a user selects the Model_No, the contents of the selected Model are displayed in the subform. Here, you would use a combo box to select the model, and the Purchase_Table.model_no would reference the corresponding entry in Model_Table.Model_No
- Use unbound text control fields on the form. A combo box is used to select the Model_No. The query used for the combo box includes all entries in the Model table...
SELECT Model_No, Till_Roll_Size, Ribbon, Cash_Drawer FROM Model_Table
...and then take advantage of the Column property to display the contents to the unbound control fields. For example, the name of the combo box is cmbModel_No, and the unbound text control fields are txtTill_roll, txtRibbon_size, txtDrawer...

Me.txtTill_roll = Me.cmbModel_No.Column(1)
Me.txtRibbon_size = Me.cmbModel_No.Column(2)
Me.txtDrawer = Me.cmbModel_No.Column(3)

Richard

 

[ralphus]

Cheers willir
You have been a great hep but what do you mean by Me.
and do i put the code in the control source for each txt box
Again thanks allot

 

[willir]

Ralph

"Me" in VBA code references the current form.

The code would most like work best with the OnCurrent event procedure and perhaps the AfterUpdate event procedure for the Product_ID. The current event procedure will run the code very time you load / open the form, or move the next or previous record, or find a record.

The after update event procedure will run the code after you change the value of the Product_ID.

How to enter the code.

Open the form in design mode. Make sure the "Properties" window is open (from the menu, "View" -> "Properties")

Select the form by clicking on the top left box where the horizontal and vertical rulers meet.

Select the "Events" tab on the Properties window, then select the appropriate event. Select "[Event procedure]" from the drop down list, and then select the "..." command to the button to the right. This will take you to the VBA coding window.

 

[ralphus]

Again thanks willir
It seems to be working well, i inserted the code for on change for the combo box Model_no so that whenever a mode is seected it will update do u think this coud present any issues later on. Again thanks been trying to do this for a whie now and you have helped greaty
Cheers Ralph