Solved

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

Posted on 2014-03-24
6
672 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 119

Expert Comment

by:Rey Obrero
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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 …
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now