CODEWITHSUNDEEP
vba
Paul John
Paul John

Reputation: 21

how to loop through the records of a subform, VBA Access 2010

Here's my question: i have a subform inside a main form. The main form contains the customer number, and the subform contains the order for that customer. So one customer may have multiple orders. I want to create a buttom which when clicked moves to the next order for that customer. I have no issue doing this with the code:

 Private Sub next_comp_Click()
 [Forms]![Customer]![Order Subform].SetFocus
 DoCmd.GoToRecord , , acNext
  end sub

where I have issues is when I get to the last order for that customer. I will like it to loop back to the first order. To do this i used this code:

Private sub next_comp_click()
[Forms]![Customer]![Compressor Subform].SetFocus
Dim dbcustomer As Database
Dim rstorder As Recordset

Set dbcustomer = CurrentDb
Set rstorder= dbcustomer.OpenRecordset("orders", dbOpenSnapshot)
  With rstEmployees
    If .AbsolutePosition = - 1 Then
    MsgBox (.RecordCount)
    'DoCmd.GoToRecord , , acFirst
    Else
    DoCmd.GoToRecord , , acNext
    End If
  End With
 end sub

I used the MsgBox function to kind of debug to figure what record I am on (ultimately I want it to loop back to the first record as shown in the commented out code), but for some reason, it tells me i am always on the first record, i.e. it tells me the RecordCount is 1. This is not supposed to be so because most of the records I am working with have multiple orders. If I change the condition to something like If .AbsolutePosition = 3, it moves the records to the next until i get to the last or empty record and if i click next from there, it gives me the run-time error '2105' you cannot go to the specified record.

I think the issue is the .AbsolutePositon property is not looking at the right form or table, but any answers will be much appreciated.

Thank you in advance.

Upvotes: 2

Views: 13986

Answers (2)

Andrew
Andrew

Reputation: 21

This is what I do to loop to the First record after hitting the last record. Put it in VBA. Just do the reverse if using a previous button.

 If Me.CurrentRecord = Me.Recordset.RecordCount Then
       DoCmd.GoToRecord , , acFirst
   Else
       DoCmd.GoToRecord , , acNext
   End If

Upvotes: 2

ObieMD5
ObieMD5

Reputation: 2657

You do not even need that recordset stuff if your forms are bounded. Just use the following code in the button:

Private sub next_comp_click()
   [Forms]![Customer]![Order Subform].SetFocus
   If [Forms]![Customer]![Order Subform].Form.CurrentRecord >= [Forms]![Customer]![Order Subform].Form.Recordset.RecordCount Then
       DoCmd.GoToRecord , , acFirst
   Else
       DoCmd.GoToRecord , , acNext
   End If
End sub

I built an access database to replicate this just to make sure the code was exact. You should be able to copy and paste this and it will work.

Upvotes: 3

Related Questions