RecordSetClone Issue 1

[faeryfyrre]

Hi Guys,
Firstly, sorry about the length of this post. Getting yourself a cup of coffee might not be a bad idea

I am developing a database in which the client wants to have two modes for each form, a locked and an edit mode. This is to eliminate any accidental record updates (necessary as some of the expected users are totally computer illiterate and there has already been cases where data gets changed by accident).
My solution to this is to set the AllowEdits property of my form based on what mode the form should be in. To complicate matters, my forms have varying degrees of complexity where i can have two, three or even four nested subforms and i need to set the AllowEdits property for each of these subforms at the same time i set the main form.

I have written a recursive function that performs this task.
The function works fine when all the subforms have records but.....one of the problems i have to cope with is where a subform has no records (and therefore any subform's it may have are not loaded). In this case setting the AllowEdits property of this "Not Loaded" subform causes an error. If you look at my code below i am having trouble with the red portion.

Basically, i am looking at the recordsetclone of the subform and seeing if the recordcount is greater than zero and if so then i do the assigning to properties and resursive check of subform for nested subforms.

My problem is that for this recordset i cannot check whether there are any records, using .eof and .bof (so that .movelast does not throw an error due to no current record), as .bof and .eof are ALWAYS true even if there are records in the recordset. Now it has always been my experience that when you open a recordset the .BOF and .EOF will only be true if there are no records, but in my case this isn't so.

I have worked around this by forcing an On Error Resume Next and doing the .MoveLast regardless of whether the recordset has any records. The function does the task i wish performed but i would still like to know why this "RecordSetClone .EOF and .BOF always being true when first opened" thing is happening.

Any insight would be appreciated.

Thanks


ps The function is a bit more complex than it could be as i decided to write the function so that it could set the AllowEdits, AllowAdditions, AllowDeletions and AllowFilters properties at the same time.



Public Function setSubformDataEntryProperties(xForm As Form, _
Optional xAllowEdits As Variant, _
Optional xAllowAdditions As Variant, _
Optional xAllowDeletions As Variant, _
Optional xAllowFilters As Variant) As Boolean
On Error GoTo Err_setSubformDataEntry

Dim ctl As Variant
Dim xCount As Variant
Dim rcdCount As Variant
Dim rs As DAO.Recordset

xCount = 0
If Not IsMissing(xAllowEdits) Then xCount = xCount + 2
If Not IsMissing(xAllowAdditions) Then xCount = xCount + 4
If Not IsMissing(xAllowDeletions) Then xCount = xCount + 8
If Not IsMissing(xAllowFilters) Then xCount = xCount + 16

For Each ctl In xForm.Controls
If ctl.ControlType = acSubform Then
Set rs = ctl.Form.RecordsetClone
On Error Resume Next
rs.MoveLast
rcdCount = rs.RecordCount
On Error GoTo Err_setSubformDataEntry
If rcdCount > 0 Then
If Not (Nz(ctl.SourceObject) = "") Then
If Not IsMissing(xAllowEdits) Then ctl.Form.AllowEdits = xAllowEdits
If Not IsMissing(xAllowAdditions) Then ctl.Form.AllowAdditions = xAllowAdditions
If Not IsMissing(xAllowDeletions) Then ctl.Form.AllowDeletions = xAllowDeletions
If Not IsMissing(xAllowFilters) Then ctl.Form.AllowFilters = xAllowFilters
End If
Select Case xCount
Case 0
setSubformDataEntryProperties ctl.Form
Case 2
setSubformDataEntryProperties ctl.Form, xAllowEdits
Case 4
setSubformDataEntryProperties ctl.Form, , xAllowAdditions
Case 6
setSubformDataEntryProperties ctl.Form, xAllowEdits, xAllowAdditions
Case 8
setSubformDataEntryProperties ctl.Form, , , xAllowDeletions
Case 10
setSubformDataEntryProperties ctl.Form, xAllowEdits, , xAllowDeletions
Case 12
setSubformDataEntryProperties ctl.Form, , xAllowAdditions, xAllowDeletions
Case 14
setSubformDataEntryProperties ctl.Form, xAllowEdits, xAllowAdditions, xAllowDeletions
Case 16
setSubformDataEntryProperties ctl.Form, , , , xAllowFilters
Case 18
setSubformDataEntryProperties ctl.Form, xAllowEdits, , , xAllowFilters
Case 20
setSubformDataEntryProperties ctl.Form, , xAllowAdditions, , xAllowFilters
Case 22
setSubformDataEntryProperties ctl.Form, xAllowEdits, xAllowAdditions, , xAllowFilters
Case 24
setSubformDataEntryProperties ctl.Form, , , xAllowDeletions, xAllowFilters
Case 26
setSubformDataEntryProperties ctl.Form, xAllowEdits, , xAllowDeletions, xAllowFilters
Case 28
setSubformDataEntryProperties ctl.Form, , xAllowAdditions, xAllowDeletions, xAllowFilters
Case 30
setSubformDataEntryProperties ctl.Form, xAllowEdits, xAllowAdditions, xAllowDeletions, xAllowFilters
End Select
End If
End If
Next ctl

Exit_setSubformDataEntry:
Set rs = Nothing
Exit Function

Err_setSubformDataEntry:
ErrorNotification "ModGeneralFunctions", Err.Description, "setSubformDataEntry", Err.Number
Resume Exit_setSubformDataEntry
End Function


Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[Steve101]

Alec, give this a try:

Instead of testing eof or bof, determine the number of records in the subform's recordsetclone using:

RecCount = frmYourSubform.Form.RecordsetClone.RecordCount

Then base your processing on RecCount > 0; this will avoid the error handling situation.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[faeryfyrre]

Steve101 gets a star.

I'd always assumed that .RecordSetClone.RecordCount worked in the same way as .RecordSet.RecordCount
IE that you need to move to the last record in RecordSet before it gives an accurate result. this is obviously not the case.

Thanks



Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[Steve101]

Alec, yes so had I made this assumption, but when I ran into a similar problem to you a while back, and did some testing, I was happily surprised to find that recordsetclone did in fact return an accurate recordcount. This being said however, you make a good point. Nothing that I know of to prevent MS from changing this behaviour in aother version of Access so it would be good to establish that this behaviour of returning a true recordcount when the clone is opened, is documented and intended.

Cheers,
Steve

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)