Solved getting a second recordset based on a record in the first recordset (1 Viewer)

[slharman1]

I can't seem to get the second SQL statement to work, access says "too few parameters, expected 1."
set rs2 = is the line I am not getting right.
all variables are set in the code above where I copied and pasted, rs and rs2 are declared as dao.recordsets
and sql and sqltime are declared as strings

    Set rs = CurrentDb.OpenRecordset(SQL)
    rs.MoveFirst
   
    employeeid = rs("EmpID")
     
    sqltime = "SELECT * FROM tblTimeClock WHERE EmpID = employeeid"
   
    Set rs2 = CurrentDb.OpenRecordset(sqltime)

 

[Gasman]

You need to concatenate employeeID.?

I am sure I am repeating myself here?

 

[slharman1]

You need to concatenate employeeID.?

I am sure I am repeating myself here?

I don't understand, there is nothing to concatenate. employeeid is a variable defined by the first recordset and used in the where statement of the second recordsets query statement.
What am i missing?

 

[Gasman]

Debug.Print sqlTime and see if you can work it out?

You should do this a lot ATM as a matter of practice, confirming what you actually have, not what you think you have.

 

[Gasman]

So why did you code this in another post?

[Login] =" & Me.txtLogin

The process is the same?

 

[slharman1]

So why did you code this in another post?

[Login] =" & Me.txtLogin

The process is the same?

I believe that was when i was trying to accomplish this using dlookup, now i am working with recordsets and employeeis is a variable.
I guess am lost.
Here is the full code:

Private Sub cmdOK_Click()

    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim employeeid
    Dim rs2 As DAO.Recordset
    Dim sqltime As String
   
   
   
    If Me.txtLogin.Value > 0 Then
   
       
        SQL = "SELECT * FROM tblEmployee WHERE Login = '" + Me.txtLogin.Value + "'"
   
        Set rs = CurrentDb.OpenRecordset(SQL)
   
        Else
        Exit Sub
    End If
   
       
    If rs.EOF Then
        MsgBox "Incorrect Login", vbInformation, "Re-enter Login"
        txtLogin.SetFocus
        Exit Sub
    End If
   
    rs.MoveFirst
    If rs("Password") <> Nz(Me.txtPassword, "") Then
        MsgBox "Incorrect Password", vbInformation, "Re-enter Password"
        txtPassword.SetFocus
        Exit Sub
       
    End If
    Set rs = CurrentDb.OpenRecordset(SQL)
    rs.MoveFirst
   
    employeeid = rs("EmpID")
    rs.Close
    Set rs = Nothing
         
    sqltime = "SELECT * FROM tblTimeClock WHERE EmpID = employeeid "
   
    Set rs2 = CurrentDb.OpenRecordset(sqltime)
   
       
    DoCmd.OpenForm "frmTimeClock", , sqltime
   
   
End Sub

 

[slharman1]

Debug.Print sqlTime and see if you can work it out?

You should do this a lot ATM as a matter of practice, confirming what you actually have, not what you think you have.

I get nothing from the debug.print

 

[CodeByJim]

Set rs = CurrentDb.OpenRecordset(SQL)

Try re-writing this line:
Set rs = CurrentDb.OpenRecordset(SQL)
as follows.

Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)

 

[slharman1]

Try re-writing this line:
Set rs = CurrentDb.OpenRecordset(SQL)
as follows.

Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)

Jim, I tried it and still doesn't work, it appears that the problem is the sqltime SELECT statement is empty, but maybe it's because my variable employeeid is not getting a value from the first recordset, rs("EmpID"), which is the pk of the record set from the table tblEmployee

 

[Gasman]

You have to debug.print after you set it?

 

[Pat Hartman]

If you create a query that joins the two tables and open a recordset based on the query, you don't need the second recordset.

 

[The_Doc_Man]

The problem is simple. You say that EmployeeID is a variable. I agree with you . But the problem is "scope" which is VBA-speak for "what can I see?" You need to understand this. Once you do, you won't make this mistake again.

There are TWO environments when using Access. One is the User Interface (UI) - which includes the graphic UI and the code UI. The other is the SQL environment where all SQL gets sent to the database engine (these days, ACE) to be executed. The UI and the SQL engine are in physically different processes. They DO NOT share memory with each other (so far as we know). The UI simply sends input to the SQL engine's input channel and retrieves data from the output channel, whatever they are in your specific case.

To create a recordset, you must involve ACE because the UI doesn't have the power to make its own recordset. Hint: That is WHY you have to do things like "CurrentDB.OpenRecordset"in the first place.

