Assigning value from SQL statement to a variable

[jeffcravener]

Can anyone help me assign the value of an SQL statement to a variable...

Here is what I have so far:



Dim strID As Recordset
Set strID = New Recordset

strID.Source = "SELECT tblLIST_CS_EMP.ID " & _
"FROM tblLIST_CS_EMP WHERE tblLIST_CS_EMP.Full_Name = " & Me.cboName & ""

MsgBox strID.Source


The current output is the whole SQL statement with an equals sign and the coreect ID.

So, I am stuck on how to pull just the ID out of the recordset.

[RobDog888]

Actually this should be a question for the Databases forum,
but if I understand your question correctly you want to retrieve
an id field based upon an entered id value in the combo box?

Dim strID As Recordset
Set strID = New Recordset

strID.Source = "SELECT tblLIST_CS_EMP.ID " & _
"FROM tblLIST_CS_EMP WHERE tblLIST_CS_EMP.ID = " & Me.cboID.Text

MsgBox strID.SourceAssuming that ID field is a numeric and this is for Access or SQL.

[jeffcravener]

...didn't notice a database section.

Actually, I want to retrieve and ID field based upon an entered Full_Name in a combo box.

Basically I have 2 combo boxes, one cboName, and one cboID.

The user can use either one to select the person they are looking for.

However, when the full name is chosen, I want the cboID to show the appropriate ID, and vice versa.

I tried your code, with the adjustment:

Dim strID As Recordset
Set strID = New Recordset

strID.Source = "SELECT tblLIST_CS_EMP.L_ID " & _
"FROM tblLIST_CS_EMP WHERE tblLIST_CS_EMP.Full_Name = " & Me.cboName.Text


And the msgbox is just displaying the SQL code with value that is in the cboName.Text.

I am using an Access database and putting this code within a form in that access database,

[RobDog888]

Use the fields collection of the recordset. Modify your sql statement
for which cbo the user is using. Then populate the other cbo like
this...
Option Compare Database

Private Sub cboName_Click()

Dim strID As Recordset

Set strID = New Recordset

strID.Source = "SELECT tblLIST_CS_EMP.L_ID " & _
"FROM tblLIST_CS_EMP WHERE tblLIST_CS_EMP.Full_Name = " & Me.cboName.Text
strID.OpenRecordset dbOpenDynamic, dbReadOnly

Me.cboL_ID.AddItem strID.Fields("L_ID").Value

End Sub

Private Sub cboID_Click()

Dim strID As Recordset

Set strID = New Recordset

strID.Source = "SELECT tblLIST_CS_EMP.Full_Name " & _
"FROM tblLIST_CS_EMP WHERE tblLIST_CS_EMP.L_ID = " & Me.cboID.Text
strID.OpenRecordset dbOpenDynamic, dbReadOnly

Me.cboName.AddItem strID.Fields("Full_Name").Value

End SubYou will need to add error trapping and a little more logic to
complete your procedures.

HTH

[jeffcravener]

...Getting this error:

"Method or datamember not found" on this code:
.OpenRecordset
on this line:
strID.OpenRecordset dbOpenDynamic, dbReadOnly