Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 16th, 2004, 08:57 AM
V V is offline
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Looping through subform records

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.

Reply With Quote
  #2 (permalink)  
Old April 16th, 2004, 01:49 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
jwelz@hotmail.com
Reply With Quote
  #3 (permalink)  
Old April 16th, 2004, 03:24 PM
V V is offline
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?


Reply With Quote
  #4 (permalink)  
Old April 16th, 2004, 03:41 PM
V V is offline
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 ).

Reply With Quote
  #5 (permalink)  
Old April 16th, 2004, 06:34 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
jwelz@hotmail.com
Reply With Quote
  #6 (permalink)  
Old April 19th, 2004, 08:20 AM
V V is offline
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #7 (permalink)  
Old April 19th, 2004, 09:12 AM
V V is offline
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #8 (permalink)  
Old April 19th, 2004, 08:59 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

try

Dim tmprs As ADODB.Recordset

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




Sal
Reply With Quote
  #9 (permalink)  
Old April 20th, 2004, 08:10 AM
V V is offline
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
skipping records while looping stealthdevil Access VBA 3 October 26th, 2006 10:22 AM
Looping through all records in excel? dhl365 Pro VB 6 1 July 26th, 2005 03:39 PM
records in subform stoneman Access 1 October 27th, 2003 11:46 AM
where is cursor after looping through records? monstermash Classic ASP Basics 7 July 22nd, 2003 03:55 AM
looping through return records in sproc jtyson SQL Server ASP 5 July 14th, 2003 10:16 AM



All times are GMT -4. The time now is 01:05 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.