Printing multiple copies of a record in the same report

[klarae99]

Hi, I am using Access 2003 to create an inventory database. Within my database I have a table for transactions that records the # recieved for an Item # by the transaction date. I would like to create a report that would print out price stickers for the inventory entered on each day. I know I can create a queary to find the items that have transactions on a certain date but each item is only listed once in my database. What I would like to do is have Access repeat each detail the number of times that I have entered in the # recieved column.

Example: On 10/9 I recieved 6 Sweaters item #456; I'm sellling them for $25;
2 T-Shirts #1123 for $10;
and 1 Vest # 753 for $15.

When I print the price stickers for 10/9 I would like
6 price stickers with #456 $25;
2 with #1123 $10;
and 1 with #753 $15.
from the same Price Sticker Report.

Is this possible? Any Advice would be appreciated.

[nico5038]

Hi, I am using Access 2003 to create an inventory database. Within my database I have a table for transactions that records the # recieved for an Item # by the transaction date. I would like to create a report that would print out price stickers for the inventory entered on each day. I know I can create a queary to find the items that have transactions on a certain date but each item is only listed once in my database. What I would like to do is have Access repeat each detail the number of times that I have entered in the # recieved column.

Example: On 10/9 I recieved 6 Sweaters item #456; I'm sellling them for $25;
2 T-Shirts #1123 for $10;
and 1 Vest # 753 for $15.

When I print the price stickers for 10/9 I would like
6 price stickers with #456 $25;
2 with #1123 $10;
and 1 with #753 $15.
from the same Price Sticker Report.

Is this possible? Any Advice would be appreciated.

One option is to create a temp table with the correct number of records for pinting the stickers. This temp table can be created by a function that's accepting a date for selecting the transactions and filling the table.
Something like:

function fncCreateTempStickers (dtSelection as Date)
dim rs as DAO.recordset ' The requires "Microsoft DAO 3.##" to be set in 
Tools/References
dim intI as Integer
 
' remove old temp table and create fresh copy
currentdb.execute ("Drop Table tblTempStickers")
currentdb.execute ("CREATE TABLE tblTempStickers (ItemNo as TEXT, Price as TEXT)")
' set recordset to select only from passed date
set rs = currentdb.openrecordset("select * from tblTransactions where shippingdata = #" & dtSelection & "#")
if rs.eof and rs.bof then
   msgbox "No data for this date: " & dtSelection
   exit function
endif
while not rs.eof
    ' insert the needed number of rows
    for intI = 1 to rs![Item #]
       currentdb.execute ("INSERT INTO tblTempStickers (ItemNo, Price) VALUES ('" & rs!ItemNo & "','" & rs!Price & "')")
    next
    rs.movenext
wend
set rs = nothing
end function
 

Getting the idea ?

Nic;o)

[klarae99]

With my codeing skills, its all greek.

Where would I put this code, on a comman button click? I am assuming that I could put the date that I wanted into a text box on a form and use it as the select date of the temporary table. I am also assuming that you could create this table and output it to a report without any additional user interaction after selecting the date and clicking create. I am the most advanced user of access (which is why I am designing the database) though, as you can tell, I really don't know that much about how access works. With this skill level of users I want to try and have as little room for error (or the inadvertant deleate something very important without realizing its important) as humanly possible.

I realize the code you have provided is a rough guideline about what I should be doing to create the code, and as I already mentioned code in a new language for me. I know that you could fix it up all shiny and new for me but I would like to learn as much as I can about coding so that I don't have post every time I need to create code. I have included my understanding of the first 10 lines of code to see if I can follow what each line is trying to do. I am hoping that you will be able to confirm that I am understanding the purpose of each line of code, and to clarify what specific references mean.
****
line 1. tells access that we are using a function to create a table Temp Stickers and using the date to limit the enteries

line 2. I'm not sure what dim rs is but the rest looks like it is telling access that it will be stored as a record set.

line 3. and 4. I'm unsure what they do.

line 6 removes tells access to remove the old data and put in new data.

line 7 to create a table named tblTempStickers from the current database

line 8 to add to the table the fields x and y from the current database

line 9 limits the date to a past date (Would we be able to use todays date with this limit?)

line 10 is setting the parameters for the search? I don't understand the - shippingdata = #" & dtSelection & "#")
shippingdata = #" would that be letting access know that the date is a #? and what does dtSelection & "#" mean?
****
Also if you have any ideas about where I can find a resource for learning the basics of coding (as basic as docmd. means Do Command X and a list of possible Xs) I would appreceate it. Thanks for all your help with this!

[nico5038]

No thanks needed. Learning the coding will indeed require some training and almost all Access VBA books will provide valuable information and "How to's".

