Recordset Question

[airman_30]

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.poin ts_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_N umber=redpts.Em ployee_Number " & _
"GROUP BY calc.Employee_N umber, calc.Last_Name,
calc.First_Name "
Set dbMyDB = CurrentDb()
Set rsMyRS = dbMyDB.OpenReco rdset(MySQL1, dbOpenDynaset)

If Not rsMyRS.EOF Then rsMyRS.MoveFirs t
Do While Not rsMyRS.EOF
cmbEmpNum.AddIt em rsMyRS!empnum
' cmbEmpNum.ItemD ata(cmbEmpNum.N ewIndex) = rsMyRS!empnum 'This
line throws an error
rsMyRS.MoveNext
Loop

End Sub

Private Sub cmbEmpNum_Click ()

rsMyRS.FindFirs t "empnum= '" & Str(cmbEmpNum.V alue) & "'"
'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

[phnz]

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.

ai*******@hotma il.com wrote:

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.poin ts_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_N umber=redpts.Em ployee_Number " & _
"GROUP BY calc.Employee_N umber, calc.Last_Name,
calc.First_Name "
Set dbMyDB = CurrentDb()
Set rsMyRS = dbMyDB.OpenReco rdset(MySQL1, dbOpenDynaset)

If Not rsMyRS.EOF Then rsMyRS.MoveFirs t
Do While Not rsMyRS.EOF
cmbEmpNum.AddIt em rsMyRS!empnum
' cmbEmpNum.ItemD ata(cmbEmpNum.N ewIndex) = rsMyRS!empnum 'This
line throws an error
rsMyRS.MoveNext
Loop

End Sub

Private Sub cmbEmpNum_Click ()

rsMyRS.FindFirs t "empnum= '" & Str(cmbEmpNum.V alue) & "'"
'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

[Rich P]

To load your combobox you can do this:

Private sub Form_Load()
Combo0.RowSourc e = "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 ***