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

Printing multiple copies of a record in the same report

klarae99
P: 85
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.
Oct 9 '07 #1
Share this Question
Share on Google+
35 Replies


nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. function fncCreateTempStickers (dtSelection as Date)
  2. dim rs as DAO.recordset ' The requires "Microsoft DAO 3.##" to be set in 
  3. Tools/References
  4. dim intI as Integer
  5.  
  6. ' remove old temp table and create fresh copy
  7. currentdb.execute ("Drop Table tblTempStickers")
  8. currentdb.execute ("CREATE TABLE tblTempStickers (ItemNo as TEXT, Price as TEXT)")
  9. ' set recordset to select only from passed date
  10. set rs = currentdb.openrecordset("select * from tblTransactions where shippingdata = #" & dtSelection & "#")
  11. if rs.eof and rs.bof then
  12.    msgbox "No data for this date: " & dtSelection
  13.    exit function
  14. endif
  15. while not rs.eof
  16.     ' insert the needed number of rows
  17.     for intI = 1 to rs![Item #]
  18.        currentdb.execute ("INSERT INTO tblTempStickers (ItemNo, Price) VALUES ('" & rs!ItemNo & "','" & rs!Price & "')")
  19.     next
  20.     rs.movenext
  21. wend
  22. set rs = nothing
  23. end function
  24.  
Getting the idea ?

Nic;o)
Oct 10 '07 #2

klarae99
P: 85
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!
Oct 10 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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 "modStickers") 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)
Oct 10 '07 #4

klarae99
P: 85
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.
Oct 11 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 11 '07 #6

klarae99
P: 85
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!
Oct 15 '07 #7

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 15 '07 #8

klarae99
P: 85
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?
Oct 15 '07 #9

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. call fncCreateTempStickers (#01/01/2007#)
  2.  
and make sure that the breakdot is positioned on e.g.:
Expand|Select|Wrap|Line Numbers
  1. currentdb.execute ("Drop Table tblTempStickers")
  2.  
Now the code should execute and the "drop table row" should turn yellow.
See what happens with the F8 stepping

Nic;o)
Oct 15 '07 #10

klarae99
P: 85
I opened the Immediate window, entered the code you included in the last post. When I hit enter I got a Compile Error, Sintax Error message and the line Tools/References was highlighted in green. When I click the OK button the line Function fncCreateTempStickers(dtSelection As Date) was highlighted in yellow, with a yellow arrow in the 'ruler' bar.

I deleated "in Tools/References" from your description

' The requires "Microsoft DAO 3.##" to be set in Tools/References

and closed the module, when I reopened it and ran the immediate window again the yellow highlight moved to the 'drop table row' as you indicated it would in the previous post.
Oct 15 '07 #11

nico5038
Expert 2.5K+
P: 3,072
Hmm, I see the code tag did place "tools/references" on a new line, it was however part of the previous line...

Now try the F8 and see what happens when the code is executed line by line.

Nic;o)
Oct 15 '07 #12

klarae99
P: 85
When I run the F8 function the first thing it does is give me a Runtime Error '3376 message that tblTempStickers does not exist.

Should I create a blank table with that name? If so do I need to add any fields, etc.?
Oct 15 '07 #13

nico5038
Expert 2.5K+
P: 3,072
Yes, guess you can create the fields too, as they are the fields that are needed on the sticker. The table will be used for the StickerReport !

Nic;o)
Oct 15 '07 #14

klarae99
P: 85
Nic;o)

I have created tblTempStickers with the fields TempStickerID, Item, VendorCode and ListPrice. I went back to the Visual Basic Editor and ran the F8 debug feature. It moved through the first step and after highlighting the line:

CurrentDb.Execute ("CREATE TABLE tblTempStickers (Item as TEXT, ListPrice as TEXT)")

I recieved a runtime error '3292' Syntax Error in field definition error. I then added the phrase- VendorCode as TEXT - and recieved the same error. When I clicked on help I recieved the following information:

Syntax error in field definition. (Error 3292)
A syntax error occurred in the field definition of a CREATE TABLE or ALTER TABLE statement.

Possible causes:

A reserved word or argument name is misspelled or missing.
Punctuation is incorrect.

I know that something is happening because when I go back to review the field names that I had created in the table, the table is missing. I recreated the table, double checked the field names against the field names in the code and then ran the F8 debug, it stoped at the same point and gave me the same error message.

I have copied the code as it is now and pasted it below (so you could review where I had changed field names to match my tables, etc.) I appreciate all your help with this!




Expand|Select|Wrap|Line Numbers
  1. Function fncCreateTempStickers(dtSelection As Date)
  2. Dim rs As DAO.Recordset ' The requires "Microsoft DAO 3.##" to be set
  3. Dim intI As Integer
  4.  
  5. ' remove old temp table and create fresh copy
  6. CurrentDb.Execute ("Drop Table tblTempStickers")
  7. CurrentDb.Execute ("CREATE TABLE tblTempStickers (Item as TEXT, ListPrice as TEXT, VendorCode as TEXT)")
  8. ' set recordset to select only from passed date
  9. Set rs = CurrentDb.OpenRecordset("select * from tblTransactions where shippingdata = #" & dtSelection & "#")
  10. If rs.EOF And rs.BOF Then
  11.    MsgBox "No data for this date: " & dtSelection
  12.    Exit Function
  13. End If
  14. While Not rs.EOF
  15.     ' insert the needed number of rows
  16.     For intI = 1 To rs![Item]
  17.        CurrentDb.Execute ("INSERT INTO tblTempStickers (Item, ListPrice) VALUES ('" & rs!Item & "','" & rs!ListPrice & "')")
  18.     Next
  19.     rs.MoveNext
  20. Wend
  21. Set rs = Nothing
  22. End Function
Oct 16 '07 #15

nico5038
Expert 2.5K+
P: 3,072
Sorry, used "AS" out of "habit", try:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute ("CREATE TABLE tblTempStickers (Items TEXT, ListPrice TEXT, VendorCode TEXT)")
  2.  
Nic;o)
Oct 16 '07 #16

klarae99
P: 85
Nic;o)

I fixed the as issue and continued on. I was able to 'fix' one issue on line:

Set rs = CurrentDb.OpenRecordset("select * from tblTrans where shippingdata = #" & dtSelection & "#")

At this point I got a runtime error 3061 - too few parameters. Expected 1. So I switched shippingdata to Date (the name of my field in tblTrans that has the date information we wanted.

I then proceeded through until I got a message box that told me there was no data for that date. I changed the date in the Immediate window to a date that I knew we had recieved murchandise and then I was able to move on to

For intI = 1 To rs![Item]

Where I again recieved an error. I changed Item to Recieved (figuring that this clause was telling the code to use the data in the field as the number of times to repeat and the Recieved field is where that information would be.)

I continued on through the Steps until I got to the line:

CurrentDb.Execute ("INSERT INTO tblTempStickers (Item, ListPrice, VendorCode) VALUES ('" & rs!Item & "','" & rs!ListPrice & "','" & rs!VendorCode & "')")

Where I got a runtime error 3265, Item not found in this collection. And Now I am stuck again. If you could review what I did and then let me know where to go next I would appreciate it. Thanks for your help!!
Oct 16 '07 #17

nico5038
Expert 2.5K+
P: 3,072
Well, you did a lot, see it's fun :-)

>So I switched shippingdata to Date (the name of my field in tblTrans that has the date information we wanted.
Best NOT to use Date as a fieldname as it's also a function and thus a "reserved word". Change the name in the table into TransDate, ShippingDate, etc. as long as it's not Date it's "safe".

>I changed the date in the Immediate window to a date that I knew we had recieved murchandise and then I was able to move on to
Personally I use a combobox based on a "Select distinct ShippingDate from tblX", thus the date will always return data when used in the query....

>I changed Item to Recieved (figuring that this clause was telling the code to use the data in the field as the number of times to repeat and the Recieved field is where that information would be.)
Well done, learning fast :-)

> CurrentDb.Execute ("INSERT INTO tblTempStickers (Item, ListPrice, VendorCode) VALUES ('" & rs!Item & "','" & rs!ListPrice & "','" & rs!VendorCode & "')")
Where I got a runtime error 3265, Item not found in this collection. And Now I am stuck again.
This implies that one of the fields (RS!Item, rs!ListPrice or rs!VendorCode) isn't found. Hover with the mousepointer over these fields and check when it show the error message. Then check or the recordset (rs) did retrieve that field.

An alternative might be to type a questionmark (?) in the immediate window followed by the string like:
?"INSERT INTO tblTempStickers (Item, ListPrice, VendorCode) VALUES ('" & rs!Item & "','" & rs!ListPrice & "','" & rs!VendorCode & "')"
and press [Enter] to see what string is build.
Next Copy/Paste that resulting string into an "empty" query's SQL and see what happens when executing the query.

Nic;o)
Oct 16 '07 #18

