vba Access 2013 tables as recordsets and using the data

Question

I have a form that is for data entry. I have a list box that has a list of all the products. I also have a second list box that has all the companies. I have a table for Customers, which has their name and an auto ID. I have a product list which also contains a name and an auto ID. I have a third table that lists what customers have which products.

Example:

tblCustomer
1    Company1
2    Company2
3    Company3

tblProducts
1    Product1
2    Product2
3    Product3

tblCustomerProducts

1    1    2 years
1    2    3 years
2    3    2 years

So it means 1 is the company, 1 is the product and they have it for 2 years

Now, when I do the entry form I am trying to open a recordset for both tables and loop through it and then when it finds a match it will put the corresponding number in the corresponding text box. This is what I have

Private Sub Command15_Click()
Dim db As Database
Dim rst As Recordset   'Gets a variable ready to put a table into
Dim rst2 As Recordset  'Gets second variable ready to put a table into

Set db = CurrentDb()
Set rst = db.OpenRecordset("customer") 'Sets variable rst to the contents of the customer table
Set rst2 = db.OpenRecordset("product") 'Sets variable rst2 to the contents of the product table

Do While Not rst.EOF                                 'Loop through the customer table until it gets to the end of the file
    If rst!["customer_name"] = lstCustomerName Then  'If the contents of the field customer_name is equal to that of the company highlighted on the form
    txtCustomerFX.Value = rst!["id"]                 'Then make the value of the the CustomerFX box equal to the ID associated with that company

    rst.MoveNext
Loop

rst.Close

Do While Not rst2.EOF                               'Loop through the product table until it gets to the end of the file
    If rst2!["product_name"] = lstProductName Then  'If the contents of the field product_name is equal to that of the product highlighted on the form
    txtProductFX.Value = rst2!["id"]                'Then make the value of the the ProductFX box equal to the ID associated with that product

    rst.MoveNext
Loop

rst2.Close
End Sub

It doesn't seem to be putting the data into the text boxes though.

Please be sure to read the helpful descriptions that pop up when selecting tags. — Charles
You may have to terminate your If..Then blocks with End If. VB only allows their exclusion if it's on the same line. — Jimmy Smith
Use proper indenting on your if statement and terminate it with End If. — enderland

pteranodon pteranodon · Accepted Answer · 2014-01-29T15:35:11

You don't need to dive into the recordsets to match display names and ids like this. A combobox or listbox can be bound to a hidden column of ids and just display the names. User sees names, database sees numbers. With control wizards turned on (the default) try creating a new combobox on your junction form. Choose:

Choose "I want the combo box to get the values from another table or query"
Choose tblCustomer
Add id and customer_name
Sort by customer_name
Make sure the box to Hide key column is checked
Choose a label

This sets up the properties of the combobox to store the ID in its value, but just display the name to the user. You can bind this field directly to the customer field on the the junction table and then eliminate the code (and the button) all together. Repeat for product, too!

vba Access 2013 tables as recordsets and using the data

1 Answers