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.
pmfjoeAsked:
Who is Participating?
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.
0
Dale FyeCommented:
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?
0
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
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.
0
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.
0
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?
0
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.AddNew
  rst.Fields("DateField") = SomeDateValue
  rst.Fields("TextField") = SomeFieldValue
  rst.Update
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.
0

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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.