Creating a report from the record I am on

[ferrisj]

I am creating a very small inventory database. I have a form that has user information on it, and a subform that has the inventory option they can fill. I am going to create a button from teh button wizard that says send report. I would like the report that is sent to be specific to the record I am on.

For example.

I find my record and my name is larry, When I fill in my inventory info and click send, I want a one page report to be sent that just shows larrys information, not everyone's.

Thanks

 

[Remou]

Use the "Where" argument of OpenReport.

 

[ferrisj]

ok, I may need a bit more explanation than that. sorry

 

[Remou]

Here is Microsoft on OpenReport:

http://msdn.microsoft.com/en-us/library/aa220304(office.11).aspx

You can post back your line of code if you run into problems.

 

[ferrisj]

I have:
Private Sub Command8_Click()
On Error GoTo Err_Command8_Click

Dim stDocName As String

stDocName = "rptInventory",
DoCmd.SendObject acReport, stDocName

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub
***********************************************

Do I type stDocName = "rptInventory", Where Currentrecord=ReportREcord

 

[Remou]

Sorry, I did not catch the 'send' part

You can create a report based on a query that references the current form. The SQL might look something like this:

[tt]Select Id, This, That From tblTable Where Id=Forms!frmForm!txtID[/tt]

Or

[tt]Select Id, This, That From tblTable Where Id=Forms!frmForm!SubFormControlName!Form!txtID[/tt]

 

[ferrisj]

And where in the above code do I type this?


Select Id, This, That From [Inventory] Where Id=Forms![Inventory Form]![Inventory subform]!Form![CODIGO USUARIO]

 

[Remou]

It should go in the query (SQL View) that the report is based on.

 

[ferrisj]

I am going to need this spelled out for me. the report is based on a table, and I do not write in SQL. I use the query builder.

 

[Remou]

One of the options for the query builder is SQL View (Design View, Datasheet View, SQL View), it is very useful, especially for sites such as this.

However, you can use the design grid to build a query from your table and just add the form reference to the criteria line:

[tt]=Forms![Inventory Form]![Inventory subform].Form![CODIGO USUARIO][/tt]

It is generally recommended that forms and reports are based on queries, rather than tables.

 

[ProgramError]

for those not familiar with VBA it is best working with the wizard to produce the code. It is much easier,

Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[Remou]

ProgramError
This is for SQL in the query design window, I think you must mean the Expression Builder yesno?