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.Source

Assuming 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 Sub

You 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