Flow control addressing multiple Stored Procedures

[Massinova]

I have a database whereby three or more tables need data appended using Stored proceedures depending on the criteria of a field.

On the form, I want the user to click a submit button that will run the correct SP based on the selection critieria under Case_Type

I'm a bit unclear how to address the decision type flow-control to the various SPs for the submit button

Example:

Table Main contains
Case_No
Case_Type (Combobox selection 1.Administration, 2. Environmental, 3.Economic)

The user fills out the form (which is bound to Table Main) and chooses the case type, upon clicking the submiaaion button, the following needs to happen


if the Case_type = Administration, then -> DoCmd.RunSQL "Admin_SP" or
if the Case_type = Environmental, then -> DoCmd.RunSQL "Env_SP" or
if the Case_type = Economic, then -> DoCmd.RunSQL "Eco_SP" or
if the Case_type = null, then -> return a message "Case type must identified

 

[MajP]

use a select case statement. (not to be confused with your case_type)

select case Case_Type
case "Administration"
docmd.runSql "Admin_SP"
case "Environmental"
...
case "Economic"
...
case Null
msgbox "Case Type must be identified"
end select