Run time error '438' (1 Viewer)

Johnny Drama · Sep 7, 2011

Hello all,

I'm using the code below to filter and open a report in access 2007. When the code fires I get the following error message:

Run time error '438': Object doesn't support this property or method. Debug goes the line in bold below. I'm not a VBA coder by an means, so any help would be appreciated. Thanks

Private Sub Owner_Name_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Email Exceptions By Owner"
stLinkCriteria = "[Assigned To]='" & Me![Owner Name].[ID] & "'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
End Sub

CBrighton · Sep 7, 2011

What type of field is it?

's go around text, if the ID field is a number then you just need: stLinkCriteria = "[Assigned To]=" & Me![Owner Name].[ID]

Johnny Drama · Sep 7, 2011

Your question made me realize that I had a numeric field and that I had it in the wrong place. This is what I have now:
stLinkCriteria = "[Assigned To].[ID]=" & "Me![Owner Name]"

however when I run it I get asked for the criteria for these two items, which I don't want. I just want it passed from the combo box (Owner Name) to the Assigned To control on the report.

**scratching head**

CBrighton · Sep 7, 2011

Anything in "s will be taken literally, the text not the contents of it.

So "Me![Owner Name]" means you are looking for something with "Me![Owner Name]" in the field, rahter than the value of the control called Owner Name.

What is the name of the field in the table & the control on the form?

Johnny Drama · Sep 7, 2011

The control name for the combo box on the form is Owner Name and it is pulling from a query: SELECT [Owner Extended].[Owner Name] FROM [Owner Extended];

The Owner Name is then passed to the report control named Assigned To which is numeric

CBrighton · Sep 7, 2011

You need the fieldname in the table (the first part) and the control name where you are getting the criteria (which i guess is the combobox).

Given the name & SQL from the combobox you should use the following:

Code:

[B]stLinkCriteria = "[Owner Name]=" & Me![Owner Name][/B]

Johnny Drama · Sep 7, 2011

Hummm...now I get a run-time '3075' error: Syntax error (missing operator) in query expression '[Owner Name]=Test User'.

CBrighton · Sep 8, 2011

"Test User" is not numeric, that's why I asked the data type earlier.

Change it to this:

Code:

[B]stLinkCriteria = "[Owner Name]='" & Me![Owner Name] & "'"[/B]

Johnny Drama · Sep 8, 2011

Okay, that fixed the syntax error, but now after I make my selection in the combo box I get a criteria box popping up. If I fill it in with the same name I selected it opens the report without filtering it. If I just click okay on a blank criteria or put something other than what I selected it opens an empty report. This is giving me a headache.

CBrighton · Sep 8, 2011

Sounds like the rowsource of the combobox includes a field name which is not in the tables it is accessing or there is a piece of criteria which is mistyped / designed to be manually input.

What exactly does the input box say? Because if the message at the top of the input box is a slight misspelling of a field / control then there's your problem.

Johnny Drama · Sep 8, 2011

Hummm....that may be part of the problem. The combo box is pulling from a Query and not a table. The reason being is that the table has fields for last name and first name, but I want the user to be able to select the full name from the combo box. The query that the combo box pulls from has the field: Owner Name: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) so that the first and last names are joined as one.

The criteria box asks for Owner Name, which is spelled correctly.

CBrighton · Sep 8, 2011

OK, can you post the rowsource for the combobox (or if the rowsource is just a query name then the SQL of that query)?

Johnny Drama · Sep 8, 2011

Certainly. SELECT [Owner Extended].[Owner Name]
FROM [Owner Extended];

CBrighton · Sep 8, 2011

I assume Owner Extended is another query?

What happens if you use the SQL you just posted as a new query (change from design mode to SQL mode and paste it in, or just click the "..." build button on the row source then change view modes)? Does the Access query builder also prompt you for criteria or does it correctly give you the list of names?

Johnny Drama · Sep 8, 2011

Yes, Owner Extended is another query. I also made a slight change to that query so that it would pull in the ID, and made a change to the combo box so it would pull in the ID also. I then passed that to the report and it now works.

Thanks for all of your help. I wouldn't even have known where to start without it...with it I started poking around in the right direction, and using your suggestions helped me fix it. Thanks a ton! I've spent way more time than I've wanted to on this and appreciate all the help you've given me!

Run time error '438' (1 Viewer)

Johnny Drama

In need of beer...

CBrighton

Surfing while working...

Johnny Drama

In need of beer...

CBrighton

Surfing while working...

Johnny Drama

In need of beer...

CBrighton

Surfing while working...

Johnny Drama

In need of beer...

CBrighton

Surfing while working...

Johnny Drama

In need of beer...

CBrighton

Surfing while working...

Johnny Drama

In need of beer...

CBrighton

Surfing while working...

Johnny Drama

In need of beer...

CBrighton

Surfing while working...

Johnny Drama

In need of beer...

Similar threads

Users who are viewing this thread