Another option is to look into the Helpfile (Press F1) when in the VBA mode and look for the sample code that comes with Access.

You'll find the "Recordset processing loop" there explained in detail with an example. My code indeed gives the crude structure, but all needed to make it work is to replace the table and fieldnames with the defined ones in your table definitions.
The code needs to be placed in a module (Just create a new one e.g. named "modSticker s") and paste it there.
Then check the Helpfile how to test code using the F8 (single step mode) and setting "Break points". This way you can see step by step what the code does and check or it's like the way you expect it to work.

So some study to perform and let me know when you have placed the code in a module and that you have studied the mentioned things from the Helpfile. Then we'll step through the code and make it work to generate the temp table. The "sticker report" after that will be a piece of cake :-)

Nic;o)

[klarae99]

Thanks so much for your advice on where to look for code information, I had looked in Access help but not in the VBA mode. I will work on creating a new module after looking through the help information especially focusing on Recordset processing loop. It may take me a while to get back to you; the office is going to be crazy with meetings today and tomorrow so I won't have alot of time to dedicate to aquireing new knowledge but once things slow down on Monday I should be able to focus back in. I will let you know when I have the module created, hopefully early next week. Thanks again and I'll talk to you soon.

[nico5038]

Take your time, learning to code took me several months back in the seventies :-)

I'm always please when people want to learn !

Nic;o)

[klarae99]

Nic;o)

I have quickly looked through the help menu in the VBA window, this is a good reference for me, and I will continue to use it, thanks for the tip.

I tried to lookup the Recordset Processing Loop in help and the closest things I could find were looping through code and looping For...Next Statements and For Each...Next Statements. I don't think this is what we would be using for this paticular situation and I couldn't find anything else, so I moved on to creating the new module. I copied and pasted your code into a new module that I saved as modStickers. I changed all the references that I could find linked to my database to the appropriate references, I hope.

I tried looking up in help the Test Code in Single Step Mode and could not find anything on that. So, I looked under Debug on the tool bar and my F8 function is called Step In, so I looked Step In up in help. All I found was that Step In was a way to debug code that was located in the debug feature of the toolbar. Much less help than I thought help would be. I then tried to just run the debug F8 stepping in to see if I could accomplish anything at all. I clicked on the item in the drop down menu, and I pressed F8, neither had any visible action that I could see.

I would appreciate your help on getting this code working within my database to create my pricestickers. I have been given a deadline for this project, this Friday. This is a much shorter window than I originally thought I would have on this database and I know that I will not be able to spend as much time experimenting with code as I would need to learn it well enough to write my own by then. I will continue learning as much as I can now and go back later to learn more, as I have more time.

I also should mention that I would like to include the vendor code on my price stickers as well as the item # and price (which is a calculated field from a query qryListPrice). When I posted the question originally I was just trying to figure out if the program had the ability to do what I wanted so I tried to simplify the question to its basics and left out some of the variables. I'm sure that this complicates matters further but what's life without a little challenge?

I don't know if it will be helpful to you or not but I have a list of all my tables and fields in another post (Viewing a calculated qury field on a form) that I am working on with FishVal to create some data entry forms. I may be modifying the tables slightly in response to Fish's advice but they should be minor changes.

I am look forward to your continuing guidance with this report and will await your next post!

[nico5038]

OK, lets start with "activating " the F8 as stepping through code is a valuable technique for understanding programming and debugging errors.

For the F8 to work you'll need first to create a breakpoint to intercept the code execution. A breakpoint is placed by clicking in the left ruler untill a round dot appears. You'll notice that only commandlines can be used to create a breakpoint.
When using F8 "from scratch" (without having a breakpoint set) almost the same mechanism works, the cursor needs to be on a command within a function to allow the F8 to work.

Nic;o)

[klarae99]

Ok, I have opened the module and clicked in the left 'ruler bar' (I don't have a ruler but there is a grey bar on the left where a ruler would be) in a command line. That line is now highlighted in a maroon color and there is a maroon dot in the bar. When I press F8 I still get no visable reaction, ditto for selecting Step Into from the debug menu. Am I in the right place doing the right thing?

[nico5038]

Strange, but I guess we have here the "trouble" that it won't work as no parameter has been specified.

Let's take the 100% sure way by isung the immediate window.
It shold be at the bottom (when not select it from the view menu).
There type:

call fncCreateTempStickers (#01/01/2007#)
 

and make sure that the breakdot is positioned on e.g.:

currentdb.execute ("Drop Table tblTempStickers")
 

Now the code should execute and the "drop table row" should turn yellow.
See what happens with the F8 stepping

Nic;o)