Combo Boxes - Open Forms

  • Thread starter Thread starter Singinbeauty
  • Start date Start date
S

Singinbeauty

Please see the code below. I am just about to go stir crazy if I can't figure
out why, when a selection is made, that the form doesn't open. Please save me
from the insanity!!!

Private Sub Combo140_Click()
If Me.Combo140 = "BR35" Then
[BR35Q Form]
ElseIf Me.Combo140 = "International" Then
[International Form]
ElseIf Me.Combo140 = "MBU" Then
[MBU Form]
ElseIf Me.Combo140 = "POP NW" Then
[POPNW Form]
ElseIf Me.Combo140 = "POP MW" Then
[POPMW Form]
ElseIf Me.Combo140 = "POP SW" Then
[POPSW Form]
ElseIf Me.Combo140 = "TI Domestic" Then
[TitaniumD Form]
ElseIf Me.Combo140 = "TI International" Then
[TitaniumI Form]
Else
MsgBox "No selection made.", , "Database Information"
End If
End Sub
 
I don't see where that code is going to do anything other than display a
message box if one of the first 8 items isn't selected. Nowhere in there
are you actually telling Access to open a form, and that is probably the
wrong event to use anyway. What exactly do you want to happen? Do
you want a form to open as soon as a selection is made in the combo box,
or does the user make a selection in the combo and then click a
separate command button? Try to provide some more details.
 
I am pretty new to coding so I appreciate your information. :o) I was going
off of previous questions asked on this forum but got frustrated...

What I would like to happen is when you click on the selection in the Combo
Box that it opens the corresponding form automatically.
 
OK. I am going to assume that the column in your combo box that holds
the form name is the bound column. Also, the code listed should go in
the After Update event of the combo box.

The simplest way to do it would be to change the values in the combo
box to be an exact match for your actual form names. Then you could
use some very simple coding like;

Private Sub Combo140_AfterUpdate()

Dim strFormName as String
strFormName = Me.Combo140

DoCmd.OpenForm strFormName

End Sub

If you can't change the names in the combo box for some reason, then you
could use a Select Case statement like;

Private Sub Combo140_AfterUpdate()

Dim strFormName As String

Select Case Me.Combo140
Case "BR35"
strFormName = "BR35Q Form"
Case "International"
strFormName = "International Form"
Case "MBU"
strFormName = "MBU Form"
Case "POP NW"
strFormName = "POPNW Form"
Case "POP MW"
strFormName = "POPMW Form"
Case "POP SW"
strFormName = "POPSW Form"
Case "TI Domestic"
strFormName = "TitaniumD Form"
Case "TI International"
strFormName = "TitaniumI Form"
Case Else
strFormName = ""
End Select

If strFormName = "" Then
MsgBox "No selection made.", , "Database Information"
Exit Sub
Else
DoCmd.OpenForm strFormName
End If

End Sub

The above is all air code, so it may have some typos that need to be fixed.
 
I really do appreciate your help on this. :o)

Ok so I took your advice and made sure that the form names and the names
listed on the combo box were identical. I took the first code you have below
and applied it to the 'After Update' event but I am getting the following
message when I try to select one of the items:

Run-time error '2102'
The form name '3' is misspelled or refers to a form that doesn't exist.

The names in the column matches the names of the forms so I am unsure why it
is saying that the name is misspelled.
 
By the way, the following line of the code is highlighted in yellow when I
choose to debug:

DoCmd.OpenForm strFormName
 
Then it sounds like the column in your combo box that displays the form
name is *not* the bound column, and that your bound column actually
contains a numerical value. Try this;

Private Sub Combo140_AfterUpdate()

Dim strFormName as String
strFormName = Me.Combo140.Column(1)

DoCmd.OpenForm strFormName

End Sub

Note the Column reference that has added to Combo140

Combo boxes can have many columns, but only one column can be *bound*
to the combo box. You can still reference values in the other columns, but
you have to use the Column property as above. It is a zero based numbering
system, so the first column is Column(0), second is Column(1), etc. If the
above doesn't work, you may need to change the column number depending
on which column in your combo has the form name.
 
THANK YOU! That really helped. Though I have something mysterious happening
that hopefully you can help with.

I have the list on a table of the forms to be opened but for some reason,
one of the form names changes to a number (different almost every time like
'7' or '5' or '0') and it's always the same form name. See the table below
and the code. Maybe someone knows why it's changing.

Name Category
BR35 BR35
International International
MBU MBU
POP MW 7
POP NW POP_NW
POP SW POP_SW
TI Domestic TI_Domestic
TI International TI_International

Private Sub Combo140_AfterUpdate()

Dim strFormName As String
strFormName = Me.Combo140.Column(1)

DoCmd.OpenForm strFormName

End Sub

The 'Category' column is what pulls the name of the form. As you can see,
the form for 'POP MW' has changed to '7'.

HELP!!!
 
Back
Top