klarae99
P: 85
Nic;0)

Thanks for your response, sorry it took me a while to get back to you.

I have changed the name of my field to TransDate to distinguish it from the date function.

I am not sure how to create the combobox you suggested for the immediate window that would select distinct TransDate from tblX.

Now for the big problem, I hovered over the fields in the code and nothing happened (I have an I-bar curser not a mouse pointer, I don't know if that makes a difference or not.)

So I continued to your alternative method of determining the problem. I copied and pasted your suggested code into the immediate window and pressed enter. I then recieved a runtime error '424': object required. I was unable to determine how to add an object from the help information available so I am again turning to the expert for advice. What should I do next?

Another Thought... Could it be that the information for ListPrice is calculated in a query and is not actually stored in a table? I want the Price on the sticker to include tax so I used a queary to determine the ListPrice by multiplying the retail price by the tax rate, I also have a field on a form that calculates the list price just so I can view it there.

I really appreciate your continuing help with this!
Oct 18 '07 #19

klarae99
P: 85
Nic;o)

As I was reviewing the module and trying to see if something new would come to me and I could figure it out, I noticed something new that I thought might be helpful to you who understands code better. I am going to outline what happens when I try the F8 debug because I noticed that a loop is forming. I also included the code as is at the bottom of this post for your reference. Thanks for your continueing support!

A. I put the breakpoint on line CurrentDb.Execute ("Drop Table tblTempStickers"); enter date 9/15 into immediate window hit enter then F8

B. Yellow highlight on line: CurrentDb.Execute ("Drop Table tblTempStickers"); F8

C. Yellow highlight on line: CurrentDb.Execute ("CREATE TABLE tblTempStickers (Item TEXT, ListPrice TEXT, VendorCode TEXT)"); F8

D. Yellow hightlight on line: Set rs = CurrentDb.OpenRecordset("select * from tblTrans where TransDate = #" & dtSelection & "#"); F8

E. Yellow highlight on line: If rs.EOF And rs.BOF Then; F8

F. Yellow highlight on line: End If; F8

G. Yellow highlight on line: While Not rs.EOF; F8

H. Yellow highlight on line: For intI = 1 To rs![Recieved]; F8

I. Yellow highlight on line: rs.MoveNext; F8

J. Yellow highlight on line: Wend; F8

K. Yellow highlight on line: Yellow highlight on line: While Not rs.EOF; F8

L. Yellow highlight on line: For intI = 1 To rs![Recieved]; F8

M. Yellow highlight on line: CurrentDb.Execute ("INSERT INTO tblTempStickers (Item, ListPrice, VendorCode) VALUES ('" & rs!Item & "','" & rs!ListPrice & "','" & rs!VendorCode & "')"); F8

N. Error Message appears.

I had not realized that lines G and H repeated in lines K and L until now, I don't know if this will help or if it is completely irrelevant but I will let you descide. I'll look forward to your next post!

