Access VBA Recordset

Question

I have an access database that contains a table with employee information. My issue is that I want to loop through another table in order to determine if a specific characteristic is true or false first, then display all the records that are true in a continous form. It still populates all the records not just the ones that are true. Please see code below.

Private Sub RunQuery_Click()
Dim strSQL As String
Dim dba As Database
Dim tbl As Recordset
Dim Code As String
Dim status As String

Set dba = CurrentDb
strSQL = "SELECT DISTINCT EmployeeName,SSN,Location,SystemAssignedPersonID FROM dbo_tbl_Random "
strSQL = strSQL & "WHERE MenuUsed = 'Random' ORDER BY Location,EmployeeName"
Set tbl = dba.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
With tbl
  .MoveFirst
  If tbl.EOF Then
    MsgBox "There are no employees on Random at this time.", , "Oops! Try Again"
  Else
    Do Until tbl.EOF
      status = getEmpStatusID(tbl!SystemAssignedPersonID)
      If status = "A" Then
        Set Me.Recordset = tbl
        .MoveNext
      Else
        .MoveNext
      End If
    Loop
  End If
End With
Set tbl = Nothing
Set dba = Nothing
End Sub

The getEmpStatusID is a seperate function that is not giving me trouble. It looks up the Employee ID to get the information and returns it fine.

Thanks for the help!

Does this need to be done as a loop? Why not use no recordsets and just do this all through SQL? — Brad
Thanks for the response @Brad I am not as familiar with complicated SQL statements wirtten into VB. If you could provide some guidance it would be much appreciated. — designspeaks
It will be much simpler than manipulating recordsets. What does getEmpStatusID do? — Brad
getEmpStatusID takes the SystemAssignedPersonID, which is a unique number assigned to each employee (no duplicates) and looks into another table to find the employee statusm hence to 'A' for Active and 'T' for Termed. — designspeaks

Brad Brad · Accepted Answer · 2014-04-01T18:15:01

I think you're most of the way there already. Depending on what getEmpStatusID does you can do something like this

SELECT DISTINCT EmployeeName,SSN,Location,T.SystemAssignedPersonID 
FROM dbo_tbl_Random R
Inner JOin Table_Where_Status_Is_Found as T 
    on T.SystemAssignedPersonID = R.SystemAssignedPersonID
WHERE MenuUsed = 'Random' and T.SystemAssignedPersonID = 'A'
ORDER BY Location,EmployeeName

Use that as the rowsource for your form and don't use VBA. Your form will not be updatable because you used distinct, though. Is that necessary?

Access VBA Recordset

1 Answers