Runtime error 91 on "exit function"

[eellenoff]

I'm used to seeing runtime error 91 ("Object variable or With block variable not set") when I try to say

var = object

instead of

set var = object

however, the following code uses set, and the error only occurs when I try to exit the function:

' Find a control for the given string name
Private Function getCtl(field As String, frm As Form) As Control
    Dim ctl As Control
    For Each ctl In frm
        If ctl.Name = field Then
            Set getCtl = ctl
            Exit Function
        End If
    Next ctl
    
End Function

the line Set getCtl = ctl is being properly executed, and when I try to exit the function getCtl is still properly set. Any ideas why I'm hitting this error? Thanks.

-Eric

 

[RuralGuy]

You might start by not using the reserved word FIELD in your code: http://www.allenbrowne.com/AppIssueBadWord.html#F

 

[eellenoff]

Thank you - certainly an excellent suggestion.
However, changing that did not change the behavior regarding the error.

 

[boblarson]

Why are you setting the function to be a control? What is the purpose? I think there may be a better way but I don't know for sure because I don't know how you are trying to use this.

 

[eellenoff]

The problem that I'm trying to solve is:

I'm using an unbound form to create a "diff" style page for resolving conflicts in records. I wanted to automate the population of fields from the query I'm using, so I'd like to be able to loop through the fields in the query and say:
I have a field named X and I want the control which is mapped to that field. By the convention I've used, the name of a control mapped to field X will be X. Thus, my goal was to loop through all the controls in the form until I find the control whose name is X.

From an efficiency perspective I realize this is horrific (but in reality that doesn't seem to matter), and I'm realizing that the code I based that off of was written by someone who hadn't discovered the Dictionary object. The better version of what I'm doing would seem to be to initialize a Dictionary where the name of each field maps to the control for that field, but this still seems like a manual solution to what I would think is a pretty common problem.

I'm certainly open to better solutions if you have them!

Thanks,
_Eric

 

[boblarson]

If you are using an unbound form, then setting the controls to a field will bind it. So, if you want to truly keep it unbound then it would require other code. Otherwise you should be able to use this:

Function SetControls(frm As Form, strQueryName As String) 
Dim rst As DAO.Recordset
Dim fld As DAO.Field
 
frm.RecordSource = strQueryName
 
Set rst = frm.RecordsetClone
 
For Each fld in rst.Fields
     frm.Controls(fld.Name).ControlSource = fld.Name
Next
 
rst.Close
Set rst = Nothing

 

[eellenoff]

Thank you. I rather feel like a fool. When I typed "Controls(" the tooltip or intellisense or whatever it's called informed me that the argument should be an index which I assumed had to be numeric. It definitely makes much more sense that the controls are indexed by name. Thank you very much for clearing that up for me.

-Eric

 

[boblarson]

Thank you. I rather feel like a fool. When I typed "Controls(" the tooltip or intellisense or whatever it's called informed me that the argument should be an index which I assumed had to be numeric. It definitely makes much more sense that the controls are indexed by name. Thank you very much for clearing that up for me.

-Eric

For some items you can use the name OR the index and this is one of them. But it doesn't work for all things.