Need to make a report from a value on Form

[JC Mugs]

Problem:

Have a data entry form that enters new records that we need to print
invoices from when the form is completed.

I expect to be able to place a command button on the form and print the
invoice, but just can't seem to understand the correct way to pass the value
to the report and return to a new form.

The form uses tblInvoice as the data source, it has a primary key ID1
(autonumber). I think we should be able to use ID1 selection to print just
this record to rptInvoice1 (a report for a custom multi-part invoice), then
it should go back to the new invoice form.

Any suggestions? Perhaps someone knows of a good reference that shows the
way access handles data so that I can learn the proper way to pass values?

[Allen Browne]

Your command button needs something like this in its Click event procedure:

Private Sub cmdPrint_Click()
If Me.Dirty Then 'Save any changes.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to print.
MsgBox "Select a record to print."
Else
DoCmd.OpenReport "rptInvoice", acViewPreview, , "[ID1] = " &
Me.[ID1]
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JC Mugs" <jc****@hotmail.com> wrote in message
news:vv************@corp.supernews.com...

Problem:

Have a data entry form that enters new records that we need to print
invoices from when the form is completed.

I expect to be able to place a command button on the form and print the
invoice, but just can't seem to understand the correct way to pass the value to the report and return to a new form.

The form uses tblInvoice as the data source, it has a primary key ID1
(autonumber). I think we should be able to use ID1 selection to print just
this record to rptInvoice1 (a report for a custom multi-part invoice), then it should go back to the new invoice form.

Any suggestions? Perhaps someone knows of a good reference that shows the
way access handles data so that I can learn the proper way to pass

values?

[xzzy]

Dim g_WHERE as string

g_WHERE = " [ID1] = " & txtBOX
docmd.openreport "reportname",acviewnormal,,g_where
exit function

the above will print the report, listing only the matching records and leave
the invoice form open for further use

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"JC Mugs" <jc****@hotmail.com> wrote in message
news:vv************@corp.supernews.com...

Problem:

Have a data entry form that enters new records that we need to print
invoices from when the form is completed.

I expect to be able to place a command button on the form and print the
invoice, but just can't seem to understand the correct way to pass the value to the report and return to a new form.

The form uses tblInvoice as the data source, it has a primary key ID1
(autonumber). I think we should be able to use ID1 selection to print just
this record to rptInvoice1 (a report for a custom multi-part invoice), then it should go back to the new invoice form.

Any suggestions? Perhaps someone knows of a good reference that shows the
way access handles data so that I can learn the proper way to pass values?

[James Neumann]

JC,

One way to handle this would be to to construct a recordset in the
"Report_Open" event that reads the value on the form. You can store
this value in a global variable or preferably, a class. A sample of
some code doing this is below:

Private Sub Report_Open(Cancel As Integer)

Dim strSQL As String

strSQL = "SELECT [Employee_ID], " & _
"[Fiscal_Year], " & _
"[FY_End_Date], " & _
"[SumOfEnding_Balance] AS Ending_Balance_Net " & _
"FROM qryselCumulativeSummary " & _
"WHERE [Fiscal_Year] <= " & CStr(cGenSpecs.CurrentFY) &
";"

Me.RecordSource = strSQL

End Sub

"CGenSpecs.CurrentFY" stores the current fiscal year, and the query
gets salaries for a given fiscal year. You need to already have
defined a recordsource for the report, and it helps to have bound the
controls on the report - you can bind them at runtime, but the code
can get messy.

Another altearnative is to assign a filter to the report, in the same
area - perhaps something like this:

Private Sub Report_Open(Cancel As Integer)

Dim strEmpID As String

Me.Filter = "Employee_ID = " & g_strEmpID
Me.FilterOn = True

End Sub

"g_strEmpID" is (gasp!) a global variable, and yes, I was being lazy
by not creating a class.

You can assign some code to the form, perhaps on a command button, to
instantiate the report, either to print it out, or view it. Code
might appear as such, just be advised this has no vaildation or
anything like that:

Sub WTF

DoCmd.OpenReport "rptStatement", acViewNormal

End Sub
HTH,

JCN
"JC Mugs" <jc****@hotmail.com> wrote in message news:<vv************@corp.supernews.com>...

Problem:

Have a data entry form that enters new records that we need to print
invoices from when the form is completed.

I expect to be able to place a command button on the form and print the
invoice, but just can't seem to understand the correct way to pass the value
to the report and return to a new form.

The form uses tblInvoice as the data source, it has a primary key ID1
(autonumber). I think we should be able to use ID1 selection to print just
this record to rptInvoice1 (a report for a custom multi-part invoice), then
it should go back to the new invoice form.

Any suggestions? Perhaps someone knows of a good reference that shows the
way access handles data so that I can learn the proper way to pass values?

[Pieter Linden]

"JC Mugs" <jc****@hotmail.com> wrote in message news:<vv************@corp.supernews.com>...

Problem:

Have a data entry form that enters new records that we need to print
invoices from when the form is completed.

I expect to be able to place a command button on the form and print the
invoice, but just can't seem to understand the correct way to pass the value
to the report and return to a new form.

The form uses tblInvoice as the data source, it has a primary key ID1
(autonumber). I think we should be able to use ID1 selection to print just
this record to rptInvoice1 (a report for a custom multi-part invoice), then
it should go back to the new invoice form.

Any suggestions? Perhaps someone knows of a good reference that shows the
way access handles data so that I can learn the proper way to pass values?

Create the report as usual (unfiltered). then pass the filter you
want from your form in the Open event of the report. something like

dim strFilter as string
strFilter = "[MyField]=Me![Some Field]
DoCmd.OpenReport "rptMyReport",,,strFilter

If your VB editor is working right, you should be "prompted" for the
proper syntax. If you're having a problem, you can create a macro
that opens your report and then convert it to VB.