Button to open a report with ONLY that record number data in it

[Leopardfist]

I have a database which tracks people's data. I have reports which are set up so each different person has their own page, meaning the page of the report is per person. Right now I open the report, and browse pages to find the person I want to print out. I do need the report to open with all the people, as I print out new reports twice a week, for every person in the database, but I also want to be able to open it with just one records data.

What I want to do, is add a button to the form I use to enter all the data, and when it is clicked, it will open the report, but only that persons data which the form is showing, so I can simply click the button when I add a new person rather than having to browse through alphabetically to find the page, and print only that page to get the report for just that person.


Can someone help me with this?

 

[pbaldy]

Look at the wherecondition argument of OpenReport.

 

[Leopardfist]

How would I make the expression? I am not good with SQL Code stuff. All I would need is for it to open only the current record.

Where name = !me.name ????

 

[pbaldy]

Presuming you've looked in VBA help, what have you got so far?

 

[Leopardfist]

Well, I dont know any VBA, what I took from the other response was that I could make the buttons click event open a macro, which is where the openreport properties would allow me to use the wherecondition setting.

I have no clue how to make an expression, let alone make one to select only the currently displayed record in the form.


The button is on my form, so as soon as it is clicked, the event can then find out which record the form has loaded at that time. Once it knows that I can use my Primary Key as the reference to run the wherecondition.

Here is my buttons click event, set to open a report.

Private Sub Command236_Click()
On Error GoTo Err_Command236_Click

    Dim stDocName As String

    stDocName = "Special Housing Unit Range Log"
    DoCmd.OpenReport stDocName, acPreview

Exit_Command236_Click:
    Exit Sub

Err_Command236_Click:
    MsgBox Err.Description
    Resume Exit_Command236_Click

Can anyone tell me what to add to set it to open the report with ONLY the data from the currently viewed record of the form?

 

[Leopardfist]

I tried to do this by creating a separate report, built to open only a single record, using a different query as the data source. On my query I made the condition for number "= Me.Number" but it didnt work. When I clicked the button it threw up a parameter box asking for the value of Me.Number. Can you tell me how to do this expression? I dont know much about VBA or writing expressions.

 

[pbaldy]

Based on your earlier attempt:

DoCmd.OpenReport stDocName, acPreview, , , "Name = '" & Me.Name & "'"

 

[Leopardfist]

Thanks pbaldy, I put it in but I get an error now.

Private Sub NEWBUTTONTEST_Click()
On Error GoTo Err_NEWBUTTONTEST_Click

    Dim stDocName As String

    stDocName = "Special Housing Unit 292 Alpha Report"
    DoCmd.OpenReport stDocName, acPreview, , , "Number = '" & Me.Number & "'"

Exit_NEWBUTTONTEST_Click:
    Exit Sub

Err_NEWBUTTONTEST_Click:
    MsgBox Err.Description
    Resume Exit_NEWBUTTONTEST_Click
    
End Sub

It pops up a message box that says Type Mismatch.

 

[pbaldy]

You mentioned "Name" before. If it's a numeric value:

DoCmd.OpenReport stDocName, acPreview, , , "Number = " & Me.Number

 

[Leopardfist]

Actually the number field in the table is a text field, as it contains non numeric characters. An example is 12345-678. I did however try it both ways and it still says type mismatch in both cases.

Would it be better if I used a different field to do the select with? Could we use just the record number?

 

[Leopardfist]

TBALDY, MUCHO GRACIAS!!!

I went to msdn and looked up the openreport method, and found that I had one too many commas in the statement. It works perfectly now, and I very much appreciate your fast and good assistance!!!