[SOLVED] Access VBA Recordset

Access VBA Recordset

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!

Solution

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?