Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Looping through the records on a form 2

Status
Not open for further replies.

SkyHigh

Technical User
May 30, 2002
309
CA
Hi Folks

What I need to do is to open a form using a command button, then loop through the records on form till EOF, here are the steps :

1) click on a button to open the form ( which is on an
utility form )
2) Start with the first recrod on the form
3) Do some stuff like create PDF report and mail it ( for
which I already have the code - thanks to the forum )
The code generates PDF report based on the data on the
form for each record and email it to the address on the
form.
4) Then move to the next record and do the same stuff again
for the data in the next record

Loop until the last record and do the stuff

5) when done, display a msg saying job done.


Can someone please write me the VBA code.

Thanks very much for your time.

Rgds
Brenda
 
Hi Brenda,

You can loop through the records in a recordset with the following code. This code assumes that your Form is based on a recordset, which is the case?

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName", dbOpenDynaset, dbReadOnly)

While Not rs.EOF
Insert your Create PDF report and mail code here.
rs.MoveNext
Wend

MsgBox "Job Done!", vbInformation + vbOKOnly


Use DoCmd.OpenForm "formname"... to open the form. Please check out the Help in Access for the various parameters that you can use with the OpenForm Method.

Hope this helps.




dz
dzaccess@yahoo.com
 
Thanks dz, for your response, my form is actually based on a query, so how can I implement it, is there a Form specific logic/loop that I can run to move the records on the form till the last record.

Thanks for your help again.

Brenda
 
Brenda,

What is the purpose of the Form? You didn't describe how the user interacts with the Form, so I can't tell what event might be used to run the code that loops through the records. If the Form has a button that the user presses to create the PDF mail report, you could include the code in the On Click event procedure for that button. You could also include the code in the On Open or On Load event of the Form if you think that might be a better place for it. I generally only include code in the On Open or On Load event that is used to set up the Form. If that is the purpose of looping through the records, it might be a good place for the code. However, if you put the code in the Open or Load event, I don't really understand why you want to display a message that the job is done. Would that imply that you are done with the Form and would close it? If so, you wouldn't be using the Form as an interface to the user.

Best regards,


dz
dzaccess@yahoo.com
 
Hi dz

I have a form which is opened by a button on another form and in the form_open event I want to put the code, I tried the following code but its throwing an error once it gets to the last record :

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

DoCmd.GoToRecord , , acFirst

While Not acLast
DoCmd.GoToRecord , , acNext
' Code for creating PDFs
' Code from emailing PDFs
Wend

MsgBox "Job Done!", vbInformation + vbOKOnly

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Description
Resume Exit_Form_Open
End Sub

If you can recode it for me, I will be really grateful.

Thanks again
Brenda

 
Hi dz

Here is the full code, I haven't tested the email part of it yet, but I hope it sends emails silently without me pressing ok etc :

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Dim i As Integer

DoCmd.GoToRecord , , acFirst

While Not acLast
DoCmd.GoToRecord , , acNext


Call SaveReportAsPDF("rptInvoiceBatch", "C:\billing\invoice-" & [Forms]![frmBuldInvEmail]![invoice_no] & "-" & [Forms]![frmBuldInvEmail]![ext_id] & ".pdf")

With MailOutLook
.To = [Forms]![frmBuldInvEmail]!
.CC = [Forms]![frmBuldInvEmail]![email2] & ";" & [Forms]![frmBuldInvEmail]![email3]
.Subject = "Monthly Invoice"
.HTMLBody = ""
.Attachments.Add "C:\billing\invoice-" & [Forms]![frmBuldInvEmail]![invoice_no] & "-" & [Forms]![frmBuldInvEmail]![ext_id] & ".pdf"
.Display
End With

Wend
MsgBox "Job Done!", vbInformation + vbOKOnly

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Description
Resume Exit_Form_Open
End Sub



Thanks alot
Brenda
 
I'll try to help you, but first a few questions:

