What's wrong with this loop statement!?

[menasche]

On a subform I have a button that has code for its click event. This button reappears for each record. (btnPrintInvoices). There are 4 other forms just like this, these too will be need to be called at the same time (the loop)

I also have a public function for this event and for each other button one of the other 4 subrforms.
Public Function fTriggerInvoiceBtn()
Call btnPrintInvoices_Click
End Function

On the main form, I have button that needs to 'trigger' each of these buttons as many times as there are records on each subform. The code I currently have is:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set rs = db.OpenRecordset(Me![Invoices_tbl subform].RecordSource, dbOpenDynaset) 'my subform
With rs
Call btnPrintInvoices_Click ' the button I am calling - this does not work so I tried...
Me![Invoices_tbl subform].Form.fTriggerInvoiceBtn ' this does not work either
Do Until .EOF
.MoveNext
Loop
End With

What is wrong with this loop?
What needs to change in the above scenario?

I am sorry I am so thick! I am rather new to programming and your help and time is extremely valuable.

 

[fernin8r]

Is btnPrintInvoices_Click a Public Function?

Also...The loop isn't doing anything.

Do Until .EOF
--------------------> here is were you would put the command that needs to be looped

.MoveNext
Loop

 

[menasche]

Yes it is...should it not be?

 

[menasche]

I am using Access 2000 for the following code...read the commented lines

Dim rs As DAO.Recordset ' is it DAO or something else?
Dim db As DAO.Database 'as above
Set rs = db.OpenRecordset(Me![Invoices_tbl subform].RecordSource, dbOpenDynaset)
'my subform, but I keep getting error messages. I have also tried using the following, but it too errors:
'Forms![MAIN_FRM]![Invoices_tbl subform]
Do Until .EOF
With rs
.MoveLast ' do I need this line to populate the record set?
.MoveFirst 'do I need this line to populate the record set?
Me![Invoices_tbl subform].Form.fTriggerInvoiceBtn ' this a public function that calls a button on the subform
.MoveNext
Loop
End With

 

[filo65]

I think the problem lies on where you stored your routine.

If btnPrintInvoices_Click is in a form module I guess it will not work.

Putting it in a module should solve your problem.


filo65

 

[menasche]

Ugh...

Here is the code now...the button works great, but I get a compile error on the Do Until .Eof line. I do have the DAO reference installed.:


Dim rs As DAO.Recordset '
Dim db As DAO.Database '
Set rs = db.OpenRecordset(Me![Invoices_tbl_subform].RecordSource, dbOpenDynaset)
'Me![Invoices_tbl_subform].Form.Recordsource ' or this
Do Until .EOF
With rs
Me![Invoices_tbl_subform].Form.fTriggerInvoiceBtn ' this a public function that calls a button on the subform
.MoveNext
End With
Loop

 

[KenHigg]

Dim rs As DAO.Recordset '
Dim db As DAO.Database '
Set rs = db.OpenRecordset(Me![Invoices_tbl_subform].RecordSource, dbOpenDynaset)
'Me![Invoices_tbl_subform].Form.Recordsource ' or this
With rs
Do Until .EOF
Me![Invoices_tbl_subform].Form.fTriggerInvoiceBtn ' this a public function that calls a button on the subform
.MoveNext
End With
Loop

 

[menasche]

I did that, then I had to move the Loop before the end with...NOW, I get runtime error 424 (object req) on the Set rs line:

Set rs = db.OpenRecordset(Me![Invoices_tbl_subform].RecordSource, dbOpenDynaset)

I tried

Set rs = 'Me![Invoices_tbl_subform].Form.Recordsource

Same...Ahh!!!

HEre is the code now...

im rs As DAO.Recordset '
Dim db As DAO.Database '
Set rs = Me![Invoices_tbl_subform].Form.RecordSource
'db.OpenRecordset(Me![Invoices_tbl_subform].RecordSource, dbOpenDynaset)
' ' or this
With rs
Do Until .EOF

Me![Invoices_tbl_subform].Form.fTriggerInvoiceBtn ' this a public function that calls a button on the subform
.MoveNext
Loop
End With

 

[Pat Hartman]

You have created a complicated solution when a simple one would do.

You need to base the invoice report on a query and the query should have criteria that references the form so that the recordset is properly filtered.

If necessary, modify the report to add grouping and page breaks.

You only need to run the report once.