Solved

Microsoft Access Recordset only works once (have to reload form).

Posted on 2014-03-24
6
686 Views
Last Modified: 2014-03-25
I have the following code which successfully looks up nulls or fails in a given subform and then uses the result of the lookup to change a value on the parent form.

My problem is that the code only works once, to make it work again I have to close and reopen the form.

I don't understand why the code falls over, can anyone point me in the right direction?

Cheers.

Private Sub Command33_Click()

Dim rs As dao.Recordset
Dim bEmpty As Boolean
Dim bFail As Boolean

bEmpty = False
bFail = False
Set rs = Me.frmTestLineSub.Form.RecordsetClone
Do While rs.EOF = False
   
    ' Check if there are any EMPTY test result lines.
        If IsNull(rs!testlineresult) Or IsEmpty(rs!testlineresult) Or rs!testlineresult = "" Then bEmpty = True
    ' Check if there are any FAIL test result lines.
        If rs!testlineresult = "Fail" Then bFail = True
    
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
'Recordset.Close
'Set Recordset = Nothing


If bEmpty = True Then
    MsgBox "Tests are incomplete. The overall probe test result will also be set to FAIL.", vbCritical, "Test Module"
    GoTo SetFail
End If

If bFail = True Then
    MsgBox "Tests have failed. The overall probe test result will also be set to FAIL.", vbCritical, "Test Module"
    GoTo SetFail
End If

    MsgBox "All tests have passed. The overall probe test result will also be set to PASS.", vbInformation, "Test Module"
    GoTo SetPass

GoTo LastLine

SetFail:
    Me.testresult.Value = "FAIL"
    GoTo LastLine

SetPass:
    Me.testresult.Value = "PASS"
    GoTo LastLine

LastLine:

End Sub

Open in new window

0
Comment
  • 3
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39951205
you need to requery your subform after running the codes.

add this line before End Sub

Me.frmTestLineSub.Form.requery

or

Me.frmTestLineSub.requery
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 39951596
Hi,

you do not need to requery the form. But you're working with RecordsetClone which is an always available copy of the form's recordset which is invisible in the background. The sense is that you can work with that without moving the main recordset pointer which would change the current record and fire the events.
So if you use a loop to move the RecordsetClone to the end of the records then it will stay there until you use a "MoveFirst" to start the loop again. Otherwise it will always end in an EOF.

If you really need a fresh copy of the form's recordset you must use "Recordset.Clone" (look at the point between) but this would not be needed here. In fact, you do not need the loop or a recordset in general here. Simply use DCount if you want to see the number of records with a specific condition so you can see the result immediately - think SQL and not VBA when working with a database. Recordsets are only needed in very specific situations.

Cheers,

Christian
0
 

Author Comment

by:Computer_Support_Norwich
ID: 39952557
Thanks.... will have a look right now.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:Computer_Support_Norwich
ID: 39952561
Hi Rey, sorry but neither of those bits of code help at all - same issue.

Christian - Will have a look at DCount.
0
 

Author Comment

by:Computer_Support_Norwich
ID: 39952573
Actually I just changed RecordsetClone to Recordset.Clone and it worked first time. - I'm happy with it thanks!
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39952892
Hi,

Recordset.Clone was only to show you the difference, it works of course but is not needed as this creates a duplicate of the complete recordset. Does not make a difference if you have only 10 records in your recordset but if you have 10,000 records then that would be a complete waste of time and resources.
A really quick solution for your code would be to use a MoveFirst at the beginning to make sure your RecordsetClone (without the dot) is at the start of the list, that's all. But you should really consider to use a DCount instead of the complete recordset loop. In case of a very long recordset the loop takes a lot longer to perform.

Cheers,

Christian
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

838 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