Combo Box not saving to table

[dgriffin]

I have 2 tables set up Product_Produced and English_Products

In my form I have a combo box named ProductNo_Combo

Control Source is ProductNo

It’s row source is SELECT English_Products.ProductNo, English_Products.ProductName, English_Products.Factor FROM English_Products;

Column count 3
1”,1”,1”
Bound 1

I have 2 other text boxes in this form that auto fill with the selection of the product number.
This all works fine.

The problem is the only field that saves in the Product Produced table is the productNo
I need the productno and factor fields to be saved in the Product Produced Table

Only the productno is being saved in the Product_Produced table

Why isn’t the factor being saved to the Table.

The textbox for the factor has
Control source =ProductNo_Combo.column(1)


I’ve also have the after update set up with Me.ProductName = Me.ProductNo_Combo.Column(1)

Yet still no update.

Could you please tell me what I’m doing wrong.

Thank you

[ketbdnetbp]

You stated:
The textbox for the factor has
Control source =ProductNo_Combo.column(1)

You could try...

Textbox Name = Factor
Textbox Control Source = Factor

Then, set it's value in the AfterUpdate Event of "ProductNo_Combo", Me![Factor] = ProductNo_Combo.column(1)

All the best,

Jim

[dgriffin]

That didn't work. Not only did it not save the data but it also doesn't show in the form.

[ketbdnetbp]

Can you post the code you put in the AfterUpdate Event of the combo box?

[khalid]

The question is why you want to save the columns data to your table? this would violet the database normalization basics in Access

In good database design, one record can be related to many records with foreign and primary key, Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations.

if you want to go with your way, then you have to store the data in the actual field on the form, not the unboud controls (text box) on the form.

Textbox1=ProductNo_Combo.column(1) will show one the column value in the text box:
you can save the combobox column to your field on After_update event of the combox

Private sub combox_AfterUpadate()
fieldonTheForm.value=ProductNo_Combo.column(1)
End sub

[dgriffin]

Actually you are correct. I shouldn't save the data twice. The product name I don't have to do but the Factor field can change from time to time. I need to lock it in the English Production table so when they change the factor it doesn't change the data entered previously. I did try the code you suggested but it still doesn't save to the table.

[khalid]

if you want to save the factor also to your table, then you should have a field for factor in the target table. Do you have a field for the factor field in the receiving table?
if yes then:
1) Add that field to your form
2) On After Update Event of your combo box:

Private Sub ProductNo_Combo_AfterUpdate()
Me.FactorField.Value = Me.ProductNo_Combo.column(2)
End Sub

Where Me.FactorField is the field on the form where you want to save the combo box value and Me.ProductNo_Combo.column(2) is the third column (Factor) of the combo box;
Combo Box / List Box columns Start with 0,1,2,3...
hope this help

[dgriffin]

I'm sure I'm missing something but this still doesn't work.
Yes there is a field in the receiving table called Factor. On the form I have a combo box called productNo. Its is set up with 3 columns (productNo, productName, factor). When I punch in the product number I want the product name and the factor to show up in the form. So to acheive that I have the combo box called ProductNo with the 3 fields and 2 text boxes. 1 for the Product name and 1 for the Factor. I use this code =[ProductNo_Combo].[column](1) =[ProductNo_Combo].[column](2) in the control source to get the 2 text boxes to show Product name and Factor. That works fine. No matter what I try including what you've suggested I can't get the Factor to save in the English_Produced table. I have no idea what I'm missing. I tried the code you suggested in the after update and nothing happens. Even if I just put the Factor field in the control source it still doesn't save and then it no longer appears in the form field.

[dgriffin]

I've attached the file. Maybe I'm not explaining myself right. I hope this will help. Thank you.

[khalid]

I've attached the file. Maybe I'm not explaining myself right. I hope this will help. Thank you.

Hi, I have studied your form, its very simple and clear. For saving the combo data to your fields on form is very simple:

see this code:

Private Sub ProductNo_Combo_AfterUpdate()
Me.ProductName = [ProductNo_Combo].[Column](1)
Me.FACTOR = [ProductNo_Combo].[Column](2)
End Sub

What it does? This code is just populating your fields (ProductName and FACTOR) on the form on ProductNo_Combo_AfterUpdate event.

On your form you have these field, but with the Control Source Set to = [ProductNo_Combo].[Column](1) and = [ProductNo_Combo].[Column](2) respectively.

Remove this control source from both the fields and Select the ProductName and FACTOR fields from the Control Source (the field names of your form from the underlying table - the source table of the form)

On AfterUpdate Event of the combo box, these will be filled automatically this time with the columns of the combox and will save to the table.

Hope you got the clue:
I have Access-2010 which is not compatible with your attached db, I have converted your db in higher version and become useless for you to open. so try just what I said and analyse the data in your table for saving.

hth.

[dgriffin]

That worked. Thank you so much.

[khalid]

That worked. Thank you so much.

Glad that the problem is solved