Expand|Select|Wrap|Line Numbers
  1. Function fncCreateTempStickers(dtSelection As Date)
  2. Dim rs As DAO.Recordset ' The requires "Microsoft DAO 3.##" to be set
  3. Dim intI As Integer
  4.  
  5. ' remove old temp table and create fresh copy
  6. CurrentDb.Execute ("Drop Table tblTempStickers")
  7. CurrentDb.Execute ("CREATE TABLE tblTempStickers (Item TEXT, ListPrice TEXT, VendorCode TEXT)")
  8. ' set recordset to select only from passed date
  9. Set rs = CurrentDb.OpenRecordset("select * from tblTrans where TransDate = #" & dtSelection & "#")
  10. If rs.EOF And rs.BOF Then
  11.    MsgBox "No data for this date: " & dtSelection
  12.    Exit Function
  13. End If
  14. While Not rs.EOF
  15.     ' insert the needed number of rows
  16.     For intI = 1 To rs![Recieved]
  17.        CurrentDb.Execute ("INSERT INTO tblTempStickers (Item, ListPrice, VendorCode) VALUES ('" & rs!Item & "','" & rs!ListPrice & "','" & rs!VendorCode & "')")
  18.     Next
  19.     rs.MoveNext
  20. Wend
  21. Set rs = Nothing
  22. End Function
Oct 18 '07 #20

nico5038
Expert 2.5K+
P: 3,072
Your execution sequence shows me that the first record holds a [Recieved] value less then 1 (zero or empty).

The WHILE .... WEND is indeed a "loop" and the rs.Movenext statement takes care that all rows are processed.

Your second record can hold "invalid" data or a syntax error.
Thus my previous request to use the print statement on the string within the brackets of the CurrentDb.Execute () statement.
So:
?"INSERT INTO tblTempStickers (Item, ListPrice, VendorCode) VALUES ('" & rs!Item & "','" & rs!ListPrice & "','" & rs!VendorCode & "')"

Thus the used query text will be complete.

Nic;o)
Oct 18 '07 #21

klarae99
P: 85
Nic;0)

I'm glad that my posts were helpful to you and you know what is going on.

I am not sure what you would like me to do at this point.

I am assuming that your previous request to use the print statement was what I was trying to do by hovering over the list in the code with my mouse or by pasting the code(which I tried and replied to before outlining the loop start, in case you missed that post):

?"INSERT INTO tblTempStickers (Item, ListPrice, VendorCode) VALUES ('" & rs!Item & "','" & rs!ListPrice & "','" & rs!VendorCode & "')"

Into the Immediate window to determine what happened. I tried it again with by copying and pasting the line of code into the immediate window and I recieved the same error message as before 3264, Item not found in collection.

If I enter the code into the immediate window and have ended the debugging I get the same 424 Object Required error that I had in the previous post.

I'm not sure what you need me to do next. Please let me know.
Oct 18 '07 #22

nico5038
Expert 2.5K+
P: 3,072
Hmm, could you make a testcopy of the .mdb and zip that ?
Next post a reply here and after that's visible press the "Edit" at the bottom.
This will allow the insertion of an attachment with your zipped .mdb.

Nic;o)
Oct 18 '07 #23

klarae99
P: 85
Nic;o)

I have attached a copy of my database for you to review, please note that there are many little things that I am still working on figuring out what I need to do, aka a work in progress. If you have any questions about it or would like explinations as to why I did something the way I did just let me know. Thanks for your help with this!!

Well, I tried to attach the database using the compression feature we had on this computer, some unnamed genaric compression program that was part of our computer purchase, it was able to compress the database to about 1M, way to large, so I went online and downloaded a copy of stuffit, used it to compress the file 800K, then I opened the copy used the compress/repair feature of the database, then stuffedit. Still 600K, the max I can post on this site is 97K.

Do you know of any way that I can shrink the size of the copy before I try to compress it, I made a copy of the file to send to you but in your post you mentioned a test copy, is this different? If it is how would I create it?

Should I try to create a new database and import the tables/queries/reports etc. but no data? Just let me know the best way to proceed and I'll work on it. Thanks!
Oct 19 '07 #24

nico5038
Expert 2.5K+
P: 3,072
I asked for a "test copy" as sometimes a database can hold "sensitive" information. We only need the part of the database that enables us to troubleshoot the problem, so you could create a new database and import with "File/Get external data/Import" the report and the table(s)/Querie(s)/Form(s) needed to construct the problem. The tables don't need to be filled "to the max" and before zipping it will help to perform a Tools/Database/Compact&repair.

Nic;o)
Oct 19 '07 #25

klarae99
P: 85
Ok, I will work on exporting only those tables/queries that I would like to have on the price stickers. As well as my module in its current state. It will probably take a while for me to get this all together but as soon as I do I will upload it. Thanks again for all your help!

