save multiple records at once in vba access

[ilse20]

hello,

i'm working with access and now i have a question if it is possible to save multiple records at once.

i want to save labels. these will be saved in the table when you press the button and i have a seperate button for generating the labels from the table to a pdf file.

now i'm having a problem:

saving and generating the labels works perfect.
now i have a textbox on my form where the user kan submit the amount of labels he wants to save, because sometimes it's possible that one label needs to be printed more than one time on the pdf file.

i've tried some things but i can't seem to make it work

is it possible to do this through a for loop, that the for loop checks the amount that is submitted in the textbox and that he loops until the value is null.

thanx in advance

ilse

 

[DCrake]

Why would you be wanting to save lables to a PDF file? When creating labels you have to nominate a printer and the label size. If the person receiving the pdf does not have a matching printer how are they going to be able to print the labels?

 

[ilse20]

that is not the idea. the company where i'm programming for has a dymo label printer. but it only works on one computer. the want to change this. they don't want to use the dymo label printer but a normal printer where they can put a a4 label sheet in, and then through the pdf file they can print the labels to this sheet.

 

[DCrake]

A dynamo label printer prints labels from a roll of labels. Even if you said you wanted 10 labels for the same person it would produce the output a 10 consecutive lables. It would not for example print them 5x2. So even saving them to a PDF will give you 1 vertical list of lables. You need to first find out which type of labels they are using. ie avery L7163, L7162. Then create a label report, which will handle vertical and horizontal printing. Save this to PDF and distribute this. The end user will insert the matching laser label paper in the printer and print the pdf which will transdfer them to the Avery Labels.

 

[ilse20]

yes i already have that. i save those labels to a table and based on that table i make a report and that report i generate to a pdf file.

 

[DCrake]

So your question is: How do I create multiple labels for a particular person?

My first question is are you printing the PDF for 1 person only or for more than one person?

IE
Person 1: 1 label
Person 2: 3 Labels
Person 3: 2 lables
Person 9: 15 labels
etc
Also if for more than one person do you want the page to break for each person change?

 

[ilse20]

in the access system the company is working with everybody works in but everyone is logged in with his/her own username and the pdf file only shows the labels that are saved on that username.

so for instance if person a is the current user an has 5 labels in the table and person b has 5 labels in the table, the pdf file will only show the labels of person a, because he is the one that presses the button.

my question is: how can you save the same label multiple times at once? so that when the user clicks on the save button an has submitted the amount 3 in the textbox that the same label will be saved 3 times in the table.

 

[DCrake]

The crudest way is to create a table with one field in it that holds sequential number form 1 to howmany you want it to.

Then in a query bring in your label table and this table. Do not join the two tables. Now lets say you want to print 10 lables Bring down the field that holds the numbers and in the condition line enter [How many lables to print?]. Now view the query in datasheet mode. It will ask you for the quantity, enter 10 and click Ok. You should now see 10 rows of data for the same record.

This becomes the basis of the label report.

There are far more efficient methods but this will get you going for now.

 

[ilse20]

this is not really what i'm looking for.

i want to keep it a simple as possible for the users.

the way that they have it now with the dymo is that they can submit the amount when the are on the form for printing the label and then the dymo prints that amount. they don't save anything in a table for that.

i want that the can submit the amount on the page where the save the logo and then that vba looks in that field and saves that amount of labels for that label.

this is the code i have now for saving one label:

If IsNull(Form_Products![ProductID]) Then
MsgBox "Select a subproduct first."
Else
Set rs_NewExtUpdate = CurrentDb.OpenRecordset("Generate_Labels")
With rs_NewExtUpdate
If Me.tb_AmountLabels.Value >= 1 Then
.AddNew
!ChangeUserName = ChosenName
!Pronum = Form_Products![Pronum]
!finishing = Form_SubProduct_subform![finishing]
!colour = Form_SubProduct_subform![colour]
!Ref_Num_Buyer = "Customer Reference:" & Form_Products![Ref_Num_Buyer]
!Date = Date
!logo = "c:\access\logocintac.jpg"
.Update
MsgBox "Label Saved"
DoCmd.Close
Else
MsgBox "Submit Amount First"
End If
End With
End If

my idea was to put that .addnew to .update in some sort of for loop that looks up the amount that is submitted in the textbox and saves that amount of labels

 

[DCrake]

For x = 1 to  Me.tb_AmountLabels
   .AddNew
   !ChangeUserName = ChosenName
   !Pronum = Form_Products![Pronum]
   !finishing = Form_SubProduct_subform![finishing]
   !colour = Form_SubProduct_subform![colour]
   !Ref_Num_Buyer = "Customer Reference:" & Form_Products![Ref_Num_Buyer]
   ![B]Date [/B]= Date
   !logo = "c:\access\logocintac.jpg"
.Update
Loop

Once thing to bear in mind is that word Date is an Access reserved word and should nopt be used for table names, etc.

 

[ilse20]

thanx it works.

i only changed loop in next.

but it works as well. when i put 3 in the textbox it saves the label three times.

thank you

 

[DCrake]

Sorry my mistake it should have been Next instead of Loop

 

[ilse20]

no problem it works now so i'm happy