CODEWITHSUNDEEP
ms-accessvbams-access-2013
jordankoal
jordankoal

Reputation: 73

vba Access 2013 tables as recordsets and using the data

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.

Upvotes: 1

Views: 8469

Answers (1)

pteranodon
pteranodon

Reputation: 2059

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!

Upvotes: 1

Related Questions