Query To Produce Single Report Page

[Colin]

Hi,
I have created a database with two tables, Customers
and Jobs.

With a single form with a subform, to enter the details -
Customers is the form, and Jobs is the sub-form.

I have also created a report where the deatils of the job
and customer are combined into one page to print out as
an invoice.

The problem is that the report brings up every invoice in
the database. I only ever want one at a time.

Can someone explain to me how the button on the Northwind
example, in the Orders form works? - the Print Invoice
button. It somehow only opens the report for the order
being viewed and prints that invoice only.

Any help is appreciated, I have maanged to put a button
on the Jobs form which when I click it it prints out all
the invoices but I want to make it so that it will only
print out the invocie for the job being viewed.

Thanks

Colin

 

[tina]

when deconstructing somebody else's db, you have to follow the code.
in the Northwind.mdb, open the Orders form in design view, click on the
Print Invoice button, and click the Events tab in the Properties box to see
what's happening.
there's an Event Procedure in the Click event. click on the build button at
the right [...] to go to the VB Editor window and look at the code.
you'll see that DoCmd.OpenReport has a filter entered in the FilterName
argument. if you're not familiar with the OpenReport action/arguments, move
your cursor (I-beam) anywhere in the word OpenReport and press F1. Access
VBA Help will open to that topic so you can read up on it.
the filter is saved as a query named "Invoices Filter", so go to the Queries
tab in the db window, find that query, and open it in design view. you'll
see the criteria being used to filter the report records when the report is
opened from the command button on the form. that criteria references the
value in a control in the current record of the open Orders form.

hth

 

[Guest]

Hi,
Thanks for your help. I have got this far, I made
this code in VB, but when I click the button nothing
happens. Can you see whats wrong?

_______________________________

Sub PrintInvoice_Click()
On Error GoTo Err_PrintInvoice_Click

Dim strDocName As String

strDocName = "Invoice"
DoCmd.OpenReport strDocName, acViewNormal, "Invoices
Filter"

Exit_PrintInvoice_Click:
Exit Sub

Err_PrintInvoice_Click:
Const conErrDoCmdCancelled = 2501
If (Err = conErrDoCmdCancelled) Then
Resume Exit_PrintInvoice_Click
Else
MsgBox Err.Description
Resume Exit_PrintInvoice_Click
End If

End Sub

_____________________________________

Also do you need two queries? I notived in the
northfield, there is one called Invoices which has all
the fileds included, and there is another one called
invoices filter which just has Invoices and Order ID?

One final question, what does the [Forms]![Orders]
[OrderID] bit come from?

Regards

Colin

 

[tina]

see comments inline.

Hi,
Thanks for your help. I have got this far, I made
this code in VB, but when I click the button nothing
happens. Can you see whats wrong?

_______________________________

Sub PrintInvoice_Click()
On Error GoTo Err_PrintInvoice_Click

Dim strDocName As String

strDocName = "Invoice"
DoCmd.OpenReport strDocName, acViewNormal, "Invoices
Filter"

Exit_PrintInvoice_Click:
Exit Sub

Err_PrintInvoice_Click:
Const conErrDoCmdCancelled = 2501
If (Err = conErrDoCmdCancelled) Then
Resume Exit_PrintInvoice_Click
Else
MsgBox Err.Description
Resume Exit_PrintInvoice_Click
End If

End Sub

_____________________________________

are "Invoices" and "Invoices Filter" the correct names for your report and
filter objects in your db? and is PrintInvoice the name of your command
button? and does [Event procedure] show on the button's On Click event
property line in the Properties box in the form's design view?
also, when you say "nothing happens", do you mean absolutely nothing - as
though the button were dead? or do you get an error message, and if so what
exactly?

Also do you need two queries? I notived in the
northfield, there is one called Invoices which has all
the fileds included, and there is another one called
invoices filter which just has Invoices and Order ID?

no, you don't need two queries, that's just one possible solution. you can
enter the filter criteria directly in the WHERE clause argument of the
OpenReport action (see Access Help again). or you can put the criteria
directly in the query that backs the report; if you do this, though, 1) the
report can only be used to print one record and 2) only when that particular
form is open.

One final question, what does the [Forms]![Orders]
[OrderID] bit come from?

that's the field reference to the specific control in the open form, that
identifies the current record you want to print.

Regards

Colin

keep in mind that Northwind is a working database, so the code is specific
to the objects in that database. when "borrowing" that code, you have to
modify it to reference your db's objects.

hth

 

[Colin]

Hi,
Thanks for your help, i've got the button working
now that it only prints out the report for the form beng
viewed. Just one slight problem.

The form is set up with a subform, and it is the subform
that has the button to print the report.

