Cleaner Code with DoCmd.RunSQL Insert into

[mduplantis]

Yes, just describing my progress. Thanks

[mduplantis]

When you are checking to see if the IsNull(lstIncident) What property is it testing for? It looks like it is checking for a record because you either call an Insert INTO or an Update for the SQL statement.

Thank you.

If IsNull(lstIncident) Then    MsgBox "ERROR:  An incident must be chosen before adding or updating exposures present", vbOKOnly, "ERROR Adding/Updating Exposure"
Else
    RequiredCheck = SubRequired(Me.Name, Screen.ActiveControl.Tag, Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab")).Pages(Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab"))).Name)
    If Len(RequiredCheck) > 0 Then
        MsgBox "The following fields are required to add an exposure" & vbCrLf & vbCrLf & RequiredCheck, vbOKOnly, "ERROR ADDING/UPDATING EXPOSURE"
    Else
        If IsNull(lstIncidentExposure) Then
            If DCount("*", "tblIncidentExposure", "[IncidentID] = " & lstIncident & " AND [Exp_ID] = " & Exp_ID) > 0 Then
                MsgBox "That exposure already exists on this incident", vbOKOnly, "ERROR ADDING EXPOSURE"
            Else
                Call SubAdd(Me.Name, Screen.ActiveControl.Tag, Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab")).Pages(Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab"))).Name)
                Call SubClear(Me.Name, Screen.ActiveControl.Tag, Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab")).Pages(Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab"))).Name)
            End If
        Else
            If DCount("*", "tblIncidentExposure", "[IncidentID] = " & lstIncident & " AND [Exp_ID] = " & Exp_ID & " AND [IncExp_ID] <> " & lstIncidentExposure) > 0 Then
                MsgBox "That exposure already exists on this incident", vbOKOnly, "ERROR UPDATING EXPOSURE"
            Else
                Call SubUpdate(Me.Name, Screen.ActiveControl.Tag, Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab")).Pages(Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab"))).Name)
                Call SubClear(Me.Name, Screen.ActiveControl.Tag, Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab")).Pages(Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab"))).Name)
            End If
        End If
    End If
End If
End Sub

I understand what the below sub procedure is doing, but I don't quite understand how it knows to insert into a different table. It looks like you are passing the values to:

Private Sub cmdSub1Add_Click()..........

 Call SubAdd(Me.Name, Screen.ActiveControl.Tag, Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab")).Pages(Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab"))).Name)

Function SubAdd(sFormname, sSub, sTab)

then checking the tags...

If InStr(ctl.Tag, sSub) Then

But, then I get a little lost.

Thanks!

[rpeare]

me.name supplies the name of the form
screen.activecontrol.tag supplies the TAG of the button clicked (Sub1 or Sub2 in my example)
Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab")).Pages(Forms(Me.Name).Controls(Replace(Me.N ame, "frm", "tab"))).Name This monster supplies the name of the TAB you are currently on. I use this specifically to cycle through smaller sets of controls rather than cycling through *all* controls on the form.

So, for example, if you look at what happens if you click a button in the EXPOSURE area when you look at the values that are being passed you would have

sFormName = me.name = frmIncident
screen.activecontrol.tag = Sub1
all the buttons in each 'sub' area have a tag of Subx where x is a number
Forms(Me.Name).Controls(Replace(Me.Name, "frm", "tab")).Pages(Forms(Me.Name).Controls(Replace(Me.N ame, "frm", "tab"))).Name = Main
I think that's what I named the first tab on that form, I pass this value strictly so that I can cycle through a smaller set of controls rather than every control on the form, let's say you had 10 tabs with 100 controls each, you wouldn't want to cycle through 1000 controls every time you were processing data, you'd want to just process the controls on the tab you're on.

In the SubAdd function itself the code is cycling through all the controls and part of that is to look for a control with the TAG sSub & "_List" or "Sub1_List". When it finds that control it extracts the control NAME, in this case it would be lstIncidentExposure, which when you replace lst with tbl you get the TABLE name where the data will be appended. That's why this code relies very heavily on 'properly' named controls with the correct TAGs. The code is basically figuring everything out based on the naming conventions.

[mduplantis]

Perfect. That helps a ton. The monster statement was throwing me off! Thanks!