"Property let procedure..." error occurs when opening form determined by listbox data

[dstorms]

Hi, I'm trying to create a form with a list box with 2 columns and a button that opens the selected record in the appropriate form. In the sample below, the first column lists the type of equipment (labeled Category) and the second column lists the equipment's serial number (SerialNo). The sub is supposed to use the Category data (say, Cameras) to select the appropriate form (in this case, AcctgDECameras) with the record containing the matching serial number.

Unfortunately, I get this error instead:
"Property let procedure not defined and property get procedure did not return an object."

I suspect the problem lies in the mutliple "ElseIf" statements.

Here's the code:

Dim stDocName As String
Dim stLinkCriteria As String
Dim stCategory As String
 
stCategory = Me.AssetList.Column(0)
 
If stCategory = "Computers" Then
stDocName = "AcctgComputers"
ElseIf stCategory = "Cameras" Then stDocName = "AcctgDECameras"
ElseIf stCategory = "Monitors" Then stDocName = "AcctgDEMonitors"
ElseIf stCategory = "MFCs" Then stDocName = "AcctgDEMFCs"
ElseIf stCategory = "Printers" Then stDocName = "AcctgDEPrinters"
ElseIf stCategory = "Docks" Then stDocName = "AcctgDEDocks"
ElseIf stCategory = "PDAs" Then stDocName = "AcctgDEPDAs"
ElseIf stCategory = "Other DE" Then stDocName = "AcctgDEOthers"
End If
 
stLinkCriteria = "[SerialNo]=" & "'" & Me![AssetList]!Column(1) & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
 

Suggestions would be welcome!

[FishVal]

Hi, dstorms.

Try to replace

stLinkCriteria = "[SerialNo]=" & "'" & Me![AssetList]!Column(1) & "'"
 

with

stLinkCriteria = "[SerialNo]=" & "'" & Me![AssetList].Column(1) & "'"
 

Regards,
Fish

[Denburt]

After reviewing the code you posted, I would start out by debugging the procedure and see if strDocname actually contains a valid form name.


Debug.print stDocName
DoCmd.OpenForm stDocName, , , stLinkCriteria

[dstorms]

FishVal found the error. Now it works! Thanks to both for responding.

[FishVal]

FishVal found the error. Now it works! Thanks to both for responding.

Hi, dstorms.

Hope you understand difference between "." and "!" operators.
They are completely different.

• "." is for referencing object's property/method
• "!" is for referencing collection's member
  expression like
  

  CollectionName!CollectionMemberName
   

  is a full equivalent of
  

  CollectionName.Item("CollectionMemberName")
   

  the only difference is that the first is faster and the second is more flexible
  • the first is a static reference which allows compiler to resolve the reference before execution thus optimizing code performance
  • the second is a dynamic reference allowing to get collection member by a string type Item property argument

People are getting confused because in many cases one may be used instead of other without any noticeable difference. This is not some special feature of VBA syntax, it is a feature of Access object model.

For example:

#1. Me.ControlName
and
#2. Me!ControlName

this will give a reference to the same control but via different routes.
Some words about "Me".

• "Me" is a special name returning object the executing code belongs to
• Each time a new form is created Access creates a new class Form_FormName (you see it in project explorer under forms' modules
• So in a form module "Me" returns object of Form_FormName class
• Form_FormName class inherits all properties of Access.Form class and has it own (for example Form_FormName.ControlName used in #1)

• the first expression will call property ControlName (see above) which will return reference to the control's object
• the second works in the following way
  • Form_FormName is not a Collection, thus using "!" operator seems to be senseless
  • But inheriting Access.Form class, Form_FormName class inherits its default property Controls which returns collection.
    Thus
    

    Me!ControlName
     

    is just the same as the following with explicitely called default property
    

    Me.Controls!ControlName
     

Best regards,
Fish

P.S. If that makes a sense you may see clearly what was wrong in your original syntax.