Solved

SQL statement in Access VBA not returning all records

Posted on 2014-03-27
8
826 Views
Last Modified: 2014-03-27
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

0
Comment
Question by:jmcclosk
  • 4
  • 4
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 

Author Comment

by:jmcclosk
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
did add this line    rst.movelast ?
0
 

Author Comment

by:jmcclosk
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<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
 

Author Comment

by:jmcclosk
Comment Utility
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
< 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
 

Author Comment

by:jmcclosk
Comment Utility
Got it.  Thank you!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now