Printing a single label problem

[colinmcewan]

Hi. I am a novice and would appreciate a helpimg hand.

I have customised the contacts database and now want the ability to print a single label using a button on the contact details form.

Having searched the web, I found a very helpful article by Susan Harkins which explained precisely how to do it. Snag is, when I hit the button, ALL the contacts are printed.

I suspect that it is something to do with the fact that the primary key for each contact is an automatically generated number rather than a string - but I could be way off here.

The code she gave was a follows

Private Sub cmdPrintLabel_Click()

'Send customer data to label report

Dim str As String

On Error GoTo ErrHandler

If IsNull(Me!CustomerID) Then

MsgBox "Can't print an unsaved record", _

vbOKOnly, "Error"

Exit Sub

End If

str = "CustomerID = '" & Me!CustomerID & "'"

Debug.Print str

'Open report in Print Preview.

DoCmd.OpenReport "rptCustomerLabels", acViewPreview, , str

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " _

& Err.Description, vbOKOnly, "Error"

End Sub

Can anyone help me out ?

Many thanks

Colin

 

[prley]

I would suspect the report design. You might have the record source property of the report set to the whole customer table. The record source of the report should be a query that uses the paramater your sending (str) it to identify the one record you want to print.

I havn't used paramters with reports in a long time so I can't help much more than that. I usually reference a field on a form that the user selects and have a stored query in the database then set the record souce to that query.

 

[colinmcewan]

Thanks for this. In fact the record source IS a query so no cigar I'm afraid.

I'm probably just doing something really stupid with the syntax.

Regards

Colin

 

[Mr. B]

It sounds as if you are not filtering your query. You must provide a filter that will only return then current record that your are currently on in your form. That could be the record ID value and that value might already be in a control on that form. If that is the case then you can open your query and use the Builder to identify and specify the name of the control on your form that has the record ID that you want to use for filtering as the criteria for the same field in your query.

Hope this helps.

 

[prley]

Maybe post the query and I'm sure someone will chime in. Since you getting output it's probably something simple like you said in the report design.

 

[colinmcewan]

Thanks for the replies Mr B and prley.

Mr B - I thought the filter was in the VBA where it says

str = "CustomerID = '" & Me!CustomerID & "'"
Please let me know if this is wrong.

prley - The fact is I'm not sure how to post the query ! There doesn't seem to be a separate file I can attach ?

 

[prley]

On the properties of the report will show you the record source ie query. Unless you are defining the query in VBA of the report itself.

 

[ted.martin]

If you can pick the code out of these elements attached then the button on the Contact form with a 'Bell' on it [ stands for laBEL ], then this is a solution that works well for J8163.

The interesting part of the technique is where the chosen position for one label on the sheet of blank labels is a concatenation of the row and column and then 'decoded' in the table J8163 layout.

The code was written a couple of years ago so feel free to tidy it up.

 

[colinmcewan]

After a year, I'm still confused.

I got a suggestion from a computer magazine here in the UK. They suggested a button on the Contact Details form linked to the following code:

Private Sub Command282_Click()
txtselect -"[Last Name] - '" & Me.[Last Name] & "' AND [First Name] - '" & Me.[First Name] & "'"
DoCmd.OpenReport "Labels Contacts1", acViewPreview, txtselect
End Sub

Snag is that the debugger stops and highlights the bit in red and says "Compile error. Sub or function not defined"

Can anyone see where the code is wrong ?

 

[JHB]

txtselect -"[Last Name] - '" & Me.[Last Name] & "' AND [First Name] - '" & Me.[First Name] & "'"

The - is wrong it has to be a =

txtselect = "[Last Name] = '" & Me.[Last Name] & "' AND [First Name] = '" & Me.[First Name] & "'"

Where do you declare txtselect with Dim?

Dim txtselect as String

 

[colinmcewan]

Many thanks JHB. I was working from a poor photocopy !

All seems to be working now

 

[JHB]

You'ar welcome.
For 1½year?