Run-Time error 438 - Object doesn't support this .... (1 Viewer)

[TyBement]

Hi all, I am currently taking a class on Access and completely stumped on a VBA issue I am having. After talking to my instructor, I came to find out my code SHOULD BE working....

My task is to modify a report to suppress the printing of three controls that are based on a table when the control value is null. The controls are supposed to be suppressed ONLY when you print the report or view the report in Print Preview.

Here is the code I am using.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(PaymentID) Then
Me![PaymentID].Visible = False
Me![PaymentAmt].Visible = False
Me![PaymentDate].Visible = False
Me![lblPaymentID].Visible = False
Me![lblPaymentAmt].Visible = False
Me![lblPaymentDate].Visible = False
Else
Me![PaymentID].Visible = True
Me![PaymentAmt].Visible = True
Me![PaymentDate].Visible = True
Me![lblPaymentID].Visible = True
Me![lblPaymentAmt].Visible = True
Me![lblPaymentDate].Visible = True
End If
End Sub

It compiles fine but when I click out of design view and into Print Preview I get the "Run-Time error 438."

As I said, I am taking a class so I am incredibly green when it comes to VBA code but any help would be appreciated.

 

[CJ_London]

which line do you get the error on?

usual convention is to use me., not me! - there are plenty of threads on the subject on this and other forums of 'dot v bang' - here is an example https://access-programmers.co.uk/forums/showthread.php?t=202806

in essence

Use Me. (dot) when you want to refer to controls, properties or methods of the form.

Use Me! (bang) when you want to refer to fields in the form's recordsource.


probably not relevant but if paymentID is an autonumber, it can never be null

 

[Orthodox Dave]

I have removed the following section from my response:

CJ_London's last sentence holds the clue I think. In fact if PaymentID is an Integer or a Long Integer, it cannot be null. You need to test for zero or empty:

If IsEmpty(PaymentID) or PaymentID = 0 Then

I tested a simple report I have with the code using dots, bangs, with and without square brackets[] and with and without "Me" and they all worked, although we should stick to the conventions. I'm just saying I don't think they are the source of your error.

 

[missinglinq]

Sorry, Dave, but Integer/Long Integer Fields can, indeed, be Null...only Autonumber Fields cannot, as CJ stated.

Also IsEmpty() can't be used against Controls or Fields...it applies to Variables, indicating whether they have been initialized.

Linq ;0)>

 

[Orthodox Dave]

Thanks for putting me straight missinglinq. Grasping at straws perhaps.

Meanwhile TyBement has no solution to a problem that looks simple on the face of it. Could it be that a library reference is missing?

 

[CJ_London]

have you tried the solution in post #2

 

[missinglinq]

As CJ has repeatedly suggested, try replacing

Me![PaymentID], Me![PaymentAmt], etc.

with

Me.[PaymentID], Me.[PaymentAmount], etc.

You probably should also replace

If IsNull(PaymentID) Then

with

If IsNull(Me.PaymentID) Then

Linq ;0)>

 

[Galaxiom]

usual convention is to use me., not me! - there are plenty of threads on the subject on this and other forums of 'dot v bang' - here is an example https://access-programmers.co.uk/forums/showthread.php?t=202806

in essence

Use Me. (dot) when you want to refer to controls, properties or methods of the form.

Use Me! (bang) when you want to refer to fields in the form's recordsource.

Although it works in practice, I have reached a more precise understanding in the seven years since I contributed to that thread.

The dot is an Early Bound reference to a member of the object. The bang is a Late Bound reference to the default member of the object. The dot is required for Properties and Methods because they are not the default members.

The binding difference can be experienced by using the bang with an invalid control name in code on a form module. The code will compile without errors but breaks at runtime when it hits the invalid control name. An invalid reference with a dot won't compile due to the early bind.

Consequently it would be best to use the dot in most places where it works so that any invalid names are detected during the compile. (BTW Note that the Compile command does not become available until at least one dot reference is used. Code that only has bang references has nothing to compile due to late binding.)

Me. and Me! both work provided the member is valid. Once again the dot reference will be checked for validity of the member during Compile.

Forms. and Forms! both work. Since an instance of a form is not a item in the Forms Collection until runtime, the late bound bang is technically more appropriate.

However in this case, Compile doesn't check either syntax for the validity of the item, so an invalid form or member is only ever found at runtime. There is a subtle difference between the dot and the bang that further elucidates the difference. With a dot, an invalid form name will raise a 438 error, 'Object does not support this property or method'. A bang raises a 2450 error, 'Microsoft cannot find the referenced form'.

The fact that members can be tested within their parent's module during compile suggests an advantage to moving as much code as possible to the target object's module when making references from another object.

For example, references to controls on another form cannot be checked during compile because the item in the Forms Collection is a dead end until runtime. Rather than referring to multiple controls from the current form within the same operation to obtain information from another form, it would be better to process that information on the other form and pass it back as a single custom Property. Of course the remote reference to this Property cannot be tested during compile either but it does reduce the potential for invalid references to a single member.

Another place it does make a practical difference with forms is in the query designer. The bang will initiate Intellisense but the dot won't. I think this behaviour was introduced in Access 2010.

 

[isladogs]

Hi Galaxiom

Thanks for that detailed explanation about dot vs bang (or dong vs bat as one of my ex-colleagues always used to say)
Some of it has gone over my head (again) but I will read it again later to take it in properly

However the last paragraph isn't correct

Another place it does make a practical difference with forms is in the query designer. The bang will initiate Intellisense but the dot won't. I think this behaviour was introduced in Access 2010.

Not true. Both dots & bangs trigger intellisense. Here's a dot example (in A2010)

 

[Galaxiom]

Both dots & bangs trigger intellisense. Here's a dot example (in A2010)

The image shows Intellisense providing the list of fields from the table.

I was referring to Intellisense from the Forms Collection. In my experience, only the bang triggers a list of form names.

Another place it does make a practical difference with forms is in the query designer.

(Emphasis added.)

 

[isladogs]

The image shows Intellisense providing the list of fields from the table.

I was referring to Intellisense from the Forms Collection. In my experience, only the bang triggers a list of form names.

(Emphasis added.)

I agree that only the bang works when using the Forms collection e.g in the expression bulder

 

[The_Doc_Man]

Guys, all of this is fine but Ty still has a problem, I guess. He hasn't come back and I hope you didn't scare him off.

TyBement - when you get the error, you SHOULD also get a pop-up for which DEBUG is one of the options. If you click that, which line is highlighted? Because THAT will narrow down our speculation considerably.