Form - Update Table - Click Event

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I have a form with unbound text boxes, check boxes with labels, etc. After
I check all of my check boxes and fill in the text boxes I want to create a
button that will append all data on the form to a table. So if a check box
is checked, I want the text in the check boxe label to be appended to a field
in my table, in addition to all text boxes that are filled in to be appened
to fields in my table. Some check boxes and text boxes will remain blank.
Can someone get me started with the code I would use for this? Thanks so
much.
 
Is each checkbox associated with it's own field, or can only one of the
checkboxes be selected?

If only one, then I would argue that what you need instead of a checkbox is
probably a combo box. Create a table that contains an ID field (autonumber)
and a text field (would contain the text you are currently displaying in your
check boxes labels). I would normally not store the text in your data table,
only the ID value associated with the text.

The code behind your click event might look something like:

Private sub cmd_Save_Click

Dim strSQL as string
Dim rs as DAO.Recordset

strSQL = "SELECT * FROM tbl_YourTable WHERE False"
Set rs = currentdb.openrecordset strSQL

rs.AddNew
rs("Field1Name") = me.txt_Field1
rs("Field2Name") = me.txt_Field2
rs("Field3Name") = me.txt_Field3

'if the checkboxes all apply to a single field then:
if me.checkbox1 = true then
rs("Field4Name") = me.lbl_checkbox1.caption
elseif me.checkbox2 = true then
rs("Field4Name") = me.lbl_checkbox2.caption
elseif me.checkbox3 = true then
rs("Field4Name") = me.lbl_checkbox1.caption
end if

rs.update
rs.close
set rs = nothing

End Sub

The other way to do this is to build a SQL string on the fly, but if you do
it that way, you have to worry about wrapping text values in quotes, and I
find this to be much easier, especially where there are a lot of fields. For
instances with only a couple of fields, I'll create the INSERT statement and
execute it, something like:

Private sub cmd_Save_Click

Dim strSQL as string

'in this instance, I'm assuming that txtField1 is text (hence wrapping the
'value in single quotes), and that txtField2 is numeric.
strSQL = "INSERT INTO tbl_YourTable (Field1Name, Field2Name) " _
& "Values ( '" & me.txtField1 & "', " _
& me.txtField2 & ")"
Currentdb.execute strsql, dbfailonerror

End Sub

HTH
Dale
 
Back
Top