I created a new database and imported the relevant tables and queries (I think I got them all anyway, if you are missing one let me know and I will get it for you.)

Thank you so much for taking a look at this!
Attached Files
File Type: zip Test2Giftshop.zip (20.6 KB, 66 views)
Oct 19 '07 #26

nico5038
Expert 2.5K+
P: 3,072
Hmm, the problem was the fact that tblTrans doesn't hold the needed fields.
I've switched the rs to use the qryListPrice and changed that to:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTrans.Recieved, tblTrans.TransDate, tblProd.VendorID, tblCat.TaxRate, tblProd.Price, ([Price]*[TaxRate])+[Price] AS ListPrice, tblProd.Item
  2. FROM (tblCat INNER JOIN tblProd ON tblCat.CatID = tblProd.CatID) INNER JOIN tblTrans ON tblProd.Item = tblTrans.Item
  3. GROUP BY tblTrans.Recieved, tblTrans.TransDate, tblProd.VendorID, tblCat.TaxRate, tblProd.Price, tblProd.Item;
  4.  
So the recordset (rs) wasn't filled properly, as it needed the fields from the qryListPrice. This query has been changed to add the TransDate and the VendorID.
(That was called CendorCode....) The query mentioned "List Price", thus didn't match your[ListPrice] because of the space...

The final function now looks like:
Expand|Select|Wrap|Line Numbers
  1. Function fncCreateTempStickers(dtSelection As Date)
  2. Dim rs As DAO.Recordset ' The requires "Microsoft DAO 3.##" to be set
  3. Dim intI As Integer
  4.  
  5. ' remove old temp table and create fresh copy
  6. CurrentDb.Execute ("Drop Table tblTempStickers")
  7. CurrentDb.Execute ("CREATE TABLE tblTempStickers (Item TEXT, ListPrice TEXT, VendorCode TEXT)")
  8. ' set recordset to select only from passed date
  9. Set rs = CurrentDb.OpenRecordset("select * from qryListPrice where TransDate = #" & dtSelection & "#")
  10. If rs.EOF And rs.BOF Then
  11.    MsgBox "No data for this date: " & dtSelection
  12.    Exit Function
  13. End If
  14. While Not rs.EOF
  15.     ' insert the needed number of rows
  16.     For intI = 1 To rs![Recieved]
  17.        CurrentDb.Execute ("INSERT INTO tblTempStickers (Item, ListPrice, VendorCode) VALUES ('" & rs!Item & "','" & rs!ListPrice & "','" & rs!Vendorid & "')")
  18.     Next
  19.     rs.MoveNext
  20. Wend
  21. Set rs = Nothing
  22. End Function
  23.  
and this needs the new qryListPrice as indicated above.

Nic;o)
Oct 20 '07 #27

klarae99
P: 85
Nic;o)

Thanks! The code now works but I noticed a couple of things when I ran it.

1. The prices all round out to 4 decimal places. When I changed the field in the table (after creating the table) to a Number and used the currency setting it rounded everything to the nearest whole dollar, when I changed the setting before running the code it was still out 4 decimal places.

2. The other thing was that the Vendor Code field seems to be listing the VendorID, not the Vendor Code.

If we can fix these two things I think we will be well on our way to getting this working!! Its so exciting! Thanks for your continued help!!
Oct 22 '07 #28

nico5038
Expert 2.5K+
P: 3,072
OK, almost there :-)

1) Best to use the datatype "Currency" for the field. Thus always the number of decimals for the currency will be used.

2) For this you need to add the [Vendor Code] to the query that's SET to the recordset "rs". Then the INSERT can use: rs![Vendor Code] instead of the VendorID.

Nic;o)
Oct 22 '07 #29

klarae99
P: 85
You are So Awesome!!

It works GREAT now!

I am so Excited!

Now, I am assuming that I can create a report bound to tblTempStickers to produce the stickers themselves.

I do have a couple of questions though
In my ideal setup, this is what would the user would see happening:

