Error 438 - Object doesn't support this property... 1

[annie52]

I get Error #438 (object doesn't support this property or method) when I run the following code.

If Len(Me![ReqnWatch.NSN]) < 12 Then
MsgBox "The NSN you entered in invalid.", _
vbOKOnly, "RPP Tracking System"
Me![ReqnWatch.NSN].Undo
Me![ReqnWatch.NSN].BackColor = 255
End If

 

[addy]

I assume this is a text box that people enter information into? If so, why not just set it back to null rather than using undo?

Me!ReqnWatch!NSN = Null

 

[Remou]

What is this?

ReqnWatch.NSN

You cannot have stops in control names, so to what object(s) are you referring?

http://lessthandot.com

 

[annie52]

Hi, Addy. I had already tried setting it to null but got the same error.

Hi, Remou. I have two NSN fields on the form. One originates with the NSN table and the other originates with the ReqnWatch table. When designing my form, Access named the fields [NSNs.NSN] and [ReqnWatch.NSN].

When I enter a stock number in the [ReqnWatch.NSN] field, the system looks for a match in the [NSNs.NSN] field (i.e., primary key in the NSN table). If it's not found, I move the value of the [ReqnWatch.NSN] field to a variable, clear that field, move the variable's value to the [NSNs.NSN] field and also back into the [ReqnWatch.NSN] field. This part works well.

If IsNull(Me![NSNs.NSN]) Then
MyNSN = Me![ReqnWatch.NSN]
Me![ReqnWatch.NSN] = ""
Me![NSNs.NSN] = MyNSN
Me![ReqnWatch.NSN] = MyNSN
End If

I don't understand why I can set the field to nothing in this snippet of code but not in the code I'm complaining about. They both reside in the BeforeUpdate event of the [ReqnWatch.NSN] field.

 

[Remou]

You can Undo a control, but not a field. I think you will find that Access has named the control something like ReqnWatch_NSN, because you cannot have stops in the names of controls. This:

Me![ReqnWatch.NSN] = ""

Refers to the field, not the control.

It is usually best to use aliases in you query when you have two fields with the same name.

I think you have have some problems with your design. Have you considered a form and subform? If you set up a form and subform properly with link child and master fields, Access will fill in the foreign key (child field) of the table bound to the subform with the key (master field) from the main form. It can get more interesting, but that is a basic set-up.

http://lessthandot.com

 

[annie52]

Remou, excellent analysis and feedback. Absolutely deserving of a star!

[ReqnWatch.NSN] and [NSNs.NSN] were actually the source names not the control names.

I took your advice and edited my queries with aliases so I now have NSN and ReqnNSN as the control names. It sure is a lot less confusing this way.

Actually, I have four subforms contained in a tab control. They are all linked to the parent form by ReqnID. The NSN has nothing to do with the subforms. When the user types in an NSN into the ReqnNSN field on the parent (main) form, that value is linked to a lookup table of NSNs. If a match is found, several fields on the main form are populated by the NSNs table.

Maybe an Insert Into statement would be nicer, but I'm really awful at writing and placing SQL statements into VBA. I ended up putting this in the form's OnError event:

If Err.Number = 0 Then
Me!NSN = Me!ReqnNSN
Response = acDataErrContinue
End If

The part I'm struggling with is validating the entry:


If Len(Me.ReqnNSN) < 12 Then
MsgBox "The NSN you entered in invalid.", _
vbOKOnly, "RPP Tracking System"
Me.ReqnNSN.SetFocus
Me.ReqnNSN.Undo
Me.NSN.Undo
End If

I cannot get the Undo or SetFocus to work for me. I get confused about when to use the period as opposed to the exclamation point so I tried several variations of these statements but no luck.

 

[Remou]

If you have a lookup table, are you using a combo or listbox? These will make your life a lot easier, and you can use the NotInList event, which allows a return value. You will find examples in help for NotInList, or post back.

http://lessthandot.com

 

[annie52]

Hi, Remou. I have used the NotInList before and I'm not sure why I didn't go that way this time. And yes, the NSN is a combobox in the table. Thanks for the reminder.