Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-03-24
6
Medium Priority
?
742 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 2000 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

879 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