1. On switchboard click on button labeled Print Price Stickers
2. Form would open requesting Date that items were entered into database (typically 'today' but may be 'yesterday'; I know that at one point you had suggested a combobox with a list of dates the user could select from, which would work but I would want to limit the list to the 'most recient 5 dates' or something)
3. User would enter date and click OK
4. Price Sticker report would open showing items with a transdate matching the date entered above.

Is this senario possible or do I need to break the steps up into something where the user would retreve the records for printing price stickers and then have to do something else to generate the report?

If the ideal sinario is correct I know that I can create the switchboard item, and the form to enter the date (but could use some pointers on how to limit my date to the most recient 5). What I would really need help with was to 'code' the command button to run the module using the date entered and then generate the report based on the new table.

I can't wait until your next response so that I can get this tricky little process completed! I really appreciate all your help!
Oct 23 '07 #30

nico5038
Expert 2.5K+
P: 3,072
OK, step by step:

1. On switchboard click on button labeled Print Price Stickers
I hate the switchboard (I always create my own menu by creating an unbound frmMain with buttons to open the specific form or another menu form e.g. foReports), but you'll need to have a form with the possibility to start the labels and that can be linked to a switchboard choice.
2. Form would open requesting Date that items were entered into database (typically 'today' but may be 'yesterday'; I know that at one point you had suggested a combobox with a list of dates the user could select from, which would work but I would want to limit the list to the 'most recient 5 dates' or something)
Base the combobox on a query like:
[code=sql]Select Top 5 Distinct Datefield from tblX order by Datefield Desc;[code]
3. User would enter date and click OK
Add the code to the OnClick event of the [PrintLabels] button to pass the date to the created function and open the report in preview mode. Just two lines:
[code=vb]call fncCreateTempStickers(Me.ComboNameWithDatefield)
Docmd.OpenReport "rptLabels", acPreview
[code]
4. Price Sticker report would open showing items with a transdate matching the date entered above.
Already handled by point 3.

Nic;o)
Oct 23 '07 #31

klarae99
P: 85
Nic;o)

OK, we are so close I can taste it...

I set everything up as you suggested and it works, the only thing now is that when I select a date and click on the price stickers button the Visual Basic window opens and I have to F8 my way through the code before I can view the report.

I tried to remove the break I put in originally to run the debugger but it still opened. I also closed Visual Basic and the database, then reopening the database and tryed to print the Price Stickers but Visual Basic still opened and ran the debugger.

Any advice on how to eliminate the need to Debug everytime would be greatly appreciated.

Thanks for you continueing help!
Oct 23 '07 #32

nico5038
Expert 2.5K+
P: 3,072
Hmm, looks like an Access bug. Best to run a Tools/Database/Compact & Repair.
When that doesn't work create a new empty database and use File/Get externaldata/Import to get all objects in a "fresh" copy.

Nic;o)
Oct 23 '07 #33

klarae99
P: 85
Nic;o)

I compacted and repaired my database and as you suspected it did not resolve my problem.

I then created a new database and imported everything from the old database to the new one.

Now when I click on Print Price stickers (after selecting date) I get a runtime error 3061 Too few parameters Expected 1

when I select debug, the line

Expand|Select|Wrap|Line Numbers
  1. Set rs = CurrentDb.OpenRecordset("select * from qryListPrice where TransDate = #" & dtSelection & "#") 
is highlighted in yellow, when I select F8 I get the same error message.

I'm not sure what happened since this is the same module that I used in the other database.

Any thoughts?
Oct 23 '07 #34

klarae99
P: 85
Nic;o)

I got it figured out.

I imported my queries into the new database before I linked my tables to the new database and my queries got confused. When I deleted my queries and reimported them everything worked great.

Thank you So much for all your help! I am so excited about this function actually working! I know that I couldn't have done it without you. I really appreciate the time you dedicated to me and my obvious lack of knowledge. I now appreciate what code can do so much more than I did before and will continue to try and learn the language. Thanks again!
Oct 23 '07 #35

nico5038
Expert 2.5K+
P: 3,072
Glad I could help. It's always very satisfying to see members making progress during the course of a thread. Now you're already able to cope with the "Too few parameters" error, and who knows you're assisting here soon others with their first steps :-)

Nic;o)
Oct 23 '07 #36

Post your reply

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