Executing a select Query and storing into a RecordSet

[markronz]

Hey everyone-
I'm a little unclear on where to start on this one. I have a form in Access. Within the code on that form, I want it to execute a SELECT command of the current database, and then store it into a recordset. I will be using that recordset to populate some different fields on the form.

The query looks something like this:
"Select * from CustomerInformation where CustomerKey = 1"

I've been looking around on the internet and I've gathered that my code needs to look something like this:

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim sSQL As String
    Set db = CurrentDb()
    sSQL = "Select * from CustomerInformation where CustomerKey = 1"
    Set qd = db.CreateQueryDef("Test", sSQL)

I understand that this creates a query in Access. But I have no idea how to run it and place the contents into a recordset.

Can anyone help me with that? Or maybe help me start over from scratch if I am really off on how to accomplish this?

Thanks
-Mark


Oh, one last thing, as a follow up question. Do you know how you would parse apart the recordset once I have it? Say the query returns someone with this information:
FName: Mark
LName: Smith
JobCode: 234

If my recordset is named simply rs, would the FName data be rs(1)?

Thanks again!

 

[JerryKlmns]

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb()
sSQL= "Select * from CustomerInformation where CustomerKey=1"
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
txtPersonID=rs.Fields(0) 
txtFname=rs.Fields("FName").Value
txtLName=rs.Fields("LName")
txtJobCode=rs.Fields![name]

All 4 ways to refer to a recordset's field are valid I just prefer #3
Make sure that when using the ordinal position of a field remember to never, ever change the table structure or the
SQL statement of the query if you use one already saved.

You could also put a character in every text box you need to populate with the recordset, loop all the controls on the form and check the .Tag & .ControlType of it. Just name the controls like the fields of the recordset and use the .Name of the control for referring to the field

Dim ctl As Control

For Each ctl in Me.Controls
  If ctl.Tag="?" And ctl.ControlType = acTextBox Then  ctl.Value = rs.Fields(ctl.Name)
Next

 

[markronz]

Thanks a lot!!