INSERT SQL statement from Form

[bigal.nz]

Hello,



I have 6 combo boxes on a form and want to construct a insert statement from the values on the boxes, via VBA. This works fine as long as all the boxes have a value selected, but if some boxes are empty I understandably get a error.

I would like a elegant way to handle nulls where no value was selected in the combo box.

Here is the code:

Private Sub cmd_submit_Click()
' *** A FUCNTION ON CLICK TO UPDATE THE MTG TABLE WITH PERSONS PRESENT - ON SUBMIT


Dim strSQL As String
Dim rs As Recordset
Dim db As DAO.Database
Dim contr As Control
Set db = CurrentDb




' *** GET THE ID NUMBERS FROM THE HIDDEN COLUMS IN DROPDOWN LIST
str_facilitator_ID = Me.combo_facilitator.Column(1)
str_admin_ID = Me.combo_admin.Column(1)
str_HB_ID = Me.combo_HB.Column(1)
str_YF_ID = Me.combo_YF.Column(1)
str_corr_ID = Me.combo_corr.Column(1)
str_port_ID = Me.combo_port.Column(1)
' CONSTRUCT A STRING OF ID NUMBERS TO PUT INTO THE INSERT SQL STATEMENT
str_IDs = str_corr_ID & "," & str_HB_ID & "," & str_YF_ID & "," & str_facilitator_ID & "," & str_admin_ID & "," & str_port_ID




' *** INSERT THE ID VALUES OF PEOPLE PRESENT FROM THE FORM AND INSERT INTO MTG TABLE
strSQL = "INSERT INTO tbl_mtg (corr_person_ID, HB_person_ID, yf_person_ID, facilitator_ID, admin_ID, port_ID, Comments) VALUES (" & str_IDs & ",'" & txt_comments & "');"
Debug.Print (strSQL)
' *** EXECUTE THE UPDATE
db.Execute strSQL, dbFailOnError


' *** TIDY UP
strSQL = ""
db.Close
Set db = Nothing


End Sub

-Al

[John_G]

My suggestion would be to use 0 for the ID where none was selected. All you need to do is change your 6 str... statements
from this:
Me.combo_facilitator.Column(1)

to this:

nz(Me.combo_facilitator.Column(1),0)


The Nz function is used to "convert" Nulls to meaningful values. The second parameter specifies the value to return if the first parameter
is Null, otherwise it returns the value in the first parameter.

If you don't want to use zeros to replace nulls, then your code will be a lot trickier, since you will have
to change the Select statement field lists and value lists to account for the Nulls.

[pbaldy]

An alternative is using the AddNew method of a recordset, which wouldn't care about the Nulls. The Nz() method John mentioned is simpler if appropriate.

[davegri]

If you can't tolerate zeros in the table, you can use something like the code below to build the query. The query will not include verbiage for null fields.

Dim strSQL As String
Dim strInsert As String
Dim strValue As String
strInsert = "INSERT INTO tbl_mtg ("
strValue = " Values ("
If Len(txt_comments & vbNullString) = 0 Then
    txt_comments = " "   'space
End If
If Not IsNull(.combo_corr.Column(1)) Then
    strInsert = strInsert & "str_corr_ID, "
    strValue = strValue & "corr_person_ID, "
End If
If Not IsNull(Me.combo_HB.Column(1)) Then
    strInsert = strInsert & "str_HB_ID, "
    strValue = strValue & "HB_person_ID, "
End If
If Not IsNull(Me.combo_YF.Column(1)) Then
    strInsert = strInsert & "str_YF_ID, "
    strValue = strValue & "yf_person_ID, "
End If
If Not IsNull(Me.combo_facilitator.Column(1)) Then
    strInsert = strInsert & "str_facilitator_ID, "
    strValue = strValue & "facilitator_ID, "
End If
If Not IsNull(Me.combo_admin.Column(1)) Then
    strInsert = strInsert & "str_admin_ID, "
    strValue = strValue & "admin_ID, "
End If
If Not IsNull(Me.combo_port.Column(1)) Then
    strInsert = strInsert & "str_port_id, "
    strValue = strValue & "Port_ID, "
End If
strInsert = strInsert & "comments) "
strValue = strValue & "'" & txt_comments & "');"
strSQL = strInsert & strValue
Debug.Print strSQL

[bigal.nz]

My suggestion would be to use 0 for the ID where none was selected. All you need to do is change your 6 str... statements
from this:
Me.combo_facilitator.Column(1)

to this:

nz(Me.combo_facilitator.Column(1),0)


The Nz function is used to "convert" Nulls to meaningful values. The second parameter specifies the value to return if the first parameter
is Null, otherwise it returns the value in the first parameter.

If you don't want to use zeros to replace nulls, then your code will be a lot trickier, since you will have
to change the Select statement field lists and value lists to account for the Nulls.

Thanks for that - I didnt know about the "nz" function. Very handy.

I did also come up with :

Private Sub cmd_submit_Click()
' *** A FUCNTION ON CLICK TO UPDATE THE MEETING TABLE WITH PERSONS PRESENT - ON SUBMIT


Dim strSQL As String
Dim rs As Recordset
Dim db As DAO.Database
Dim contr As Control
Set db = CurrentDb


'strtoday = Date
'Debug.Print (strtoday)




' *** GET THE ID NUMBERS FROM THE HIDDEN COLUMS IN DROPDOWN LIST
For Each contr In Me.Controls
    If TypeName(contr) = "ComboBox" Then
        If contr.Column(1) <> 0 Then
            strValues = contr.Column(1) & "," & strValues
            Else: strValues = 0 & "," & strValues
        End If
    End If
Next


Debug.Print (strValues)

Which does the same as what "nz"

I now need to store these values against a corresponding fieldname in the table.

I could through the field names and create a list of field names paired with values?

Cheers

-Al