all values in a listbox

[tomvdduin]

Hello everyone,

I have a form on witch users can select customers that of who they want to print address stickers of. They can select them from a drop down box, then enter the amount of stickers they want, and click on 'add'. Then in a temp table is written the customer ID and the amount of stickers they want of that customer.
When they click on print, a report is opened. It has a recordsource with a query that selects all the customers. the report is opened on this way:
docmd.openreport "etiket1",wherecondition:=strwherecondition
in strwherecondition the records are filtered with the customer ID's in the temp table, so it looks like:
"[c_id] = 121 OR [c_id] = 3"

Everything is working just fine, exept that I don't know how to implement the amount of stickers that must be printed. for example, I want to print 3 stickers of customer 121 and 5 of customer 3. How can I tell access that in the report the sticker of customer 121 must be print 3 times? Any ideas on how to implement this?

any help is welcome!!

greetz,
Tom

 

[JeremyNYC]

Tom,

You'll have to make your form design a little more complex. You'll want to use a subform or make your list box much more fancy. I would suggest the subform.

You'll need a place to store the number of labels per person, or else Access will have no way of knowing what it is. If you list the people in a subform and have a control in there for # of labels that will work--though you'll have to add a field to your Person table so that you can store the info temporarily. You may or may not want to delte the values in that field after you close the form used to do the printing.

One other option would be to use the listbox with that field shown alongside the person's name. Every time you highlight a person in the listbox, you'll open an input box to collect the number of labels to print. Of course, you'll have to do careful error handling to deal with no answer and non-numeric answers.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[tomvdduin]

Thanks, Jeremy! When I select people of whom I want to print stickers of, it's placed in a temp table, called tbl_TEMP_stickers. In that table, there are the following collumns:
c_id (number, primary key) > customer number
name (string) >customer name
amount (number) >number of labels per customer
the listbox get's the values from that table. Everytime the stickers are printed or the user exits the form, the table is emptied. I thought it was better to empty the table when I exit the form. In that way I am sertain that the data is deleted. But perhaps I can empty the table when I exit the report. In that way I can use the table to have the number of labels per person.
The only problem I have in that way is how I can make a query that selects a person 5 times when the value in the column amount is 5 for that person.

Any idea?

 

[tomvdduin]

Tnx Jeremy for your answer! I am using a temp table, named tbl_TEMP_stickers, with the following columns:
c_id > unique customer number, primary key
name > customer name
amount > the number of labels to print.
This table is now emptied every time the form is unloaded. Perhaps I have to delete the values after the labels are printed, so I can use the values in the table to print the labels. But how can I make a query that selects 5 times a certain customer of where the value for amount is 5 in the temp table?
perhaps I can enter the values 5 times in the table, it's temporarily, so the data is removed at the end, but it that a good sollution?
the table would look like this:
c_id;name
1;"tom van der duin"
1;"tom van der duin"
3;"jeremy"
3;"jeremy"
3;"jeremy"
so now I don't have to make a complex query. What's your opinion Jeremy?

greetz

Tom

 

[tomvdduin]

sorry, I looked at the thread and didn't saw my second post, so I edited and reposted it...

 

[JeremyNYC]

Tom,

No worries.

My thought after reading your second post was that you shouldn't use a temp table, just put the NumberOfLabels field in the main table as I had described. Temp tables lead to bloating in your database, and the need to compact quite often.

After reading your third message, I can see some value in the temp table. To do this, add that field to the main table. Then when you go to print, make a recordset of all the people who have numbers in that field (as you'll delete the numbers after you print). Then delete all records in the temp table. Then loop through the recordset adding records to the temp table. Then print the report. Then set the value of the numeric field to zero in all records in the main table.

How's that sound?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[tomvdduin]

Ok, Jeremy, I think it's working. Tnx 4 your help!