CODEWITHSUNDEEP
excelvbams-access
Pat
Pat

Reputation: 149

Write from Excel to Access multicolumn combobox field using VBA

I have an Access file with two tables: users & products. Users keeps a list of who can write to the Access file (fields like userID, systemID, name). Products keeps a list of product attributes including who made the last update to the record. The last update field is a combobox with two columns: userID (bound to this), name (displays this due to column widths of 0";2").

I also have an Excel file, named simulator. Using VBA, the simulator reads from the products table, uses assorted prediction algorithms to simulate the product's future, then writes the predictions back to Access.

When Excel writes back to a product's record, I'd like to record the last update author to be simulator. Assuming this user exists (userID=100, name=Simulator, say), how do I do this?

I currently have

' Open Access database, get products table, get product record
connection.Open ...
products.Open "Products", connection, ...
products.Filter = "ProductID = " & productNumber

' Update record
products("LastUpdateAuthor") = "100; Simulator"
products.Update
products.Close

And this writes "100; Simulator" to the correct field. How do I get it to recognize that 100 is the bound column and Simulator is the second column?

Upvotes: 0

Views: 53

Answers (1)

June7
June7

Reputation: 21370

Should only save the UserID into LastUpdateAuthor field. Then multi-column combobox RowSource should be an SQL statement of Users table in order to retrieve and view the related UserName. So have a record in Users with UserID 100 and name Simulator, then still just save the UserID.

As long as the RowSourceType is Table/Query, it will see the 100; Simulator value as a single string from the LastUpdateAuthor field. Can set combobox RowSourceType as ValueList then use code manipulating recordset and Add method to load the LastUpdateAuthor data to the RowSource and the semi-colon will be recognized as column separator. However, if you do as described in first paragraph, this should not be necessary.

Upvotes: 1

Related Questions