1. Is the name of the form that contains the code in your last message "frmBuldInvEmail"?

2. What purpose does the Form have other than the code in the On Open event?

3. What is the error that you get on the last record? I would guess that it has something to do with your trying to go to a record that is past the end of file. The code that I gave you would avoid that problem. Just curious why you didn't use it.

Thanks,



dz
dzaccess@yahoo.com
 
Hi

Here are answeres to your questions:

1) Correct
2) The only purpose of the form is whats in the code.
3) Correct again, ok please explain how can I use your code, this form is based on a query can I give query name in place of table name ? what else I need to do on the form.

Your help will be greatly appreciated.

Thanks
Brenda
 
The reason that I asked my first question is because you don't need to refer to the form name if the data is on the current form. You normally use that syntax to refer to data on an open form that does not have the focus. When you use a Recordset, refer to the data with RecordsetName!fieldname as shown in my example below. If the fieldname is not in the Recordset, you will get an error.

The reason that I asked the second question is because you normally only use a Form to provide the user with an interface to enter something. Instead of using a Form, why not just move all your code to the OnClick event of the button that opens the Form where your code now resides? I really see no use for that Form if it is only being used as a container for your code.

To follow up on your answer to my third question, you can use a Query name or SQL statement in place of "YourTableName" in the Set statement.

Set rs = db.OpenRecordset("YourQueryName", dbOpenDynaset, dbReadOnly)

OR

Set rs = db.OpenRecordset("SQLStatement", dbOpenDynaset, dbReadOnly)

I am not familiar with code to interface with Microsoft Outlook, but assuming that it is correct, try this:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Dim i As Integer

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("YourQuery", dbOpenDynaset, dbReadOnly)

While Not rs.EOF
Call SaveReportAsPDF "rptInvoiceBatch", "C:\billing\invoice-" & rs!invoice_no & "-" & rs!ext_id & ".pdf")

With MailOutLook
.To = rs!email
.CC = rs!email2 & ";" & rs!email3
.Subject = "Monthly Invoice"
.HTMLBody = ""
.Attachments.Add "C:\billing\invoice-" & rs!invoice_no & "-" & rs!ext_id & ".pdf"
.Display
End With

rs.MoveNext
Wend

rs.Close
db.Close

MsgBox "Job Done!", vbInformation + vbOKOnly

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Description
Resume Exit_Form_Open
End Sub




dz
dzaccess@yahoo.com
 
Thanks so much dz, here's your star!
Brenda
 
Hi dz

One more question for you, if you can help, instead of using form I would like to use MS ProgressBar Control to show the progress, if you are familiar with this can you please add the necessary code to your above code.

Thanks again for all your help
Rgds
Brenda
 
Thanks, Brenda. I haven't used the MS ProgressBar Control, but gather that you would do something like this:

ProgressBar.setMinimum 1
ProgressBar.setMax rs.RecordCount
ProgressBar.setStep 1

While Not rs.EOF
Call SaveReportAsPDF "rptInvoiceBatch", "C:\billing\invoice-" & rs!invoice_no & "-" & rs!ext_id & ".pdf")

With MailOutLook
.To = rs!email
.CC = rs!email2 & ";" & rs!email3
.Subject = "Monthly Invoice"
.HTMLBody = ""
.Attachments.Add "C:\billing\invoice-" & rs!invoice_no & "-" & rs!ext_id & ".pdf"
.Display
End With

ProgressBar.increment

rs.MoveNext
Wend

You might find the following article useful.


Did you get the ProgressBar control in the Windows SDK? I'd be curious if you get it to work. Unless you have a very large table or are doing this over a slow network connection, the ProgressBar will probably move too fast to be useful. For example, I have used code similar to what I gave you to iterate through several thousand records, and it only took a few seconds to finish. I am not familiar with the stuff that you are doing with Outlook, and maybe it's more time consuming. Do you have any idea how long it will take to process your records?






