Hi,

I have 2 subforms that are updated by the user. When the user clicks on a Calculate button I read through the entered fields in the subforms looking for specific data. I read the correct data the first time around but if I click on the button a second time I get the "No current record." error message. How do I reset the subforms recordset so this error does not occur and I can click on that button all I want. Below is the code executed. I'm only transferring the data to a string for testing purposes.

    For tmpint = 0 To [dataFabric subform].Form.Recordset.RecordCount - 1

        For Each tmpcontrol In [dataFabric subform].Form.Controls
            If tmpcontrol.name = "FabricID" Then
              tmpstring = tmpstring & tmpcontrol & " : "
            End If
            If tmpcontrol.name = "EstimatedCost" Then
                tmpstring = tmpstring & tmpcontrol.name & " ->" & tmpcontrol.Value & vbCrLf

            End If
        Next tmpcontrol

        [dataFabric subform].Form.Recordset.MoveNext
    Next tmpint


Any help would be appreciated.

Judging from the error message it looks like you need to begin the sequence with:

If Not [dataFabric subform].Form.Recordset.EOF Then
    [dataFabric subform].Form.Recordset.MoveFirst
    For tmpint = 0 To [dataFabric subform].Form.Recordset.RecordCount - 1
...
...
    Next
End If

That way it always starts from the first record in the subform no matter which record is currently selected in the subform. You would also do well to use a recordset variable rather than using the full syntax each time. Not only is it shorter to type, it runs faster. I would also suggest changing it to a While loop so you don't need to do an EOF If test. Testing EOF is faster than reading a count and the code is simpler.

Dim rst As DAO.Recordset

Set rst = [dataFabric subform].Form.Recordset

While Not rst.EOF
....
    rst.MoveNext
Wend

Also, I wouldn't iterate the controls so much as the field values and it would probably be a good idea to set the recordset variable to the recordsetclone rather than the actual recordset.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Thanks for your help! Unfortunately, the line

Dim rst As DAO.Recordset

causes my Access application to CRASH! Arrrgggg! Using ADODB.Recordset causes the error "Type mismatch" from

Dim tmprs As ADODB.Recordset
Set tmprs = [DataFabric subform].Form.Recordset

Any more suggestions as I search for why DAO crashes Access 2002 on Windows XP?

I agree with not iterating through all of the controls but rather just the fields. How does one do this?

Oh, I've found the referencing with a variable of type "variant" works as opposed to using DAO. Unfortunately, I still get the same error "No current record" message after the initial push of the button.

Strangely, the code

Dim tmprs As Variant
Set tmprs = [DataFabric subform].Form.RecordsetClone

and everything else being the same causes only the first record of the subform to be read ( i.e. the movenext doesn't seem to work ).

My suggestion was predicated on a reference to the DAO library which you obviously do not have set. Using a variant results in late binding which is not efficient. Given that you are using ADO as the current data access library, the recordset and recordset clone syntax may be a bit different from what I suggested and as a result, it is not working as desired.

Try it again with:

Set tmprs = [DataFabric subform].Form.Recordset

rather than the clone. In DAO I always use the clone so as not to move the form current record but I'm not certain whether this will work with your current default references. The machine I am working at today only has Access 97 so I am unable to verify the ADO or ADOX requirements.

With DAO and RecordsetClone, (which may not work without an explict reference set from the module toolbar - Tools - References), and assuming the field that the control is bound to has the same name as the control:

Dim tmprs As DAO.Recordset
Dim fld As DAO.Field

Set tmprs = [DataFabric subform].Form.RecordsetClone

tmprs.MoveFirst
While Not tmprs.EOF
    For Each fld in tmprs.Fields
        If fld.Name = "FabricID" Then
            tmpstring = tmpstring & tmpcontrol & " : "
        End If
        If fld.Name = "EstimatedCost" Then
            tmpstring = tmpstring & tmpcontrol.name & " ->" & tmpcontrol.Value & vbCrLf
        End If
    Next
    tmprs.MoveNext
Wend


Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Well, I'm confused. I've gone into my Access Tools -> References and set the various Data Objects that seem to relate:

Microsoft ActiveX Data Objects 2.7 Library
Microsoft DAO 3.6 Object Library

and I run the above code ( which had 2 small errors in the data to capture but I corrected it ) and I get

Application-defined or object-defined error

Thanks for your help so far but I'm still lost in the wilderness on this one.

Ok, not sure why this works now... but

My references are:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.7 Library

and the following is my code:

    Dim tmp As Variant
    Dim tmprs As Variant
    Set tmprs = [DataFabric subform].Form.Recordset
    Set tmp = [DataFabric subform].Form.Controls

    If Not tmprs.BOF Then
        tmprs.MoveFirst
    End If
    While Not tmprs.EOF

        For Each tmpcontrol In tmp
            If tmpcontrol.name = "EstimatedCost" Then
                totalcost = totalcost + tmpcontrol.Value
            End If
        Next tmpcontrol

        tmprs.MoveNext
    Wend
    tmprs.MoveFirst


I'm not sure why setting the variables to specifically DAO or ADODB recordset objects causes errors but I need to get on with other aspects of this project so I'm just happy it works.

try

Dim tmprs As ADODB.Recordset

Do not use variant unless you want a variant. Here you want a recordset.




Sal

I get a "Type Mismatch" error everytime I do this.

My references are

Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.7 Library

Thanks anyways.