Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL statement in Access VBA not returning all records

Posted on 2014-03-27
8
859 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39959617
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
ID: 39959627
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39959634
did add this line    rst.movelast ?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:jmcclosk
ID: 39959681
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39959746
<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
ID: 39959754
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39959873
< 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
ID: 39959925
Got it.  Thank you!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

791 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