When you open the subform its self, the button works
fine, but when you try to use the button when it is a
subform the message 'Enter Prameter Value' appears.

Any idea how to fix this?

Thanks

Colin

 

[tina]

pls copy/paste the code that's attached to the command button, so we can see
it.

 

[Colin]

Private Sub PrintCashSale_Click()
On Error GoTo Err_PrintCashSale_Click

Dim stDocName As String

stDocName = "CashSales"
DoCmd.OpenReport stDocName, acViewNormal, "CashSales
Filter"

Exit_PrintCashSale_Click:
Exit Sub

Err_PrintCashSale_Click:
MsgBox Err.Description
Resume Exit_PrintCashSale_Click

End Sub

 

[tina]

your CashSalesFilter has criteria referring to the subform as a form object,
or "stand-alone" form, as

[Forms]![FormName]![ControlName]

copy the filter (File, SaveAs from design view) with a new name. change the
criteria in the new filter to reference the subform object *as a subform of
the main form*, as

[Forms]![MainFormName]![SubformControlName].Form![ControlName]

note that the subform control name may be different from the subform name.

you still need to tell the command button which filter to use at any given
time. let me try to come up with an easy to explain/implement solution and
post back - maybe not til tomorrow.

 

[Colin]

Hi,

How do I find out the subform control name?

-----Original Message-----
your CashSalesFilter has criteria referring to the subform as a form object,
or "stand-alone" form, as

[Forms]![FormName]![ControlName]

copy the filter (File, SaveAs from design view) with a new name. change the
criteria in the new filter to reference the subform object *as a subform of
the main form*, as

[Forms]![MainFormName]![SubformControlName].Form! [ControlName]

note that the subform control name may be different from the subform name.

you still need to tell the command button which filter to use at any given
time. let me try to come up with an easy to explain/implement solution and
post back - maybe not til tomorrow.

Private Sub PrintCashSale_Click()
On Error GoTo Err_PrintCashSale_Click

Dim stDocName As String

stDocName = "CashSales"
DoCmd.OpenReport stDocName, acViewNormal, "CashSales
Filter"

Exit_PrintCashSale_Click:
Exit Sub

Err_PrintCashSale_Click:
MsgBox Err.Description
Resume Exit_PrintCashSale_Click

End Sub

.

 

[Colin]

Ahh,

Finally its working

Thanks for all your help

-----Original Message-----
your CashSalesFilter has criteria referring to the subform as a form object,
or "stand-alone" form, as

[Forms]![FormName]![ControlName]

copy the filter (File, SaveAs from design view) with a new name. change the
criteria in the new filter to reference the subform object *as a subform of
the main form*, as

[Forms]![MainFormName]![SubformControlName].Form! [ControlName]

note that the subform control name may be different from the subform name.

you still need to tell the command button which filter to use at any given
time. let me try to come up with an easy to explain/implement solution and
post back - maybe not til tomorrow.

Private Sub PrintCashSale_Click()
On Error GoTo Err_PrintCashSale_Click

Dim stDocName As String

stDocName = "CashSales"
DoCmd.OpenReport stDocName, acViewNormal, "CashSales
Filter"

Exit_PrintCashSale_Click:
Exit Sub

Err_PrintCashSale_Click:
MsgBox Err.Description
Resume Exit_PrintCashSale_Click

End Sub

.

 

[tina]

open the main form in design view.
press Alt+Enter to open the Properties box.
click on the subform *once* to select it.
in the Properties box, click the All tab.
the very first property should be Name. thats the name of the subform
control.
(fyi, note that the the next property is Source Object, which shows the name
of the subform itself.)

Hi,

How do I find out the subform control name?

-----Original Message-----
your CashSalesFilter has criteria referring to the subform as a form object,
or "stand-alone" form, as

[Forms]![FormName]![ControlName]

copy the filter (File, SaveAs from design view) with a new name. change the
criteria in the new filter to reference the subform object *as a subform of
the main form*, as

[Forms]![MainFormName]![SubformControlName].Form! [ControlName]

note that the subform control name may be different from the subform name.

you still need to tell the command button which filter to use at any given
time. let me try to come up with an easy to explain/implement solution and
post back - maybe not til tomorrow.

Private Sub PrintCashSale_Click()
On Error GoTo Err_PrintCashSale_Click

Dim stDocName As String

stDocName = "CashSales"
DoCmd.OpenReport stDocName, acViewNormal, "CashSales
Filter"

Exit_PrintCashSale_Click:
Exit Sub

Err_PrintCashSale_Click:
MsgBox Err.Description
Resume Exit_PrintCashSale_Click

End Sub



-----Original Message-----
pls copy/paste the code that's attached to the command
button, so we can see
it.

.