Generate set number of records, print the records, and mark them as printed.

Here is what I want to do, though I am not sure it is possible.

I have a table with 4 fields:
      ID Number (Auto Number Field)
      Printed (Yes/No Check Box)
      Date Printed (Date Field)
      Printed by (Short Text Field)

The person would open up a form, select their name from a drop down (for the Printed By field) and then click a print button.  The print button would ask how many labels to print and then generate that number of records in the table, it would also print a report based on those records, and lastly it would mark those records as printed and the date they were printed.

I think the printing part would be easy because all I would have to do is have it print any records not marked as printed (single user).

I guess my question is how to create a button that could generate the records (if this is even possible) and then do the printing all at once.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You typically print a Report, so you'd first have to insure your report has the correct number of records, and then you simply print it.

So create a report that shows the correct data (Access has a wizard to help you create a report to print labels) and then "print" it like this:

DoCmd.OpenReport "Your Report Name", acViewNormal

If you need to filter that report for a specific number of records, you could use the WHERE argument of the OpenReport method:

DoCmd.OpenReport "Your Report Name", acViewNormal, , "SomeField<" & TheNumberOfRecords

Of course, I'm just guessing at your data structure. We'd need to know more about that before we could provide further help.
Dale FyeOwner, Developing Solutions LLCCommented:
I'm a bit confused about what you meant by:

"The print button would ask how many labels to print and then generate that number of records in the table"

It sounds like these records don't currently exist at run time?  What do these records consist of?
pmfjoeAuthor Commented:
No the records would not exist that is my question is there a way to generate a set number of records at run time?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Dale FyeOwner, Developing Solutions LLCCommented:
Well, if the records have no data in them, what are they for?

I use a table (tbl_Numbers) with one field (lngNumber) and 10 records (the values 0-9).  With this table, I can create a list of numbers from 0 to N.  If your number will always be under 100 labels, then you could use:

SELECT Tens.lngNumber * 10 + Ones.lngNumber as LabelNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

Save this as qry_Label_Numbers

Then use Scott's recommendation to create a label report based on this query.  And use the Where condition argument of the OpenReport method (as Scott recommended) to restrict the number of labels to some number.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As Dale has pointed out, you cannot print data that does not exist, so you'll need to create some sort of dataset (i.e. table) that you can use when printing your labels. How you do that depends on exactly what will be on the labels.

I'm not sure what your table structure you listed in the question has to do with your labels. Are you trying to print data from that table? If so, explain a bit more and we'll try to help further.
pmfjoeAuthor Commented:
So I guess the question really is can you create a set number of records using a script.  The each record would only contain a autonumber field (the label barcode), a yes/no has been printed box, a date box of when they were printed, and a text box of who printed them.

The autonumber field would be generated by Access when the record was created as it would be a primary key field.
The Yes/No has been printed box would be assigned once the report was printed.
The Date box and the text box would be assigned from unbound fields in the form where the print button would be located.

The main question is how could you generate a set number of records using a script attached to a button?  Or is this just not possible in MS Access?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you'd insert records into your table, and then print the report?

If so, this would insert the records:

Dim i As Integer
Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM YourTable WHERE 1=0")

For i = 0 to NumberOfRecords
  rst.Fields("DateField") = SomeDateValue
  rst.Fields("TextField") = SomeFieldValue
Next i

'/ now print the report
DoCmd.OpenReport "YourReport", acViewNormal, , "Printed = False"
'/ update all records to show Printed
Currentdb.Execute("UPDATE YourTable SET Printed=True")

That's quite generic, but then you've really not given us a lot to go on. If you could give more information on the data and such, we could provide more focused insight.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pmfjoeAuthor Commented:
The first part was exactly what I needed to get me started.  Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.