Link Combo Box

[Mando13]

I have a form which links to a table in MS Access. My form displays all the data and populates every field. Now I'd like an easier way to navigate through all the fields so I added a combo box.
The problem I'm having is I cannot get the rest of the data to change when I select a different selection on my combo box.
Can someone help?
My current code is below.



Private Sub Form_Load()
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=G:\ITG Budgets\DB TEAM\Contingent Compensation.mdb;"
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select COMPANY,DEPT,DEPTNAME,DIV,EMPNAME,EMPNO,FLSA,HIREDATE,JOBCODE,JOBDATE,JOBTITLE,LVL,Maximum,Midpoint,Minimum,PAYGRP,PERF,REPORTSID,REPORTSTO,REVDATE,SALARY,[SALARY PLAN],STATUS,[VP LEVEL 1] from test2 ORDER BY EMPNO", db, adOpenStatic, adLockOptimistic
Dim oText As TextBox
'Bind the text boxes to the data provider
For Each oText In Me.txtFields
Set oText.DataSource = adoPrimaryRS
Next
'Populate the Combobox
Set Combo1.DataSource = adoPrimaryRS
Combo1.AddItem adoPrimaryRS("EMPNO")
Do Until adoPrimaryRS.EOF = True
Combo1.AddItem adoPrimaryRS("EMPNO")
adoPrimaryRS.MoveNext
Loop
End Sub

Private Sub Combo1_Click()
For Each oText In Me.txtFields
Set Combo1.DataSource = adoPrimaryRS
Set oText.DataSource = adoPrimaryRS
Next
End Sub

 

[Ruffnekk]

In the Combo1_Click() method you only set the datasource, but you do not look up the corresponding record anywhere?

Regards, Ruffnekk
---
Is it my imagination or do buffalo wings taste just like chicken?

 

[Mando13]

Thanks Ruff but how do i go about doing that?

 

[BobRodes]

Looks like you've been using the Data Form Wizard, and don't quite understand what's going on. Let's see if we can sort things out.

First thing is, setting the datasource of the combo box will just tell the combo box to use adoPrimaryRS as its data source. If adoPrimaryRS doesn't change, then neither will the data being displayed. So, all the different times you set the DataSource property (like inside your loop for your text boxes) you aren't actually doing anything. Furthermore, if you use AddItem to populate your text box (the better way to do it) you don't need to use the datasource property at all.

Before we fix it, let's first get clear on what you're trying to do. Do you want your combo box to display a list of all the employee numbers, and then when you select one, have all the text boxes show that employee's data? And would you rather have it display the employee names instead and do this? Answer those and we'll see if we can sort things out for you.

HTH

Bob

 

[Mando13]

Thanks for clearing that up Bob. The answer to both your questions is Yes. I'd like the combobox to be populated with user names and when clicked then the rest of the text boxes on the form populates with the correct data for that particular employee.

 

[BobRodes]

Ok, here's what I would do:

1. Don't bind the combo box using the DataSource property.
2. Do this:

 with adoPrimaryRS
   .MoveFirst
   Do Until .EOF
      Combo1.AddItem !EMPNAME
      Combo1.itemdata(combo1.newindex) = !EMPNO
      .MoveNext
  Loop

Now, the itemdata property can be set to whatever integer value you want. Each line item has its own itemdata value, so you have combo1.itemdata(0), for example, which will correspond to combo1.list(0). So, list will have the name, itemdata will have the employee number.
3. In the combobox click event, set the recordset's filter property to the current itemdata value. (see if you can figure that one out.)

HTH

Bob