Multiple functions from one combo box VBA

[Mescalero]

Good day all,

I have come across an issue with a combo box on my form.
- The combo contains a list of process types for the record.
- One of the list items triggers a process that opens another form based on an identifier in the form. In other words, if someone selects a certain list entry, the next step in the process triggers.
- The one list item is working great.


- The problem arises when I need a different item in the list to trigger a completely separate process.

- The field on this form is called [Request Type] and is a combo box.
- The code is in the "After Update" property.
- The example is for the list item "Death".
- The next item in the list that needs this functionality is "DB App Disability", shown below.
______________________________________
Private Sub Request_Type_AfterUpdate()
If ([Type] = "Death") Then
DoCmd.OpenQuery "FindDupesRnLCheckpoint", acViewNormal, acReadOnly
DoCmd.OpenForm "frm_RnLchkresults"
DoCmd.Close acQuery, "FindDupesRnLCheckpoint"


End If
End Sub
______________________________________
As you can see, this opens and closes a series of items. All of this currently functions great.

Now, I need for the other types to do things like opening a form as well. When I add the code for the other type, it will either only conduct one of the functions or it wont let me open the form containing the field because of an ambiguous designation error.

I'm sure this is because of my lack of VBA knowledge.

- The code below is for the "DB App Disability" type from the combo box.
- This functions well when it is the only function in the "after update" property.
______________________________________
Private Sub Request_Type_AfterUpdate()
If ([Type] = "DB App Disability") Then
DoCmd.OpenForm "frm_DBAppProc1", acNormal, , , acFormEdit, acWindowNormal


End If
End Sub

______________________________________

This will need to continue for the rest of the types in the combo box list.

I've tried combining the code in several different ways but have not been successful. Anyone out there that can help with this?

Thank you.

[davegri]

Something like this....

Private Sub Request_Type_AfterUpdate()
Select Case Request_Type
    Case  "Death"
       DoCmd.OpenQuery "FindDupesRnLCheckpoint", acViewNormal, acReadOnly
       DoCmd.OpenForm "frm_RnLchkresults"
       DoCmd.Close acQuery, "FindDupesRnLCheckpoint"
   Case "DB App Disability"
       DoCmd.OpenForm "frm_DBAppProc1", acNormal, , , acFormEdit, acWindowNormal
   Case "Something Else"
        Docmd.openform "frm_SomethingElse"
...
...

End Select

Alternatively, in your original code, I think the IF should be
If Request_Type
and not just
If [Type]

[Micron]

I'm curious as to why what looks like a select query is being opened and then closed again right after a form opens. Are you thinking that the form needs to have the query open first to show those records?
Type is a reserved word and should not be used for any user created object. In this case, it's probably forcing you to wrap the name in [brackets].

[Mescalero]

Thanks Davegri and Micron,

I really appreciate the help. I'm buried in tasks for the next day and a half. I will go about making the changes as soon as I can and let you guys know what I come up with. Thanks again.

[Mescalero]

Davegri, that method works great! Thank you very much. Micron, the main form that contains the field we are speaking of in this thread has a condition on the drop down when the type "Death" is selected. It has to take the SSN from the current record, challenge it against a table of SSNs that require further actions. If not found, nothing happens. If found, a form opens that is a preformatted email containing pertinent record information. What you see there is simply the way I could get it to work with my limited knowledge. Also, Micron, thanks for the link to the reserved words.

[Welshgasman]

Bring the form name or whatever in with the data for the combo, then just use that other column in an open statement or whatever comes next.

That way you do not need to change code when a new step in the process is added.?

[Mescalero]

Bring the form name or whatever in with the data for the combo, then just use that other column in an open statement or whatever comes next.

That way you do not need to change code when a new step in the process is added.?

Welshgasman, I can see how that would work. I'll keep that in mind. Thanks.