Printing current record

Topic

New Reply

Right now I use the Like command from a query to print what record I need. I would like to print the current record that is viewed from a form. Not the form itself. Is there away to do this.

Thanks,

Dan from The Last Frontier

Reply | Quote

Replies

Dear Dan,
The following code linked to the on click event of a button on your form should do it.
You will need to design a report that delivers the info you require and replace the code — strDocName = “rpt_PrnOrders” with your reports name. You will also have to modify the code — strFilter = “tbl_Orders.OrderID = Forms!frm_Take_Orders!OrderID” to return the current record displayed on your form.

Private Sub Cmd_Print_Current_Rec_Click()
On Error GoTo Err_Cmd_Print_Current_Rec_Click

Dim strDocName As String
Dim strFilter As String

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 ‘Save record

strDocName = “rpt_PrnOrders”
strFilter = “tbl_Orders.OrderID = Forms!frm_Take_Orders!OrderID”

DoCmd.OpenReport strDocName, acViewNormal, , strFilter

Exit_Cmd_Print_Current_Rec_Click:
Exit Sub

Err_Cmd_Print_Current_Rec_Click:
MsgBox Err.Description
Resume Exit_Cmd_Print_Current_Rec_Click

End Sub

Rupert

Reply | Quote

Thanks for the help. It looks like it will work for me. Again, thanks.

Reply | Quote

Here’s another approach for a print button on your form. This assumes that you want to print an existing report called rptCustomers for the CustomerID that matches the current record on the form. This will work in Access 97 or 2000.

Private Sub cmdPrint_Click()
  'dim an object variable as an
  'instance of the report class object
  'named "rptCustomers"
  Dim rpt As Report_rptCustomers
  'open an instance of the report class object
  'this isn't visible at this point
  Set rpt = New Report_rptCustomers

  With rpt
    'Set the report filter to the value
    'of the current record on this form
    .Filter = "[CustomerID] ='" & [txtCustomerID] & "'"
    'turn the filter on
    .FilterOn = True
    'make the report visible
    .Visible = True
  End With
  'destroy the object variable
  Set rpt = Nothing
End Sub

Reply | Quote

I tried to implement this solution to my case but I must be doing something wrong. I have a form called “MyRecords” where I want to place a button that will allow me to print the record I am currently viewing using the report called “SingleRecord”. The form reads from a query that is linking two tables.

I apologize for taking your time and will appreciate any assistance you can give.

Reply | Quote

What the form reads from isn’t the issue. If you have a key field or fields in the current record on the form that will uniquely identify a record in your report, you should be able to make it work using either of the suggested approaches. If the report is based on the same query or at least on a query that returns the equivalent record, it will work because you pass it a filter or where string that limits the record to the one you want or you can actually set the recordsource for the report in code to do the same thing.

If you post your code, we can see what might be going wrong. Otherwise, we’re just guessing and have a 50% chance of guessing wrong.

Reply | Quote

Thanks to you and JerryC for your prompt response.
I am no expert on this so, this is what I’m doing: I open the form (MyRecords) in design mode. I select the command option from the Toolbox and place it where I want it. I right click over the button, select properties, and go into “On Click”. I select the (…) option “Choose Builder; Code Builder” and paste the following:
“““““““““““
Private Sub cmdPrint_Click()
‘dim an object variable as an
‘instance of the report class object
‘named “rptCustomers”
Dim rpt As Report_SingleRecord
‘open an instance of the report class object
‘this isn’t visible at this point
Set rpt = New Report_SingleRecord

With rpt
‘Set the report filter to the value
‘of the current record on this form
.Filter = “[ActionID] ='” & [txtActionID] & “‘”
‘turn the filter on
.FilterOn = True
‘make the report visible
.Visible = True
End With
‘destroy the object variable
Set rpt = Nothing

