multiple check box values - update table

[joycesolomon]

hi all,

Good morning.

I do need some help with the vba programming.

I have a form that has 4 check boxes. They all can be left null at a given time, or they all can be checked at the same time.

each check box is tied to a column in the table. (image attached)



how do i send the value back from the form to the table, if the checkbox is checked?

Here is the code i am using to insert the values from the form to the table.

CurrentDb.Execute "INSERT INTO Project (Initiator, InitDate, Program, Type,[Developer Name], EquipAff, [Project Description], Justification, ReqCompl, Notes) " & _
                  " VALUES ('" & Me.txtInitiator & "', """ & Me.txtInitDate & """, '" & _
                  Me.cboProgram & "', '" & Me.cboType & "', '" & Me.cboDeveloperName & "', '" & Me.txtEquipAff & "', '" & Me.MOCDescr & "', '" & _
                  Me.Justification & "', """ & Me.txtReqCompl & """, '" & Me.Notes & "')"

The check boxes are named chkEnvironmental, chkSafety, chkCompliance, chkRunCritical.

In the table , the columns are named Environ, Safety, Comp, Run

Please help as i am not sure how to achieve this.

Thanks in advance

[Bob Fitz]

If the form is bound to the table, each of the check box controls can be bound to a field in the table. No code would be needed. Any change made in the form will automatically be made in the table too.

[John_G]

Hi -

If the checkboxes on the form are bound to the fields (columns) in the table, then you don't need any code. The table is updated when you make the changes on the form, and move off that record. (The same is true of all the other values as well).

If you want to include those values in the VBA insert statement, include the field names in the SQL Insert statement field list, and the form control names in the VALUES list, in addition to the ones you have. These ones will not have quotation marks:

CurrentDb.Execute "INSERT INTO Project (Initiator, InitDate, Program, Type,[Developer Name], EquipAff, [Project Description], Justification, ReqCompl, Notes, Environ, Safety, Comp, Run) " & _
                  " VALUES ('" & Me.txtInitiator & "', """ & Me.txtInitDate & """, '" & _
                  Me.cboProgram & "', '" & Me.cboType & "', '" & Me.cboDeveloperName & "', '" & Me.txtEquipAff & "', '" & Me.MOCDescr & "', '" & _
                  Me.Justification & "', """ & Me.txtReqCompl & """, '" & Me.Notes & "'," & _
me.chkEnvironmental & ", " &  me.chkSafety & ", " &  me.chkCompliance & ", " & chkRunCritical & ")"

HTH

John

[joycesolomon]

John,

This form is to enter new records. So i have set all the fields to be "unbound". Is that a bad idea?

[John_G]

Hi -

"So i have set all the fields to be "unbound". Is that a bad idea?"

No, not necessarily. It depends on what you are comfortable with, and if you reasonably comfortable with VBA. Having all the fields unbound gives you more flexibility, in that you only add a record when you really want to, after all checks and validations are done.

John

[Bob Fitz]

I would not normally use an unbound form for data entry. I'm sure it will end up needing more code. I can't see what there is to be gained in that.

[joycesolomon]

john,

Will i be using the same resolution you provided above to still insert new records with unbound field?

I only reason why i did that was, i did not want to show all the records but purely wanted to only add a new record with this form.

[John_G]

Hi -

Yes, the code is the same (assuming all data is going into the same table, of course!)

John

[joycesolomon]

It worked perfectly. thank you!