Form updates unique id in table 1

[Crystalguru]

Hello,

I have a Customer form contains Name, Address, Phone. On this same form, I have Sales Info - product name, qty, cost.

The product name is a drop down list from the Product table.

What I would like to happen is when the user selects the product name from the drop down list, the Sales Info table is populated with the Product_ID (key) that is associated with the product.

Is this doable?

Thanks

 

[Remou]

Hi
If your combo Row Source is:
[tt]Select ProductId, Description From tblProducts[/tt]
You can have a textbox with a Default Value:
=ComboName.Column(0)
Alernatively, you can set the value using the After Update Event of the combobox.

 

[Crystalguru]

I would like to set the value using the After Update Event of the comboBox (ProductName). I am not sure how to go about writing this.

Here's the steps I do know:
Properties of Product Name
Event Tab
After Update

What should I use from here? Expression? If yes, what do I need to put in the expression?

thanks

 

[Remou]

I forgot how much the wizard will do. If you create a combo and follow the steps through:
1. 'I want the combo to look up values in a table or query'
2. Select the product table
3. Select the relevant fields, ticking 'Hide key column'
4. Choose 'Store that Value in this Field', and select the appropriate field from the sales table.
This will create a combobox with a Control Source set to the field chosen in step 4. If you have already created the combo (as I think you have), you can simply set the Control source to the appropriate field. However, this method means that whenever the ProductName combo is updated the field named as a Control Source will be updated, as the combo is now bound to that field.

Using After Update:

Private Sub ProductName_AfterUpdate()
Me.Sales_Product_ID = Me.ProductName
End Sub

This will also mean that Sales_Product_ID will be updated each time the combo is updated, but you can now put in a message to control this, if you wish.

Once again, both these suggestions depend on the Row Source of your combo having appropriate fields.

 

[Crystalguru]

Since I am in the testing stages of my database and forms. I took the first option you suggeested, doing the wizard. This would work. But when I went through the wizard, I didn't get step #4
Choose 'Store that Value in this Field', and select the appropriate field from the sales table.

I cannot find that option in the wizard. Am I missing something here?

 

[Crystalguru]

Interesting. Once I unchecked the hide key column, it allowed me to designate which field I would want to store in this table. Great. I am going to test it through the form.
Thanks.

 

[Remou]

This is a problem, the key field must be hidden for the combo to work properly. Is it possible that your form is unbound? If so, the first option will not work.

 

[Crystalguru]

Here's how it is going.

In the tblSales, I created a field called Product Name - data type is Look Up to the Product Table on Product ID. During the wizard, I did not hide the key field.

When I recreted my form, I have both the Product Name and the Product Id. Interesting.

This seems so simple and I don't know why I can't get it figured out. On the form, I should see just the names of the Products, but in the tblSales, have it store the Product ID? Hmm.

thanks

 

[Remou]

Ah ... lookups come with a health warning:

Did you use the dreaded anti-feature of Lookup Fields in tables?

http://www.mvps.org/access/lookupfields.htm

 

[Crystalguru]

Ah, interesting. Thanks for sharing!

 

[Remou]

I should have added, Hide Key Field is the bit that makes only the product show. If you check out the Column Widths property on the Format tag for the combo, you will see two numbers separated by a semi colon, if you make the first of these 0, it will hide the key column, which will, hopefully, give you the layout you want.

 

[Crystalguru]

That is just perfect! Wonderful! It works for me. What a great way to start a weekend!

Thanks for your help and tips.

Carrie