Autofilled data not in table (1 Viewer)

[Linda Rossiter]

I can enter the productcode and the productname will automatically fill in, through the relational join, having set the Control Source for the productname to the productcode in the recordset.

However, once using the form to enter data, although I see both the productcode and the productname, only the productcode is filled in on the table... the productname field is blank.

I'm a very new user; can you help?

 

[KevinM]

Are you sure that ProductName is bound to a field in the table?

How are you adding the product code, via a combo box?

 

[Linda Rossiter]

<<Are you sure that ProductName is bound to a field in the table?>>

No, I'm not sure... where do I check?

<<How are you adding the product code, via a combo box?>>

Yes

 

[KevinM]

First of all....

Do you need to store the Product Name twice in two different tables?
i.e. You have it in your Products table already and usually just storing the ProductID in your 'Other' table is enough as you can always use a query to retrieve the Prod Name from the Products table.
This is all well and good as long as you DON'T delete Products from the Products table or change their names. If you do then it is PERFECTLY normal to 'duplicate' this data (Name) and to store it twice, regardless of the so called 'database rules'.
If not then there is no need to store the Name again, the ProdID will suffice.

Anyway back to your original problem.

Delete your combo and start again.

Use the combo wizard and add ProdID and ProdName columns (UNHIDE ID field if the Wizard recommends you to hide it)
Store it in ID field when prompted.

On the AfterUpdate Event of the comboWizard you need to add the following line (in the vb window)...

[ProdName]=[ComboName].Column(1)

Change the above to YOUR names.

HTH

 

[Linda Rossiter]

Struggling a bit...

In afterupdate,
[ProdName]=[ComboName].Column(1)

is this an event procedure? I've so far got something like

Private Sub Combo53_AfterUpdate()
[ProdName] = [Combo53].Column(2)
End Sub

Second, even more basic question: on the form, where I want the product name to be filled in, this currently is a combo box. Should it be changed to a text box?

 

[KevinM]

Yes you have the correct event procedure, BUT if ProdName is in the SECOND column use...

[ProdName] = [Combo53].Column(1)

Ant NOT (2).
Column(0) is the FIRST column in the combo box.

[ProdName] can be a text box unless you want to give users the option of selecting by ID or by Name.

HTH

 

[Linda Rossiter]

Got it. Your help was VERY much appreciated this afternoon... looking through other people's questions, I could see that what I was asking was very basic, and your patience was marvellous. Thank you again!

 

[Linda Rossiter]

Probably thanked you too soon...

I wish to get more complicated now, by restricting the list that comes up in the third combo, area, based on the productcode.

In the criteria section of the productcode field within the productname combo box, I've put [forms]![myform]![combo16]

I'm stuck on the requery; I've already got

Private Sub Combo16_AfterUpdate()
[Productname] = [Combo16].Column(1)
End Sub

How do I then add

Me![Combo18].Requery

Is this a separate Sub, or what's the syntax to join it to the existing one?

 

[KevinM]

Private Sub Combo16_AfterUpdate()
[Productname] = [Combo16].Column(1)
Me![Combo18].Requery
End Sub

HTH