dz
dzaccess@yahoo.com
 
Hi dz

I am having problems with the above approach, my report is created based on an id field on the form and the above code does not move the records on the form and so it creates one customer's report as many times as it loops, how can we make it to work correctly.

Thanks and regards
Brenda
 
I'm not following you, Brenda. Please post the Set statement where you open the Recordset and I'll try to figure out why it isn't working.

Thanks,



dz
dzaccess@yahoo.com
 
Hi dz

I am sorry I am not being very clear, problem is I am very new in VBA or windows programing, I know I am missing something.

You said set statement and I think thats what I am missing, my form is already based on that query and I don't know how to pupulate the form just using the VBA code.

How can I pupulate the form's controls from the result set returned by your VBA code.

Once I know that that shuld solve the problem.

Thanks again dz and accept my apologies for being so stupid:)

Rgds
Brenda
 
What controls are on the Form?

The other day you said that you were only using the Form to execute the code in your message, which only creates reports and sends mail. Even if your Form contains controls that display data from a Query, the Set statement would not interfere with that. The Set rs = db.OpenRecordset(&quot;YourQuery&quot;, dbOpenDynaset, dbReadOnly) statement creates a recordset that you can use to access your data. You reference the data with rs!<fieldname>, where <fieldname> is any field in the record set. This doesn't affect the data that is displayed on the form. It is just another way to reference it.

What is stored in the Record Source of your Form? Does it have an SQL statement or a Query name?

By the way, the only stupid question is the one that isn't asked. It looks like you are making good progress with your code.

dz
dzaccess@yahoo.com
 
Hi dz

All the controls on the form are txt boxes and the record source is a query, its the same query that I am refering in your code above.

I think I can map the record set returned by the above code with the fields on the form like:

[Forms]![frmBuldInvEmail]![ext_id] = rs!ext_id
[Forms]![frmBuldInvEmail]! = rs!email

and remove the query from record source property, that should work and move the records on the form as well ?

Thanks
Brenda
 
Brenda,

No, that isn't correct. Perhaps a quick explanation of what a Recordset is and how it relates to the data on your form would help.

When you create a Form based on a Query, Access automatically populates the bound fields on your form when you move the record pointer. You can move to the next record by pressing one of the navigation buttons on your form, for example.

When you create a Recordset with Set rs = db.OpenRecordset(&quot;YourQuery&quot;...)

you are effectively creating a COPY of the data that is specified in &quot;YourQuery&quot;. The data in &quot;YourQuery&quot; can be the same as the Query stored in the Form's Recordsource, but doesn't necessarily have to be. The Recordsource for the Form that is created by storing a Query or SQL statement in the Form's Recordsource is completely independent from the Recordsource created by using the Set rs = db.OpenRecordset statement. In essence, you have two Recordsets.

With that background, the reason that I suggested that you create a Recordset with Set rs = db.OpenRecordset is because it provides an easy way to loop through the records in a table, which is what you wanted to do. The Recordset for the Form has nothing to do with rs, nor should the data on your Form be affected by moving the record pointer in rs. Consequently, there is no reason to assign the value of controls on the form to those in the rs Recordset. Just let Access keep track of the data on the Form with the Recordset stored in the Form's Recordset Property.

Are we on the same page now?

Regards,


dz
dzaccess@yahoo.com
 
One more note, Brenda...You could populate the fields on your Form with the assignment statements in your code; however that would unnecessarily complicate your code. Doing this would require you to manually move the Form's record pointer and populate the fields each time the record is changed. Access does this for you behind the scenes when you use the Form's Recordsource Property and Navigation bar. It's best not to mix up the two record sources as they each serve their own purpose. I hope that this helps.

dz
dzaccess@yahoo.com
 
Hi dz, thank for explaining however in my situation I do need to move the records on the form as well, if you can tell me how using VBA, that will be great.

Thanks again
Brenda
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top