Can not get value from combo box to update master table!!!!

[nanusecure]

I created a form for data entry into a table. The form, among other fields, contains three fields as combo boxes. Such fields (customer ID, address and Item ID) are queried from two separate tables (Item and Customer). The problem that I am having is that any time I enter a record into the table (Table1), the customer ID, address and Item ID have a random number, instead of the number in the combo box and entered thru the form.

My question is: How do I get such fields from the combo boxes to correctly update my table?

Hope this makes sense.

 

[dhookom]

We have trouble seeing your screen...
What are these properties of the combo boxes?

Control Source:
Row Source:
Bound Column:
Column Widths:

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jaydeebe]

You could write to the table using an SQl statement that is triggered by clicking a button.

DoCmd.RunSQL "INSERT INTO table1 ([CustomerID],[Address],[ItemID]) VALUES (Combo1,txt1,txt2);"

where table1 is the your table followed by the fields (customerID, Address, ItemID) and the VALUES for those fields are taken from controls on your form (combo1,txt1,txt2).

In this example the combo1 value is inserted into CustomerID field and so on. Hope this helps!


Jaydeebe BSc (hons)
MCSA 2k
MCSE 2k

 

[nanusecure]

Customer ID combo box

Control source - Customer-ID
Row source - SELECT CUSTOMER.ID, CUSTOMER.[Customer ID] FROM CUSTOMER;
Bound column - 1
Column widths - 0";1"

Customer Address list box:

Control source - Customer-ID
Row source - SELECT CUSTOMER.ID, CUSTOMER.[Ship to Name 1] & " " & CUSTOMER.[Ship to Address 1-Line One] & " " & CUSTOMER.[Ship to Address 1-Line Two] & " " & CUSTOMER.[Ship to City 1] & " " & CUSTOMER.[Ship to State 1] & " " & CUSTOMER.[Ship to Zipcode 1] AS Expr1 FROM CUSTOMER;

Bound column - 1
Column widths - 0";1"

Item ID combo box

Control source - Item-ID
Row source - SELECT ITEM.ID, ITEM.[Item ID] FROM ITEM; Bound column - 1
Column widths - 0";1"

I am getting the info I want on the form. The only problem is that after I enter the info on the form, I can not get the customer ID, address and Item ID info entered via the form to populate the main table, all I get is a weird, random number. My ultimate goal is to produce a customer and Item ID report, but I can't because the main table does not have the corresponding customer/item info populated on the main table.

I tried creating a report directly from the form, instead of the table but could not get it to display all the mentioned fields contained on the form.

I am looking for a way to pass the current customer-id, address and item ID info after I enter it on the form to the customer ID, address and item fields on the table or be able to design a report that takes these fields, among others into the report.

FYI - I do not have any problems with any other fields in the report, just the ones from the list or combo boxes that are populated from other tables.

 

[nanusecure]

jaydeebe,

Tried yor suggestion but getting an error. Just so you know, on this step I am just trying to update only the customer ID, if it works, I will copy the logic to the address and item id boxes. Below is the info that I pasted:

Public Sub Combo97_AfterUpdate()
DoCmd.RunSQL "INSERT INTO table1 ([Customer ID],VALUE (Combo1);"
End Sub

I am getting an error - Run-time error '3134':
Syntax error in INSERT INTO
statement.
also, I added the above logic as an "after update" event procedure on the customer ID combo box properties. Is this what you were referring to? or I did something wrong.

Thanks for your help.

 

[dhookom]

nanu-secure,
You table should be storing the ID values. I'm not sure why you have both the customer combo and address list box bound to the same field. Changing one should update the other.

Also, I doubt your control source is actually Customer-ID since it has a hyphen in it.

Your Item combo is more than a little confusing with an incorrect Control Source of: Item-ID and Fields Item.ID and Item.[Item ID]. Are you sure you have all this correct?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nanusecure]

To answer your questions:
1 - My bad. I was trying to get the customer ID combo box selection to update the address field in the list box below with the corresponding information. I concatenated all the address fields to this field and by mistake bound it to the same field as the combo box. Since this list box is comprised of many concaneted fields, how would I bind it to one field so I can update it on the table, which has these fields divided in 4-5 separate columns?

2 - Yes, the info that I sent you is what is displayed in the sql statement. All I did was create a combo box that pefromed a query on the Item table for the values. Maybe by playing around in the properties I added something by mistake.

Ultimately, I would like to have the Item, customer Id and customer address fields updated on my table from the data entry on the form. Right now, all I get is some random number on those fields.

 

[nanusecure]

DISREGARD PREVIOUS POSTS.

I figure out what is going on. The table is being updated with the primary key field. Instead of getting the customer Id or Item ID, I am getting the corresponding ID numbers (first column, primary key- auto number)for the corresponding record. What do I need to do to fix this so I get the next column over (customer Id or Item ID instead of the ID field (first column). Also, how do I update the address info from my list box, since is comprised of several fields concatenated into the list box (address 1, 2, city, state, zip and country).

Thanks again and apologize for the confusion.

 

[dhookom]

What's wrong with storing the primary key from your Customer table in the forms record source table? This is the standard method of using primary and foreign keys.

You also have provided a justification for storing address information in more than a single table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.