Need to Generate On Error MsgBox

[David92595]

So I'm almost done with a piece of script. My boss has asked me to add a error MsgBox, and I have no idea how to do it or where to put it in the script...before, after, do I have to put it after every If statement???

explination of the script:
The macro "Find_on_click_TS2" just looks at a textbox in another form in a then all the "%% fees & Costs". All of the "%% fees & costs" forms are seperate from each other, but in the same DB.

Here is the script:

Private Sub Command152_Click()
On Error GoTo Command152_Click_Err


If State = "AZ" Then
DoCmd.OpenForm "AZ Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"
ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"


ElseIf State = "CA" Then
DoCmd.OpenForm "CA Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"

ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"
ElseIf State = "ID" Then
DoCmd.OpenForm "ID Fees&Costs"
DoCmd.RunMacro "Find_on_click_TS2"

ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"
ElseIf State = "NV" Then
DoCmd.OpenForm "NV Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"

ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"
ElseIf State = "NC" Then
DoCmd.OpenForm "NC Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"

ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"
ElseIf State = "HI" Then
DoCmd.OpenForm "HI Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"

ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"
ElseIf State = "OR" Then
DoCmd.OpenForm "OR Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"

ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"
ElseIf State = "UT" Then
DoCmd.OpenForm "UT Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"

ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"
ElseIf State = "WA" Then
DoCmd.OpenForm "WA Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"

ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"
ElseIf State = "TX" Then
DoCmd.OpenForm "TX Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"

ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"
ElseIf State = "CO" Then
DoCmd.OpenForm "CO Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"

ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"
ElseIf State = "FL" Then
DoCmd.OpenForm "FL Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"

ElseIf (Form.%% Fees & Costs.TS) <> "Find_on_click_TS2" Then
MsgBox "No Fees & Costs Exist for this TS #"
Else
MsgBox "State Unknown"
End If
Command152_Click_Exit:
Exit Sub
Command152_Click_Err:
MsgBox Error$
Resume Command152_Click_Exit
End Sub




Any help would be greatly apprecheated. If you know of any other ways of stream lining the code, I'd love that too. I know it can be done, I'm just a bit of a noob at VB.

 

[sfreeman@co.mer]

To answer your last question, I'd serioulsy look at using a Select Case rather than the lengthy If

http://support.microsoft.com/kb/141691

 

[David92595]

First of all thank you for being so responsive

I've intigrated the Select case as best I can. but I'm still am having problems right around the case vb___ statements. I can't get my statements to do anything. When I click the btn nothing happends.
For the code:
Case vbOkay
DoCmd.OpenForm "NewOrdersAz"
DoCmd.GoToRecord , , acNewRec

I want to open a blank copy of a form that is already open, currently the "Find and replace" window in front of the open form if that matters, it can be closed if need be.


If State = "AZ" Then
DoCmd.OpenForm "AZ Fees & Costs"
DoCmd.RunMacro "Find_on_click_TS2"
If Forms![Main Details]!TS <> Forms![AZ Fees & Costs]!TS = True Then
Select Case MsgBox("TS# is not in Fees & Costs, would you like to add it now?", vbOKCancel)
Case vbOkay
DoCmd.OpenForm "NewOrdersAz"
DoCmd.GoToRecord , , acNewRec
Case vbCalcel
DoCmd.Close acForm, "AZ Fees & Costs"
End Select
End If