unable to empty text boxes

[lawdy]

The following function works good to make the text boxes visible on my form. But, when I add the line *shown in bold text below) to 'Empty' the values in the text boxes, I get error code 438, object doesn't support this property.

Public Function modMenu_unhideTextBoxes() '   unhides develement text boxes on frmAMainMenu    Dim strBox As String
    
    Dim db As Database
    Dim rst As Recordset
    Set db = DBEngine(0)(0)
    Set rst = db.OpenRecordset("mtQueryMenu", dbOpenDynaset)    '   a table created from a query, containing _
                                                                    the items to make visible and empty the _
                                                                    values in each
               With rst
                While Not .EOF
                    strBox = ![fStrTextBoxName]
                    Forms![frmAMainMenu].Controls(strBox).Visible = True
                    Forms![frmAMainMenu].Controls(strBox) = Empty
                    .MoveNext
                Wend
            End With
    
    rst.Close
    Set rst = Nothing
    Set db = Nothing


End Function

[davegri]

Looks like 'Empty' is undefined. So,

Dim Empty as string : Empty = ""

or just

Forms!frmAmainMenu.Controls(strBox) = ""

If you have the line
'Option Explicit' as the second line of your code module, you would have received a compile time error stating that 'Empty' was an unknown object.

[Welshgasman]

It ia called a language for a reason.
You cannot just go and make your own names up without doing something like davegri mentions in post #2

[Gicu]

Usually it is preferred to use Null instead of zero length string "" so your line will simply become:

Forms!frmAmainMenu.Controls(strBox)=Null

Cheers,

[Micron]

"Empty" doesn't seem to be in the list of Access reserved words (although it is a Word reserved word). Yet apparently you can inquire in the immediate window as
? Empty = ""
or
? Empty = 0 and both are True. So I don't think I'd use it as a variable, table or field name.

[June7]

Immediate window is just seeing Empty as a variable, possibly Variant. Can do the same with x:

?x = ""
True

?x = 0
True

[lawdy]

I wasn't trying to make up my own code. "Forms![frmCalendar].Form![tbLineNumber] = Empty" will work. I use 'empty' like that all the time. I just wanted to make "Form![tbLineNumber]" a variable to save lines of code.

Using

Forms!frmAmainMenu.Controls(strBox) = Null
Forms!frmAmainMenu.Controls(strBox) = ""

bothe produce the same error 438 - Object doesn't support this property or method.

[Micron]

You may be able to eliminate some repetition by assigning an object to a variable, but I don't see how you're going to eliminate lines of code by doing just that. Each line that needs to be written using the full reference will simply need to be written using the variable. One way to avoid repeated referencing is to use a With block for that reference, but that doesn't eliminate the lines you need to act on that referenced thing. If that doesn't help, perhaps post an example of what you have that you are trying to condense.

[davegri]

If mtQueryMenu contains names of objects that do not have a .value property, such as labels, it will produce the 438 error.

Still suggest you include 'Option Explicit' in your code modules. 'strBox' is not an explicitly defined object.

[lawdy]

Ok davegri , you solved it. I did have a label in the table mtQueryMenu. I deleted that object from the table and all methods works, = Empty, = Null, and ="" the same. You also suggested that I
include 'Option Explicit' in my code. Please tell me what you mean by that.

[davegri]

include 'Option Explicit' in my code. Please tell me what you mean by that.

Lots of examples via google like

Option Explicit statement (VBA) | Microsoft Learn

[Welshgasman]

I wasn't trying to make up my own code. "Forms![frmCalendar].Form![tbLineNumber] = Empty" will work. I use 'empty' like that all the time. I just wanted to make "Form![tbLineNumber]" a variable to save lines of code.

Well I would say that is making it up as you go along.