i am opening a recordset in my VB code and populating a combo box with
the data from one of the fields in my recordset. I want to set it up so
that, when a user selects one of the values from the combo box, the
text boxes on the form will be populated with the associated data. This
is driving me insane! I've researched almost all day on this, and can't
seem to find an answer. Any help would would be HUGELY appreciated as
this is part of a project for the pres of our company......

Public dbMyDB As DAO.Database
Public rsMyRS As DAO.Recordset
Public MySQL1 As String

Public Sub Form_Load()

MySQL1 = "SELECT DISTINCT (calc.Employee_Number) AS empnum, " & _
"sum(calc.total) AS totalearned,
sum(redpts.points_redeemed) AS used, " & _
"calc.Last_Name AS lname, calc.First_Name AS fname " & _
"FROM Calculation AS calc LEFT JOIN Redeemed_Points AS
redpts " & _
"ON calc.Employee_Number=redpts.Employee_Number " & _
"GROUP BY calc.Employee_Number, calc.Last_Name,
calc.First_Name"


Set dbMyDB = CurrentDb()
Set rsMyRS = dbMyDB.OpenRecordset(MySQL1, dbOpenDynaset)

If Not rsMyRS.EOF Then rsMyRS.MoveFirst
Do While Not rsMyRS.EOF
cmbEmpNum.AddItem rsMyRS!empnum
' cmbEmpNum.ItemData(cmbEmpNum.NewIndex) = rsMyRS!empnum 'This
line throws an error
rsMyRS.MoveNext
Loop

End Sub

Private Sub cmbEmpNum_Click()

rsMyRS.FindFirst "empnum= '" & Str(cmbEmpNum.Value) & "'"
'These will populate, but only with the data from the 1st record
regardless of the cmbEmpNum value
txtLastName = rsMyRS!lname
txtFirstName = rsMyRS!fname

End Sub

Airman,
Have one query as the rowsourse for your combobox.
Have another query which is the recordsource of your form an which
takes the value of the combo box as one of the criteria.
Then use the afterupdate property of th combo box to requery the form.



airman_30 (AT) hotmail (DOT) com wrote:

To load your combobox you can do this:

Private sub Form_Load()
Combo0.RowSource = "Select fld1, fld2, fld3 From tbl1"
End Sub

Then in the Click event of the combobox you can populate what ever you
need:

Private Sub Combo0.Click()
txt0 = Combo0.Column(0)
End Sub

Note: in the rowsource sql I load data from 3 columns of tbl1. In
order for all 3 columns to show up in the combobox at run time - go to
the property sheet of the combobox in design view and type 3 in the
Column Count property box (3 in my exmaple - could be less, more). The
default is 1 column - for which you don't need to enter anything in the
Column Count property. And to extend the populating idea, you could
populate more controls from one row of the combobox if you have more
than one column in the rowsource:

Private Sub Combo0_Click()
txt0 = Combo0.Column(0)
txt1 = Combo0.Column(1)
txt2 = Combo0.Column(2)
End Sub

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***