Printing of single and multiple records

[BrianM2]

I operate a database which is used for recording the inspection of instrumentation on a chemical plant. The printing of inspection sheets and the results can involve hundreds of records and is an ideal candidate for automation. I decided to give it a go.

Generated a much reduced and simplified test database. Recorded a macro to form a base, converted it to VBA - this is shown below - and then set out to make it print a single record prior to embarking on a loop addition to print a complete query contents.

Didn't get to square one. No matter what I tried I could not get anything other than a print of all records.

The original, untouched module is as follows:

----------------------------------------------------------------------------------------------------------------------------------------------------------
Option Compare Database

'------------------------------------------------------------
' Print_first_record
'
'------------------------------------------------------------
Function Print_first_record()

'DoCmd.OpenTable "Instr", acNormal, acEdit (commented out as unnecessary)

DoCmd.OpenQuery "Test_Haz_area_instr_list_rep_limited_q", acNormal, acEdit
DoCmd.GoToRecord acQuery, "Test_Haz_area_instr_list_rep_limited_q", acFirst
DoCmd.OpenReport "Test_Insp_sht_instrument_d_rep", acPreview, "", ""

End Function
----------------------------------------------------------------------------------------------------------------------------------------------------------

I would greatly appreciate it if someone could show me

a) How to modify this code to make it print the first record
and
b) How to further modify it to make it print all records in the query

Upon request I will gladly email the simplified database if this would assist.


Brian

 

[boblarson]

I'm attaching a sample that I created for you so hopefully that will help.

 

[BrianM2]

Bob

Thank you for your reply. It has been helpful and has given me some ideas.

I need to print each record individually in all cases so that I end up with a set of sheets for each instrument. This suggests that I need to use the single print button approach for the "print all" case with a do loop. Will give it a go and see what happens.

To give you more of an idea of what I am doing I've attached a file of the test database. This includes a query which is not necessary in this version of the database but is essential in the full version where information is gathered from two databases and several tables.

Don't be surprised if you hear from me again as this is my first attempt at VBA!

Brian

 

[BrianM2]

Bob

Your advice of a couple of days ago together with my response refer.

Having seen how you used the form for controlling printing I've pulled an existing form from my main database. The revised test database is attached. I've added a couple of command buttons and used the button wizard to write the code. However I can not figure out how to get a single record to preview. In your code I can see that it is the SQL WHERE clause "[grantee_id]=" & Me.txtgrantee_id which is used for this purpose but I don't understand how it works. I've tried a range of options but none work

Can you (or some other helpful person!) please advise?

Brian