When building SQL statements, use the Tag property to hold the Field Name and Data Format. I use a naming convention of str, int, dat, and so on to determine the datatype; the rest of the tag holds the field name in the database?such as strCompanyName, datStartDate, or intQuantity. This routine iterates through the controls on the form and determines whether the control has a tag (you should tag only the controls that hold data). The routine then checks the type of control?Textbox or MaskedEditBox?to determine whether it should use the Text or FormattedText property. If the control has a value, it builds a string consistent with the datatype. Otherwise, it adds a Null value. After running through the controls, it combines the strings. You can then use the resulting SQL statement to execute an Insert operation into the database:
Dim strSQL as StringDim strColumns as StringDim strValues as StringstrSQL = "INSERT INTO [TableName] "' start SQL statementstrColumns = "(" ' hold column namesstrValues = "VALUES(" ' holds corresponding column valuesFor Each ctrl In frmSite.Controls' iterate thru controls If Len(ctrl.Tag) > 0 Then ' if no tag, do not use strColumns = strColumns & Mid(ctrl.Tag, _ 4) & ", " ' add column name Select Case TypeName(ctrl) ' find control type Case "TextBox", "ComboBox" If Len(ctrl.Text) > 0 Then Select Case Left(ctrl.Tag) ' find datatype, whether to ' include single quotes or not Case "str" strValues = strValues & _ "'" & ctrl.Text & "', " Case "int" strValues = strValues & _ ctrl.Text & ", " End Select Else strValues = strValues & "Null, " End If Case "MaskEdBox" If Len(ctrl.Text) > 0 Then Select Case Left$(ctrl.Tag, 3) ' find data type, whether to ' include single quotes or not Case "dat", "phn", "ipa" strValues = strValues & _ "'" & ctrl.FormattedText _ & "', " Case "int" strValues = strValues & _ ctrl.FormattedText _ & ", " End Select Else strValues = strValues & "Null, " End If End Select End IfNext'remove last comma and spacestrColumns = Left$(strColumns, Len(strColumns) - 2)strValues = Left$(strValues, Len(strValues) - 2)'add last parenthesesstrColumns = strColumns & ") "strValues = strValues & ")"'combine stringsstrSQL = strSQL & strColumns & strValues
