[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 753
  • Last Modified:

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

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
Computer_Support_Norwich
Asked:
Computer_Support_Norwich
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
BitsqueezerCommented:
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
 
Computer_Support_NorwichAuthor Commented:
Thanks.... will have a look right now.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Computer_Support_NorwichAuthor Commented:
Hi Rey, sorry but neither of those bits of code help at all - same issue.

Christian - Will have a look at DCount.
0
 
Computer_Support_NorwichAuthor Commented:
Actually I just changed RecordsetClone to Recordset.Clone and it worked first time. - I'm happy with it thanks!
0
 
BitsqueezerCommented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now