Drop Down List to Open Forms

[Annabelle Lee]

Hey~
It's Annabelle again and really appreciate for people who answered my previous VBA questions. Saved my life a lot of times~~

This time I am working on a drop down list... which opens forms that users desire. Im having difficulty linking those form names with the OpenForm commend.

The code I had is like this:

 
Private Sub Form_Open(Cancel As Integer)
Dim fobj As AccessObject

With Me![cmdForms]
 .RowSourceType = "Value List"
 
   For Each fobj In CurrentProject.AllForms
     .AddItem obj.Name
   Next
End With
End Sub

Private Sub cmdForms_AfterUpdate()

DoCmd.OpenForm Me.cmdForms, acViewNormal

End Sub

Would someone please help me~ Big thanks in advance!!

Annabelle

[NeoPa]

Please be more specific as to your problem Annabelle. What goes wrong where?

[Annabelle Lee]

What I am trying to do is simply open forms in my current database through drop down list. First I want to attach form names onto the drop down list, and next step I want to link those names to its corresponding forms, so when update the drop down list, I would get the right form to open up.

When I ran this code, a error message shows as " The form name '1' is misspelled or refers to a form that doesn't exit. And it highlighted the "DoCmd.OpenForm " line..

[Rabbit]

Well, do you have a form that is named 1?

[Annabelle Lee]

no, my form's name is Asset Status Form and the drop down list's name is cmdForms. I have no idea why the error message says '1'....

[NeoPa]

Let me put you out of your misery first :
obj is not the same as fobj.

Line #9 should be :

     .AddItem fobj.Name

[NeoPa]

Now, I suggest that you read all the relevant points in When Posting (VBA or SQL) Code and ensure that you follow these in future. I suspect this would not even have arisen as a problem if you'd followed these simple basic rules.

If you learn only these steps from this question, then I expect it's been very much worth the effort.

[Mihail]

Hi !
Are you verify what Me.cmdForms return ?
I suggest you to insert this cod before line 16 (only for debug purpose):

Dim FormName As String
FormName = Me.cmdForms
Debug.Print FormName
Stop

When the program stop you can see in Immediate Window if the FormName is what you expect to be.

[NeoPa]

The code will work perfectly, as long as the object variable in line #9 is spelt the same way as the one in line #8 Mihail (See post #6).

[Mihail]

I see your post, NeoPa, and I understand the point.
I wish to provide a simple step to debug himself (herself) that type of errors. Not for now, because you already do that, but for the future.

[NeoPa]

Ah. I see. That makes some sense.

[Annabelle Lee]

Thanks NeoPa and Mihail for your replies! One extra question, if I want to assign "fobj" one or more specific form names instead of all form names in my database, how should I change the code?

[NeoPa]

You'd simply use the command (outside of any loop) :

Call Me.cmdForms.AddItem("YourText")

PS. No need even to Dim fobj in this situation.

[Annabelle Lee]

After I changed everything, the drop down list still shows nothing....I have no idea what I did wrong. The weird thing is that when I try to switch to design view, the first form I put on the list opens up (where I had total of two forms wanted on the list), the following is the code:

Private Sub Form_Load()

With Me![cmdf]
 .RowSourceType = "Value List"
End With
Call Me.cmdf.AddItem("Asset Status Form")
Call Me.cmdf.AddItem("Database Update Operation Page")
End Sub
 
Private Sub cmdf_AfterUpdate()
 
DoCmd.OpenForm Me.cmdf, acViewNormal
 
End Sub

Another concern I had is I had another drop down list on the same form and the Form_Open function has been used, so when I try to define this drop down list, is showed an error message saying there's ambiguous information within the code. So I switched to Form_Load. But is there other ways to define this?

[NeoPa]

Every time you add an item into the list after the form is properly available (I'm not sure, but you probably didn't see this problem until after you changed it from Form_Open() to Form_Load()), the item's _AfterUpdate() event fires, triggering the event procedure to run.

Also, your code is not using the With syntax very well. It should look more like :

Private Sub Form_Load()
    With Me.cmdf
        .RowSourceType = "Value List"
        Call .AddItem("Asset Status Form")
        Call .AddItem("Database Update Operation Page")
    End With
End Sub

Private Sub cmdf_AfterUpdate()
    Call DoCmd.OpenForm(Me.cmdf, acViewNormal)
End Sub

Annabelle:
Another concern I had is I had another drop down list on the same form and the Form_Open function has been used, so when I try to define this drop down list, is showed an error message saying there's ambiguous information within the code. So I switched to Form_Load. But is there other ways to define this?

I'm not sure what your actual design is, so I can't see why this may be necessary. It rarely proves to be so if the design is good.

Assuming for now though that it is, you can avoid the problem by Dimming a private module variable that is required to be True before cmdf_AfterUpdat e() is allowed to process anything. That way forms will only be opened after the items have all been added to the control :

Private blnReady As Boolean

Private Sub Form_Load()
    With Me.cmdf
        .RowSourceType = "Value List"
        Call .AddItem("Asset Status Form")
        Call .AddItem("Database Update Operation Page")
    End With
    blnReady = True
End Sub

Private Sub cmdf_AfterUpdate()
    If blnReady Then Call DoCmd.OpenForm(Me.cmdf, acViewNormal)
End Sub