By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,992 Members | 934 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,992 IT Pros & Developers. It's quick & easy.

VBA - duplicating record sets based off 1 field quantity and 1 field reference?

P: 1
First off - I want to thank all who respond / try to help out and even those who took a moment to read over my question.

Please be patient as I am -very- new to VB in general and unfortunately I've only had the chance to pick up pieces here-and-there. So keep that in mind when reading my inquiry as I am liable to misuse terminology.

My question, although lengthy, is likely quite basic but I can't seem to find a solution and yes I have searched around.

I am required to print off a report, using Access 2000, when ever material is requested from our customer. The report itself is more so a label that we stick to each piece of material. (Prints out on single 4x1 label via Zebra Printers)

I have mutiple fields in the form for data entry but my main focus is the 'quantity' field.

The problem comes when the report pulls the information from the form. If 1 piece of material is requested that is fine but when several pieces of material is requested I run into a dilemma.

For example; If 1 piece of 'x' is requested I would place 1 in the quantity field then enter the remaining information and click print which would produce 1 label. If 10 pieces of part 'x' is requested then I place a 10 in the quantity field -not- enter the part 10 times into the form. Obviously I could just print 10 copies but unfortunately there will also be requests for parts 'y' and 'z' with different quantity variances.

I would like to add some code to the 'On Click' procedure so access will reference the quantity field (txtQuantity) and create that many labels per piece of material since 1 is required for each individual part. It would also likely require a reference to the Part number (txtPart) field to differentiate which record to duplicate/create another label for with quantities greater than 1, correct?

My only other piece of information I can provide that may change the suggested code is how the form works in itself. When you first enter the form from the main menu there is only 1 record set to enter information into. The moment you begin typing information into the fields another record set is created to enter the info for the next piece of material. When you click on print it's set to auto-save the records and I 'believe' it deletes any set with a null value. (so we don't generate blank labels)

This DB was created prior to my entering the position and as posted earlier I am very wet behind the ears with VBA (please keep that in mind when posting a response) and semi confident in access itself. I am starting a VB course next month but until then I am forced to 'wing it' and hope I can find answers on my own and hope others can give me a hand in the process. I am not looking for a pasted solution I would definitely like an explanation of the code as I am trying my best to learn in the process.

Thanks again to anyone who can help!
Apr 12 '07 #1
Share this Question
Share on Google+
2 Replies


Denburt
Expert 100+
P: 1,356
Lets start with the form you use to call the report, would you please show us the code for your 'On Click' procedure. I sounds like you simply need a loop statement to cycle through and print. I just recently created something very similar so lets see what your code looks like.
Apr 20 '07 #2

NeoPa
Expert Mod 15k+
P: 31,419
In pseudo code :
  1. Have a table (We'll call it tblTemp) to store the data required to run the report.
    1. Clear the old data from [tblTemp].
    2. Run through the source table and process each record.
    3. For each record loop through [Copies] times and add a record into [tblTemp] in each iteration of the loop
  2. Run your report which is driven from [tblTemp].
Apr 23 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.