Solved

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

Posted on 2014-03-24
6
713 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
[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
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

634 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