Access2007 - how to print multiple labels based on a record value.

[pinto47]

I am using Access2007 - I have a database for a flat accommodation
I have a number of records which I need to print. No problem, however I need to print the same record multiple times depending on a record field value.
1 - A table (called:- tabbooking) have all the records (name; numberofpersons; arrival; numberofnights)
2 - A have another table (called:- tabmeals) that information is inserted via a query file on particular arrival date request;
3 - A query (called:- qrymeals) file which reads details from the tabbooking via a request from record arrival and the result is inserted in the file tabmeals;
4 - a report (called:- repmeals) file that reads and prints the information from the table tabmeals

So far so good. As I wish to print a report depending on the value given in the record field value "numberofnights" multiple times how to I go about it? The purpose of creating the second table tabmeals is I am sure somehow the same record can be inputted multiple times but I have no idea how.

Eg:-
Name:- Ransley; numberofpersons:- 2; arrival:- 1/feb/16; numberofnights:- 6;
Name:- Jackson; numberofpersons:- 1; arrival:- 1/feb/16; numberofnights:- 14;

So I need to print ransley for 6 times and Jackson for 14 times.



Can anybody be kind enough to fix it for me. I have little knowledge of access BUT I do know that may be a code in VBA via [Event Procedure] should do the trick.

Philip

[ranman256]

the query, qsRptQuery, is whatever query you use in your report.

Sub btnPrint_Click()
iCopies = NZ(Dlookup("[numberofnights]", "qsRptQuery","[name]='" & me.txtName & "'"),1)
For i = 1 to iCopies
  DoCmd.OpenReport sRptName
Next 
end sub

[pinto47]

Thanks for your reply.
(1) were would the code be placed? and is - Sub btnPrint_Click() - an Access2007 code?)
(2) Am I to assume that the qsRptQuery in my case is "qrymeals"?
(3) your field "name" is the actual field "name" used by my query?
(4) so me.txtname is a filed name?
(5) so sRptName is your report name equivalent to my report name repmeals


So is the below correct?
I am assuming that this code is placed in the [Event Procedure] on click in the repmeals. Why not on load?

Sub btnPrint_Click()
iCopies = NZ(Dlookup("[numberofnights]", "qrymeals","[name]='" & me.txtName & "'"),1)
For i = 1 to iCopies
DoCmd.OpenReport repname
Next
end sub

Your help is very much appreciated

[pinto47]

I have written the below code but it is not doing what it is suppose to do. It does make report but not in multiple times as per value in the nts field. On click it gives me an "Error 94 - Invalid use of IsNull".

Private Sub Report_Click()

Dim intNOM As Integer 'NOM means number of nights meals
Dim mTimes As Byte
On Error GoTo errorhandler
intNOM = 1
mTimes = 0
mTimes = DLookup("[nts]", "tabAmeal") 'mTimes means number of meals

If IsNull([nts]) Then
MsgBox "File is empty GO to Query", _
vbOKOnly, "Error - Run the Query"
Exit Sub
End If
' now print the information found in table "tabmeal" number of times depending on the value field "nts"
For intNOM = 1 To mTimes
DoCmd.OpenReport "repAmeal2"
Next

errorhandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub