Solved

SQL statement in Access VBA not returning all records

Posted on 2014-03-27
8
885 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
[X]
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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

734 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