SQL statement in Access VBA not returning all records

I'm trying to pull records from a table and use them to populate a form.  I have 19 checkbox fields on the form for possibly assigned Job Categories for each Team Member.  And, each TM could have any combination of these Categories.  Each Category has its own record line in the table, which means if a TM has five categories assigned to them there would be five records in the table each with a field of USER_ID being unique to that User and a second field, JOB_TXT, holding the category (which would be different on each of the five records).

I created a query in Access to show me all records specific to a selected User ID from the form.  The query worked great.  So, I copied the SQL code from it and inserted it in to the AfterUpdate Sub for the field where the User ID is chosen on the form.

The problem is that when it runs, it only returns one record for any user, no matter how many records the user has in the table.

My question is, assuming a User ID has five categories assigned to it (thus, having five records in the table), can you tell me why the code below would only see and return one of those records instead of all five?

Dim strSQL As String

'declare recordset name
Dim rst

'Pull currently set up jobs for this User ID
strSQL = "SELECT CPCMEP_USER_JOB.[USER_ID], CPCMEP_USER_JOB.[JOB_TXT] " & _
         "FROM CPCMEP_USER_JOB " & _
         "WHERE (((CPCMEP_USER_JOB.[USER_ID]) Like " & Chr(34) & Me.UserIDSelected & Chr(34) & "));"

'assign array to recordset
Set rst = CurrentDb.OpenRecordset(strSQL)

'If there are records for the User...
If rst.RecordCount > 0 Then
    rst.MoveFirst
    Do While (Not rst.EOF)
    
        'Populate jobs on form based on what is set up
        If (rst(1) = "MOPS") Then
            Me.MOPS = True
        Else
            Me.MOPS = False
        End If
        
        If (rst(1) = "OPCO") Then
            Me.OPCO = True
        Else
            Me.OPCO = False
        End If
        
        If (rst(1) = "WLCM") Then
            Me.WLCM = True
        Else
            Me.WLCM = False
        End If
        
        If (rst(1) = "OPC2") Then
            Me.OPC2 = True
        Else
            Me.OPC2 = False
        End If
        
        If (rst(1) = "SLOG") Then
            Me.SLOG = True
        Else
            Me.SLOG = False
        End If
        
        If (rst(1) = "JJRF") Then
            Me.JJRF = True
        Else
            Me.JJRF = False
        End If
        
        If (rst(1) = "DFPP") Then
            Me.DFPP = True
        Else
            Me.DFPP = False
        End If
        
        If (rst(1) = "CSPK") Then
            Me.CSPK = True
        Else
            Me.CSPK = False
        End If
        
        If (rst(1) = "DREP") Then
            Me.DREP = True
        Else
            Me.DREP = False
        End If
        
        If (rst(1) = "INLV") Then
            Me.INLV = True
        Else
            Me.INLV = False
        End If
        
        If (rst(1) = "TASK") Then
            Me.TASK = True
        Else
            Me.TASK = False
        End If
        
        If (rst(1) = "ISTA") Then
            Me.ISTA = True
        Else
            Me.ISTA = False
        End If
        
        If (rst(1) = "SLOT") Then
            Me.SLOT = True
        Else
            Me.SLOT = False
        End If
        
        If (rst(1) = "KEYU") Then
            Me.KEYU = True
        Else
            Me.KEYU = False
        End If
        
        If (rst(1) = "SACC") Then
            Me.SACC = True
        Else
            Me.SACC = False
        End If
        
        If (rst(1) = "JUSM") Then
            Me.JUSM = True
        Else
            Me.JUSM = False
        End If
        
        If (rst(1) = "SUPR") Then
            Me.SUPR = True
        Else
            Me.SUPR = False
        End If
        
        If (rst(1) = "SPRT") Then
            Me.SPRT = True
        Else
            Me.SPRT = False
        End If
        
        If (rst(1) = "ELEV") Then
            Me.ELEV = True
        Else
            Me.ELEV = False
        End If
        rst.MoveNext
    Loop
End If

Open in new window

jmccloskAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
< Is there a way to get the total records information without having to run through every record with a counter loop? >

i already posted the codes above


if not rst.eof then
   rst.movelast
   msgbox rst.recordCount & " records found"
end if
0
 
Rey Obrero (Capricorn1)Commented:
what made you conclude that there is only one record returned?

to test how many records were returned, add the line after opening the recordset

Set rst = CurrentDb.OpenRecordset(strSQL)

if not rst.eof then
   rst.movelast
   msgbox rst.recordCount & " records found"
end if
0
 
jmccloskAuthor Commented:
That is exactly what I did.  I added a msgbox line at that spot to make sure I was getting the correct User ID, and to check how many records were in the recordset.  The ID came back correct.  The number of records came back as 1.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Rey Obrero (Capricorn1)Commented:
did add this line    rst.movelast ?
0
 
jmccloskAuthor Commented:
Ok, so I am not sure why my msgbox statement was coming back with one record:

MsgBox ("UserID: " & rst(0) & Chr(10) & Chr(13) & "Records: " & rst.RecordCount)

Open in new window


But, I have identified the fact that it is not the query that was causing my problem here.  It was the fact that in each of the If-Then statements I had an Else clause that said to make the checkbox false if the statement wasn't true.  

The statement would be true the first time, but then false the next 18 times!  So, it cleared out the first four checkboxes to false and only kept the last entry.

I solved the issue by taking out the Else clause of all the statements, and cleared all 19 checkboxes before I started the loop to make sure there was no leftover data from a previous search.

Even though I now can confirm the query works and retrieves all records, the msgbox statement still only says 1 every time?  That really tripped me up!
0
 
Rey Obrero (Capricorn1)Commented:
<the msgbox statement still only says 1 every time?>  that is correct

if you want to see incrementing count

dim j as long
j=1
If rst.RecordCount > 0 Then
    rst.MoveFirst
    Do While (Not rst.EOF)
   
    MsgBox "Record number " & j

   
   rst.movenext
   j=j+1
   loop
end if
0
 
jmccloskAuthor Commented:
So, the RecordCount statement does not return the total number of records in the array?  It only returns the current record?  Is there a way to get the total records information without having to run through every record with a counter loop?
0
 
jmccloskAuthor Commented:
Got it.  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.