Automate no. of label printed based on no. of units per batch? - help please

[olivercastell]

Hi, I work in a hospital pharmacy and i am trying to write a database for the production of labels for sterile manufactured products. This is my first real experience with access and im impressed with the possibilities/uses of it.

What im looking for is a macro/code to produce the correct number of labels based on the size of the batch. I require 2n +1 labels (n= number of units in a batch) for each batch.

the number of units will be entered along with other info on the form for batch data entry, is it possible to utilise this to autamically print the required no. of labels? This is an important feature and helps minimise error in the manufacturing process.

Any help will be greatly appreciated.
I have a little experience with macros but not really any with visual basic - sorry

 

[oldaf294]

Number of units to print

The description below gives the following results;
( I noticed in the preview that the numbers do not line up with the columns...)
item quantity Number
chair 1 1
table 5 1
table 5 2
table 5 3
table 5 4
table 5 5
pencil 40 1
pencil 40 2
pencil 40 3
. 4
. (from 4 to 39) (5 to 38)
. 39
pencil 40 40
apples 2 1
apples 2 2
... ... ...

Add a new table to your database with one field, numbers. Populate the field with numbers from 1 to how many you would possibly need.
Create a query based on your table.
Add the numbers table to the query (don't relate the numbers table) and place the [numberfield] in the design grid. In the criteria section of the [numberfield] type "Between 1 and [Quantity field]"
when you open the query there will be as many entries for each product as are in the [quantity field]

 

[olivercastell]

Hi,

thanks very much, that seems like a good way round the problem. I think i get it, so you end up with the appropriate number of reports(labels) rather than requesting it to print the one label x number of times.??

Thank you very much for your help

Ill give that a go now and see how i get on.

thanks

 

[olivercastell]

Thanks

When the query runs it is asking me to input the quantity is this what you expected? or am i doing something wrong? I would prefer it if it could automatically take this data (number of labels to print based on the number of units) from the main table as this has already been entered - is this possible? Because this is duplication of data - again more room for error.

I think i may have done something wrong, as im getting n labels rather than 2n+1??

thanks for your help

Ollie

 

[oldaf294]

Your "quantityfield" should be the field that you have for the number of labels you want printed. That should eliminate the need for entering a parameter value.
jim

 

[olivercastell]

Thanks ,

I see how this works however its not quite what im trying to achieve. With this example if you put in the quantity as 10 you get 10 labels, where as for a quantity of ten items i would require 21 labels (2n+1). I cant really change the field from number of items(units) to be manufactured to number of labels required to get round this problem for two reasons; the database will be used as our 'batch book' this legally must contain the number of units in the batch, also I need to be able to total up the number of items/units manufactured in say a month/3 monthly period for statistics/funding purposes.
If at all possible i would like to avoid having to input both the number of units and number of labels required as this could lead to errors (this is something V.important in the NHS!) and is essentially duplication of data.

I cant really think of a way round this using tables/queries which makes me think i need to use VB?

Thank you for your help

 

[oldaf294]

oliver,
I have tried and tried to get the *2 + 1 to work... I can get the figure but referencing it to the "Between" criteria has got me stumped...
In a blank QBE field i placed [quantityfield]*2+1. It, of course, gave me the correct number but I cannot for the life of me figure out how to reference the value in the between clause.

 

[oldaf294]

Oliver,
end of the between clause;
Between 1 and [Quantityfield]*2+1
and see if it gives you the correct number of labels.
jim

 

[oldaf294]

Oliver,
Almost forgot. This is a routine described by CosmoKramer on a different forum.
If the "numbers" table contains as many numbers as your [field]*2+1 maximum then the between 1 and [field]*2+1 will work.
Have had many requests for this type of routine.
jim