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

[The_Doc_Man]

@Gasman - I didn't think that RS("ID") would work... but on reflection, it ALSO uses the rule that the default property would be the .Fields collection - for which saying RS("fieldname") is the same in effect as saying RS.Fields("Fieldname"). So I stand ... well, .... SIT corrected.

Even so, using the RS("ID") syntax is probably a little bit confusing. It confused me on first reading, for example. However, the OP used Pat's solution which is probably the best choice anyway.

 

[Pat Hartman]

Glad you could work with the query. I didn't actually look at the code. If you are trying to update the recordset, you might want to use an action query instead. In general action queries are more efficient than code loops.

 

[Gasman]

I would also say not the best method of referencing, hence my test.

 

[slharman1]

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

Pat,
I am having a problem with the rs using the join statement.
If I add a new employee to the table and there is no timeclock punch yet - the query returns no records!
What do I do now?
I also split the database to contain a backed with the tables.
Thanks all

 

[Gasman]

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

You need to move to the next line to see the value.?
When you are at a breakpoint/line, that line of code has NOT executed.

 

[slharman1]

My recordset is empty because of the join. I have written a query with the join type stating to show all records from the table tblEmployee and only those records from tblTimeClock where the joined fields are equal, but I am still getting no records.
How do I write a query to show all records from tblEmployee even if there are no records from tblTimeClock in it yet.

 

[slharman1]

@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?

What do i do if the RS contains no records?

 

[slharman1]

Can anyone figure out how to get past the SQL2 statement and accompanying "set rs2 =" towards to the bottom of my code.
I went back to trying two recordsets because the second rs is empty, but I am not sure if that is why I am getting the 3601 error "too few parameters."

Private Sub cmdOK2_Click()
    
    Dim db As Database
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim rs2 As DAO.Recordset
    Dim SQL2 As String
    
    
    
    
    If Me.txtLogin.Value > 0 Then
    
        
        SQL = "SELECT * FROM tblEmployee WHERE Login = '" + Me.txtLogin.Value + "'"
    
        Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
    
        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.MoveLast
        
  
    Dim employeeid As Long
    employeeid = rs("EmpID")
        
        SQL2 = "SELECT * FROM tblTimeClock WHERE EmpID = employeeid"
        Set rs2 = CurrentDb.OpenRecordset(SQL2, dbOpenDynaset)
    
    If rs2.RecordCount < 1 Then
            DoCmd.OpenForm "frmTimeClock", , , , acFormAdd
            Forms!frmTimeClock.cboEmpID = employeeid
    ElseIf IsNull(rs2("TimeOut")) Then
        DoCmd.OpenForm "frmTimeClock", , , "EmpID =" & employeeid
        DoCmd.GoToRecord , , acLast
    Else: DoCmd.OpenForm "frmTimeClock", , , , acFormAdd
          Forms!frmTimeClock.cboEmpID = employeeid
    End If
    
    rs.Close
    Set rs = Nothing
    
End Sub

 

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

Doc Man, I somehow missed this post. Been screwing around for hours but this was the correct answer! Thank you so much! I can sure get lost in the weeds sometimes

 

[The_Doc_Man]

You are welcome. And as to weeds, they and I are on very intimate terms 'cause I've been lost in them so many times I know them by name. So don't feel bad.

 

[Gasman]

Doc Man, I somehow missed this post. Been screwing around for hours but this was the correct answer! Thank you so much! I can sure get lost in the weeds sometimes

I give up

 

[slharman1]

I give up

Aw don’t say that! You have been a big help as well. I am not that proficient yet so I don’t always understand what someone is trying to show me - but I don’t give up!
Thank you!

 

[Pat Hartman]

What do I do now?

Use a LEFT join instead of an INNER join. Build it with the QBE if you don't know the syntax.