Closing a form only after a combo box selection is made.

[Sla]

Hello,


I'm pretty new to VBA. I have a form with a combo box, in the combo box is a list of other forms you can open. If someone tries to continue without making a selection I have a message box pop up prompting them to make a selection. The problem I'm having is the form closes before the message box pops up, which I don't want to happen. I've tried removing the DoCmd.Close in the code but then the menu doesn't close after a selection is made and that's the only time I want it to close, is after the user has selected a valid field and pressed continue.

Here's a stripped down version of the code.


On Error GoTo Err_Command65_Click
Dim intOKCancel As Integer
Dim strForm As String

If Me.combobox= "-------" Then
strForm = "--------"
ElseIf Me.Me.combobox = "---------" Then
strForm = "------------"
ElseIf Me.Me.combobox = "-------------" Then
strForm = "--------------"
ElseIf Me.Me.combobox = "-------------" Then
strForm = "-----------"
ElseIf Me.Me.combobox = "--------------" Then
strForm = "--------------"
ElseIf Me.Me.combobox = "--------------" Then
strForm = "--------------"
ElseIf Me.Me.combobox = "--------------" Then
strForm = "------------------"
ElseIf Me.Me.combobox = "--------------" Then
strForm = "--------------"
ElseIf Me.Me.combobox = "---------------" Then
strForm = "-------------"
End If


DoCmd.GoToRecord , , acNewRec
DoCmd.Close
DoCmd.OpenForm strForm
Exit_Command65_Click:
Exit Sub


Err_Command65_Click:
intOKCancel = MsgBox("Please Make Selection", vbOKOnly)
Resume Exit_Command65_Click:


I've sat here for nealry 3 hours trying everything and anything I could think of and find from using google. And as I said I'm still learning so I'm absolutely open to any suggestions.

[orange]

You may want to check for the largest string "-------------" first, since the shorter string will be found in the longer.
It might also help readers if you described what you are trying to do in plain English.
It looks like some strings being tested are the same values.

[John_G]

The problem is you are executing these three lines:

DoCmd.GoToRecord , , acNewRec
DoCmd.Close
DoCmd.OpenForm strForm

before you are checking that strForm has been given a value.

You need to check that strForm has a valid value right after the Endif.

e.g.

If len(strForm) = 0 then
MsgBox("Please Make Selection", vbOKOnly)
exit sub
endif

HTH

John

[Sla]

There are actual words that I replaced with ------------. As this is a database for work that I'm working on and I'm trying to be careful as to how much I put out there. I'm sorry if it makes things more difficult to understand what I'm trying to do. What I want is when someone makes one of the selections from the strings I want that form to open and the menu to close. But if they don't make a selection I want the menu to stay open until they make a selection. I don't know if this helps clear anything up.

This is an example of the menu that I just made.

Attachment 17656
Here is a little bit better of an example of the code that I rewrote to match the example picture

Fake values but it's the exact code I pulled out. I didn't write this code, someone else did, I'm just trying to tweak it to do the above.

On Error GoTo Err_Command65_Click
Dim intOKCancel As Integer
Dim strForm As String

If Me.combobox= "FormnameA" Then
strForm = "FormA"
ElseIf Me.Me.combobox = "FormnameB" Then
strForm = "FormB"
ElseIf Me.Me.combobox = "FormnameC" Then
strForm = "FormC"
End If


DoCmd.GoToRecord , , acNewRec
DoCmd.Close
DoCmd.OpenForm strForm
Exit_Command65_Click:
Exit Sub


Err_Command65_Click:
intOKCancel = MsgBox("Please Make Selection", vbOKOnly)
Resume Exit_Command65_Click

[Sla]

Thank you I'll try that.

[Sla]

I fount that deleting the code lines:

On Error GoTo Err_Command65_Click
Dim intOKCancel As Integer
&
Err_Command65_Click:
intOKCancel = MsgBox("Please Make Selection", vbOKOnly)
Resume Exit_Command65_Click:


That were previously in there and adding

If Me!combobox.ListIndex = "-1" ThenMsgBox "A selection is required", vbCritical, "Selection Required"
Me!combobox.SetFocus
Exit Sub
End If

right at the start has solved the issue I was having.


Thank you for the replies!