Form data -> report -> Help!!

[james_IT]

hi,

i have a form that i use to enter data.

once data is entered i want it to be printed as an invoice (report)

i have created the report template called EventInvoice.

how do i get the data from the form onto the report to preview/print?

i have managed to do it for the first record but it wont work for others. also only want the one record (event) details from the form to be shown and printed, not 1 of 100 different events, if that makes sense.

thanks

 

[RuralGuy]

Look up OpenReport in VBA help (<ALT> F11 then F1). It has a WhereCondition just like the OpenForm command.

 

[james_IT]

i can get the first record though. its just i want it to display the details from the open form only and NOT the first record in the table. ive tried looking in the help section and tried a different control source etc. no luck with it, any other ideas?

thanks

 

[RuralGuy]

As I said, put a WhereCondition clause in your OpenReport that limits the recordset to the current one in the form.

 

[RuralGuy]

What are you doing to get *only* the first record in the table? Your report should be bound to a generic query that returns *all* of the records and then you limit the recordset with a filter when you open the report.

 

[james_IT]

im not too sure to be honest...i just made a query that uses all the fields in my form and based my report of that.

i understand about using the where clause but im not sure how the code should look. what is it for criteria? is it the filter?

thanks for your help

 

[RuralGuy]

James,
Your OpenReport line will look similar to the OpenForm line I gave you in this thread.

 

[james_IT]

been giving it a few tries now...cant get it right....what should it look like now?


Private Sub Report_Open(Cancel As Integer)

Dim strWhere As String

strWhere = "[EventID] = " & Me.Filter

DoCmd.OpenForm "frmEditEvent", strWhere
DoCmd.Close acForm, "frmEditEvent"

End Sub

this doesnt work

thanks again

 

[RuralGuy]

Don't you have a line somewhere that starts with:
DoCod.OpenReport...

Post it along with the code around it please. Your code does *not* belong in the Report but in the form that opens the report.

 

[james_IT]

Private Sub PreviewButton_Click()
On Error GoTo Err_PreviewButton_Click

Dim stDocName As String

stDocName = "EventInvoice"
DoCmd.OpenReport stDocName, acPreview

Exit_PreviewButton_Click:
Exit Sub

Err_PreviewButton_Click:
MsgBox Err.Description
Resume Exit_PreviewButton_Click

End Sub

Private Sub PrintButton_Click()

----------------------------------------------------------

On Error GoTo Err_PrintButton_Click

Dim stDocName As String

stDocName = "EventInvoice"
DoCmd.OpenReport stDocName, acNormal

Exit_PrintButton_Click:
Exit Sub

Err_PrintButton_Click:
MsgBox Err.Description
Resume Exit_PrintButton_Click

End Sub


----------------------------------------------------------


these are the preview and print codes on my form used to EDIT existing records. will the code be the same (apart from the form name) to preview and print from the form used to enter NEW records?

thanks again

 

[RuralGuy]

Very similar but add a WhereCondition argument to the command.
1st build your WhereCondition string. You will have to decide what the WhereCondition statement needs to be.
strWhere = "[EventID] = " & Me.Filter
DoCmd.OpenReport stDocName, acViewNormal , , strWhere
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

 

[james_IT]

ok, i re-done the code. except now when i preview the report it doesnt show the first record, it shows nothing except the template. im confused! how should the report have been created? based on tables or a query? cos no matter how i create it it only shows the first record (event) and there is only one page

thanks

 

[RuralGuy]

I base all of my forms and reports on queries but both forms and reports will work with tables as well. The results you describe indicate no records match the WhereCondition you created. What WhereCondition did you create?

 

[james_IT]

ive tried a few things....

i want it to display the results on the current form

strWhere = "[EventID] = [EventID]" & Me.Filter

Perhaps? i have no idea now

 

[RuralGuy]

You need to specify the unique field in the RecordSet that will identify your current record. If it is a numeric field then:
strWhere = "[EventID] =" & Me.txtEventID
...if it is a TextField then:
strWhere = "[EventID] =" & Chr(34) & Me.txtEventID & Chr(34)
I have no idea what your recordset looks like so you will have to determine what field to use.

 

[james_IT]

FANTASTIC!!! you have no idea how much you have helped me the past couple of days.... so much thanks! i will be sure to pass knowledge on! thanks again

 

[RuralGuy]

It sounds like you go things working. That's great.