Solved

SQL statement in Access VBA not returning all records

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

776 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