So back to the point: EmployeeID truly IS a variable - in the UI. But the recordset was CREATED in the SQL arena where EmployeeID doesn't exist. When you open a recordset, whether it is a named query or table, or an SQL statement, you are passing info to ACE. But ACE doesn't know EmployeeID because it is in the UI's memory. For that reason, when you have a value to be included in the SQL, you must put the VALUE, not the variable, in the SQL string. Your SQL string in post #1 SHOULD have looked like

sqltime = "SELECT * FROM tblTimeClock WHERE EmpID = " & CStr( employeeid ) & " ; "

EDIT: Which is not to disagree with Pat Harman's statement. She is also correct. We are addressing two different issues for the same problem.

 

[slharman1]

If you create a query that joins the two tables and open a recordset based on the query, you don't need the second recordset.

Thank you so much, I see how this might solve my issue very simply.

 

[slharman1]

The problem is simple. You say that EmployeeID is a variable. I agree with you . But the problem is "scope" which is VBA-speak for "what can I see?" You need to understand this. Once you do, you won't make this mistake again.

There are TWO environments when using Access. One is the User Interface (UI) - which includes the graphic UI and the code UI. The other is the SQL environment where all SQL gets sent to the database engine (these days, ACE) to be executed. The UI and the SQL engine are in physically different processes. They DO NOT share memory with each other (so far as we know). The UI simply sends input to the SQL engine's input channel and retrieves data from the output channel, whatever they are in your specific case.

To create a recordset, you must involve ACE because the UI doesn't have the power to make its own recordset. Hint: That is WHY you have to do things like "CurrentDB.OpenRecordset"in the first place.

So back to the point: EmployeeID truly IS a variable - in the UI. But the recordset was CREATED in the SQL arena where EmployeeID doesn't exist. When you open a recordset, whether it is a named query or table, or an SQL statement, you are passing info to ACE. But ACE doesn't know EmployeeID because it is in the UI's memory. For that reason, when you have a value to be included in the SQL, you must put the VALUE, not the variable, in the SQL string. Your SQL string in post #1 SHOULD have looked like

sqltime = "SELECT * FROM tblTimeClock WHERE EmpID = " & CStr( employeeid ) & " ; "

EDIT: Which is not to disagree with Pat Harman's statement. She is also correct. We are addressing two different issues for the same problem.

Hmm, Thank you but I am still a little confused, it seems to me then that my line employeeid = rs(Empid) does not work. (EmpID is an integer in the rs) after halting my code and highlighting that line, I hover over rs(EmpID) and vba pops up with it being EmpID=Empty
So how do I set the value of the variable if I cannot set it using the first recordset?
Thanks in advance

 

[Gasman]

Might want to show what SQL is?

 

[Pat Hartman]

If the EmpID in the recordset is empty, there is no current record.

 

[Gasman]

Show all the code please, as what you are saying does not make sense.?
If the field EmpID was not in the recordset Access would complain, same with no recordset.?

You don't have On Error Resume Next do you?

Regardless, you STILL have to concatenate empID to the string sqltime

 

[slharman1]

I used Pat’s suggestion of joining the two tables in my rs to get the data I needed. Thanks Pat!

 

[The_Doc_Man]

I agree that Pat's method is more economical and thus desirable. However, I will answer the implied question anyway. You had trouble with:

employeeid = rs("EmpID")

You would get better results from either of two different syntaxes.

#1

employeeid = rs![EmpID]

OR

#2

employeeid = rs.Fields("EmpID")

Because RS isn't a collection, it's a singular object, and doesn't have a member property called "EmpID". RS - as an open recordset - DOES have a property called Fields - a collection of the fields in the recordset - which you can access using the 2nd syntax I offered. Because of the default behavior of Access when dealing with objects having properties (i.e. nearly everything), the first alternative I offered also works because .Fields is the default property for a recordset so the "BANG" syntax will work here as a way to select the "EmpID" element of the default property (which is a collection of named fields.)

 

[Gasman]

@The_Doc_Man
I'd have to say, that I tried the code below?, and it produced a valid and correct value?

Sub TestRS()
Dim strSQL As String, empID As Long
Dim DB As Database, rs As DAO.Recordset, rsc As DAO.rec
strSQL = "Select * from transactions"
Set DB = CurrentDb()
Set rs = DB.OpenRecordset(strSQL)
rs.MoveFirst
empID = rs("ID")
Debug.Print empID

rs.Close
Set rs = Nothing
Set DB = Nothing
End Sub

That said, even if all that worked, the lack of concatenation would still have been the problem? That is my view of it?