End Sub
“““““““““““““`
Once again, thanks for any help!

Reply | Quote

Is ActionID a numeric field? If so, take out the single quotes in this line

.Filter = “[ActionID] ='” & [txtActionID] & “‘”

You only wrap the value in quotes when it’s a string.

Reply | Quote

if you have a field in your form that is unique like an autonumber field set the query criteria of the report to this control of the form.
them you can use a macro or code to open, print and close the report if you want

Reply | Quote

I’m trying to print off a single invoice, too. I used your code, but I keep getting: Compile Error–user-defined type not defined. What does this mean? Here’s my code:

Private Sub cmdPrint_Click()
‘dim an object variable as an
‘instance of the report class object
‘named “Invoice”
Dim rpt As Report_SingleRecord
‘open an instance of the report class object
‘this isn’t visible at this point
Set rpt = New Report_SingleRecord

With rpt
‘Set the report fileter to the value
‘of the current record on this form
.Filter = “[InvID]=” & [txtInvID] & “”
‘turn the filter on
.FilterOn = True
‘make the report visible
.Visible = True
End With
‘destroy the object variable
Set rpt = Nothing

End Sub

Also…how would you make it so you could preview it before you print it? Thanks for your help.

Jennifer

Reply | Quote

Make sure your report has a module behind it. Open it in design view and click on the code button. The module doesn’t have to have anything in it but the Option Compare and Option Explicit lines, but it has to be there before it can be called in my code, so go back and save the report.

This code does open the form in preview, that’s what making it visible does. I generally attach a custom menubar to my reports so that I can print them or change the page setup without having access to the built in menubars. That gives the user the chance to be sure they’re getting what they wanted and allows them to specify the printer if necessary.

Reply | Quote

I still can’t get it to work. It still gives me a compile error. When it does that it hightlights the first and the fifth line. Here’s the code I have:

Private Sub cmdPrint_Click()
‘dim an object variable as an
‘instance of the report class object
‘named “Invoice”
Dim rpt As Report_SingleRecord
‘open an instance of the report class object
‘this isn’t visible at this point
Set rpt = New Report_SingleRecord

With rpt
‘Set the report fileter to the value
‘of the current record on this form
.Filter = “[InvID]=” & [txtInvID] & “”
‘turn the filter on
.FilterOn = True
‘make the report visible
.Visible = True
End With
‘destroy the object variable
Set rpt = Nothing

End Sub

Thanks.

Jennifer

Reply | Quote

Ok, so this is the line that gets highlighted?[indent]

---

Dim rpt As Report_SingleRecord

---

[/indent] If so then it isn’t recognizing the report module. When you’re in the IDE, do you see the report module in the project explorer? If not, that’s the problem and you need to enter an empty shell procedure it in and then save the report. Then you should see it in the project explorer.

If you do see it and the code still fails, something else is going on. I use this technique all the time without problems, so I know it works. That means this specific situation is causing it to fail and we’ll have to work out why.

Reply | Quote

Okay, Charlotte. Call me a complete untechnical weenie, but I don’t understand “techie” lingo. What is “IDE” and what is the project explorer? Sorry.

Jennifer

Reply | Quote

Don’t apologize, the jargon keeps mutating anyhow. IDE is the integrated development environment, translation: the visual basic editor. If you’re in a module looking at code, you’re in the IDE or the VBE or whatever. In Access 2000, you are actually in a separate environment and have a project explorer window in the IDE that displays a tree view of all the modules, including the class modules, in the project. If you’re in 97, there isn’t really a separate IDE, and the project explorer doesn’t exist.

I’ve used the same code in both Access97 and 2000, so it isn’t the code that’s the problem.

Reply | Quote

Well, I got past that problem, but then I got a run-time error on the [txtInvID]. What does that whole line mean anyway?

.Filter = “[InvID]=” & [InvID] & “”

That is the big problem I have with Access. I never really know what each line (or property) does.

Jennifer

Reply | Quote

That’s a problem that can only be cured by study. Programming of any kind is a process of learning a new language, including the vocabulary and grammar, just like learning a foreign language.

The line is inside a With … End With structure that refers to the report object you created earlier in code. What you’re doing in this line is creating a filter string and setting the report’s filter property to that string. It means, “set the filter property of the report to a string that says the field, InvID, is equal to the the literal value that’s currently in the control txtInvID on this form.”

From the looks of your code[indent]

---

.Filter = “[InvID]=” & [txtInvID] & “”

---

[/indent]…as long as your underlying field is named InvID and your control is named txtInvID, it should work. The way you put in your most recent post, it looks like the field and the control have the same name, since you dropped the txt prefix. If that’s the case, I’m not surprised you’re having problems because Access isn’t really certain which object it should evaluate. You need to make sure the fields and the controls do NOT have the same name, even if the wizard does name them that way.

You also need to be sure that the field InvID is NOT a text field for the code to work. If it IS a text field, you need to modify the line to read like this:

.Filter = "[InvID]='" & [txtInvID] & "'"

That will wrap the value in text delimiters so that the filter will work with a string value.

Reply | Quote

So you’re saying that I should go into properties of the field on my “Invoice” form and change “Name” to something else that is different from the “Control Source”?

Jen

Reply | Quote

It’s worth a try. In general, it’s advisable to make your control names different from the underlying field names. Sometimes you can get away with not doing so, but it can bite you unexpectedly.

Reply | Quote

Well, I changed all of them but I am still having trouble. Here’s the line again.

.Filter = “[InvID]=” & [invInvID] & “”

The first “[InvID]=” is that suppose to be the field name in the report? And the second “[invInvID]” suppose to be the control source of the InvID field in the Invoice form?

Jen

Reply | Quote

The first “[invID]” is the name of the field in the report’s underlying recordset. The second object should be the name of the CONTROL on the FORM that contains the value you want to pass.

What you’re trying to do is tell the report to apply a filter that will literally evaluate to something like “[invID]=15”, assuming that the value in the control named invinvID on the form is 15 in the current record. Does that make it any clearer?

Reply | Quote

Boy, this is frustrating. It’s still not working. Is it because I have a Subform in the Invoice form? I have a Form with invInvID in the top section, and invdetInvID in the Subform. But I tried changing it to [invdetInvID] and it just gave me a run time error that it couldn’t find that field. Do you know what I’m doing wrong?

Jen

Reply | Quote

Aren’t the values the same? The issue seems to be with the report rather than with your form, right? Your report is not printing a single invoice, but it should be if you pass it the invoice key, unless there’s something wrong with either the report or the value you’re trying to pass it.

Did you by any chance create the report by saving the form? Does the report have a subreport on it? Did you set up grouping and sorting to group the detail records (the equivalent of the form’s subform) by invoice ID?

Can you zip up the form and report into a sample database, along with the underlying queries and post them as an attachment?

Reply | Quote

I have the report pulling from a query. Is that the problem? Anyway, I’ve attached the database. If you have any other suggestions, I would greatly appreciate it. Thanks, Charlotte!

Ooooops! I can’t get the database file smaller that 346K (it has to be under 100K). Let me know what I should do.

Jen

Reply | Quote

Okay, Charlotte! I finally got it to work! I just put in a “Current Form” before the print preview part of the code.

Jen

Reply | Quote

Try decompiling the database and running compact and repair without recompiling or leaving the database. That sometimes knocks a bunch of space out, especially in Access 2000.

If you still can get it down to size, send me a copy by email (use the address in my profile). You’ll need to include the query and at least empty tables to support it, although the query alone might be enough. I’ll take a look at them and post my thoughts back here so everyone can benefit. If I can put a fix on either the form or report so it does work, I’ll post it back here as an attachment so you can grab it and run with it.

Reply | Quote

Hmm, it looks like our posts crossed. Glad you got it to work. If you have any more problems, post again.

Reply | Quote

Okay, I tried this again, Rupert. I can get it to print the invoices, but now I get a box asking for a Parameter value. What is that? And it still prints out all invoices. Aaaarrrggghhh!!!

Reply | Quote