Error creating report from one ID on form with command button

[jdlantz]

I know there is a lot written on this topic but I can't get it to work. I've tried three methods but still no success. The goal is to be able to have a command button on a form that will open the report filtered only to the ID viewed in the form. I'm using Access 2013. The form is titled "Entry_Form" and shows personnel profile entries. The ID field is "ID". The report is titled "Profiles".

I've tried three different ways by reading in the forums and online. I don't really care how I do this, as long as it works fine for end users. If you can correct my syntax in one of these or give me another way, I would sure appreciate the help! The three code strings I've been trying to make work and their results are pasted below.

Thanks,
jdlantz

>>TRIAL 1 - design a macro to run on event click for the button. My macro design is:
Open Report
Report Name: Profiles
View: Print Preview
Filter Name: [I left this blank]
Where Condition: = [ID]=[Forms]![Entry_Form]![ID]
Window Mode: Normal
>>RESULT: the report opens blank
(NOTE: I tried enclosing some parts of the where condition in "" but didn't get it to work)



>>TRIAL 2 (code builder on event click)
Private Sub cmdPrint_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.


Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "Profiles", acViewPreview, , strWhere
End If
End Sub
>>RESULT: "Run-time error '438': Object doesn't support this property or method"


>>TRIAL 3 - run code builder on event click
Private Sub printrecord_Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "Profiles"
strWhere = "[ID]=" & Me!ID
DoCmd.OpenReport strDocName, acPreview, strWhere
End Sub
>>RESULT: "Run-time error '438': Object doesn't support this property or method"

[June7]

Very odd. The code looks good (except the last where acPreview should be acViewPreview and missing a comma).

If you want to provide db for analysis, follow instructions at bottom of my post.

[jdlantz]

Thanks. I tried correcting the syntax on the third one but still didn't work. I tried creating a brand new database with similar requirements and got the first option to work in the new database. I copied my syntax into my current database and it doesn't work. Maybe I have a problem somewhere in the coding that is conflicting?

I created a sample database, having removed the personal data. Open the design view of the "Entry_Form" and you can see at the bottom the button I am experimenting with and click to see the code in the on event click.

Thank you!

[June7]

I suggest removing 0 from the DefaultValue property of the ID field in Database table. This is not an autonumber field. Are you relying on the user to correctly input a new ID value? Looks like Females are assigned numbers through 4999 and males from 5001. I would not do this.

Automatic generation of custom unique identifier is a common topic in forum. If you want, try DMax("ID", "Database")+1 as the DefaultValue or use VBA to populate the field and lock the textbox against user edit. This will not consider gender in assigning ID.

The code is not finding the ID because the textbox is not named ID and use of the dot (.) tells VBA to look for a control. I know ID still shows up in the intellisense popup but code execution still looks for a control by that name. Use bang (!) to reference fields. Use:

Me!ID

or

Me.Text123

Really should give controls meaningful names, like: tbxID

[jdlantz]

Thank you thank you!!

The ID column were in-organization reference numbers (not autonumber) and the primary key had been changed to that. I didn't think about that being the problem. I added a basic autonumber primary key "EntryID" and changed the other to a simple reference column. Using the new primary key the VBA code works great! I'll also give names to my controls.

Thank you very much!