Using Variable String to Set a Form object

[neo651]

I have, what I believe is, a pretty simple question but I'm just starting to learn about VBA so I needs some help.

I have a dialog form with a combo box that lists form names. I'd like to be able to select a form name, open that form and then modify some of its properties (AllowAdditions, DataEntry).



I've declared a Form but the problem I have is that I don't know how to set it to the form selected in the combo box.

Public Function SetDataEntrySettings()
Dim formObj As Form
Set formObj = Forms!frm_InitializeObject!cboObjectClassSelect.Column(1)
formObj.AllowAdditions = True
formObj.DataEntry = True
End Function

I'm fairly certain the part that fails is

Forms!frm_InitializeObject!cboObjectClassSelect.Column(1)

That refers to the combobox selection. But I don't know what the proper syntax is for setting a Form with a variable name.

Thanks in advance.

[Dal Jeanis]

Okay, so you have a combobox that has been loaded to the names of forms, and you want to take whatever form is selected, find that form, and then set formObj to point to that form, right? The form name is a text field, so you have to use the syntax that tells Access to find the form by that name in the collection.

As per this page, http://msdn.microsoft.com/en-us/libr.../ff195841.aspx, assuming that the form is open, you could try

 
Set formObj = Allforms!(frm_InitializeObject!cboObjectClassSelect.Column(1))

or

 
Set formObj = Forms!(frm_InitializeObject!cboObjectClassSelect.Column(1))

Also, this item - "frm_InitializeObject" could probably be replaced by "Me".

[neo651]

Alright so here's my new code. I'm running it from a Macro and when my form calls the macro a get an error message:

"The expression you entered has a function name that Microsoft Access can't find."

Then an error window for my macro with an error number 2950. My understanding is that this is typically a Trust issue but my database is in a Trusted Location and I even enabled all macros for shits and giggles and I still get that error.

Private Sub SetDataEntrySettings()
Dim formObj As Form
Set formObj = Forms!(frm_InitializeObject!cboObjectClassSelect.Column(1))
formObj.AllowAdditions = True
formObj.DataEntry = True
End Sub

[Dal Jeanis]

try this:

Private Sub SetDataEntrySettings()
Dim formObj As Form
Dim strFormName As String
strFormName = frm_InitializeObject!cboObjectClassSelect.Column(1)
Set formObj = Forms!(strFormName)
formObj.AllowAdditions = True
formObj.DataEntry = True
End Sub

Breaking the formname string resolution into a separate line will tell you whether Access is confused by possibly thinking Forms!() is a function, or whether frm_InitializeObject is insufficiently referenced. (Add Me. if needed)

[neo651]

Good idea, but it didn't work. Still getting that same error.

[Dal Jeanis]

Okay, at this point I'd be stepping through the code with a debugger.
Set a break on this line:

Set formObj = Forms!(strFormName)

and check what the value of strFormName is.
Hmmm. I wonder if you need to change that to

Set formObj = Forms!("""" &strFormName & """")

[ssanfu]

PMFJI,

I pretty sure that the form has to be open to change the properties. I know if the form is open, I can set the data entry mode by using a button on a form with

Me.DataEntry = True

But this setting lasts only until the form is closed. If you want to change the property permanently, you would have to open the form in design view, change the property, then save the form. You could also open the form hidden, if you didn't want to see the design view of the form. (Not sure where you are headed with the form settings).

Once the form is opened in design view (and maybe hidden), you could use (air code):

.
.
Forms!FormName.Dataentry = True (or False)
DoCmd.Close acForm, Forms!FormName.Name, acSaveYes
.
.

Change the Blue text (above) to your form name.


You said you wanted to get the form name from a combo box. If you have a one column row source (a value list), you don't have to use the columns property. If the combo box is named "cboObjectClassSelect", you could try:

.
.
Forms(Me.cboObjectClassSelect).Dataentry = True ' (or False)
DoCmd.Close acForm, (Me.cboObjectClassSelect).Name, acSaveYes
.
.

My $0.02 worth

[neo651]

I tried the new line you gave me but got the same error.

Then I set a breakpoint on that line and compiled. It highlights "Forms!" and throws:

Compile error: Type-declaration character does not match declared data type.

It does this for the original line and the new line.

[neo651]

Thanks for the suggestion ssanfu. I just tried that out, still get the same error though

[Dal Jeanis]

Okay, there's another way to skin this cat. It's not letting you go straight for the form by its title. However, if you know that the form is open, and therefore it's in there somewhere, then you can loop through the forms collection and check against each form name that's loaded until you find it. I've seen that done a few times. Can't look for it right now, I have a project of my own I've got to get done. Here's some pages to get you started.

http://www.java2s.com/Code/VBA-Excel...ghallforms.htm
http://msgroups.net/microsoft.public...ed-forms/12331
http://msgroups.net/microsoft.public...s-to-ch/157389

[ssanfu]

I shouldn't do air code.....


This changes the value for the data entry property:

Option Compare Database    'should ALWAYS have this line
Option Explicit    'should ALWAYS have this line

'Private Sub Command0_Click()
    Dim CurrentStatus   'for debugging
    Dim FormName As String

    FormName = Me.cboObjectClassSelect

    '    DoCmd.OpenForm FormName, acDesign    'open visible for debugging
    DoCmd.OpenForm Me.cboObjectClassSelect, acDesign, , , , acHidden

    CurrentStatus = Forms(FormName).DataEntry   'for debugging
    MsgBox Forms(FormName).DataEntry   'for debugging

    'set property
    'this toggles the TRUE/FALSE value
    Forms(FormName).DataEntry = Not CurrentStatus  'for debugging

    'these were for setting the value so I didn't have to type as much
    '    Forms(FormName).DataEntry = True
    '    Forms(FormName).DataEntry = False   'for debugging

    MsgBox Forms(FormName).DataEntry   'for debugging

    'close and save
    DoCmd.Close acForm, FormName, acSaveYes
'End Sub

NOTE: if the form name selected in the combo box is currently open and visible, the form will be changed to design mode and hidden, then saved and closed.

[neo651]

Alright, after trying the last two suggests you guys posted and still getting the same error I started getting suspicious. I made a test module that was literally this:

Option Compare Database
Private Sub Test()
End Sub

And it still threw that error. I don't think the code is the problem. What's going on here?

[ssanfu]

Sounds like there might be come corruption.

When you create a new dB, the first thing you should do is go to OPTIONS, select CURRENT DATABASE and turn off AUTO-CORRUPT..... err, I mean AUTO-CORRECT (both check boxes). This is a known cause of corruption.

Create a new dB, turn off AUTO-CORRECT, Save and close. Open the new dB and import the objects from the old dB. Then try the code.

[neo651]

Opened a brand new database, turned off auto correct, created a form with a test "Ok" button. Wrote a fresh module with the same test code from my last post, linked that code to the OK button and ran it. Still get that same error.....

[Dal Jeanis]

Sounds like time for a reinstall, to me. Not much chance of us fixing the code if the problem isn't the code.