Multiple checkbox values on Form to one Access DB field using VBA

[LindaRuble]

Hello,
I am trying to have multiple checkbox values go to one data field in an Access DB. I am trying to accomplish
this with VBA. I have six checkboxes on a form and a hidden text field "Emailed_Description" that should
collect all the checked (and unchecked) results before it goes to the database field "Emailed_Description" in
table "RTIClientTracker."
Here is the code:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++
Private Sub Emailed_Description_BeforeUpdate(Cancel As Integer)
Dim ES As Integer, HA As Integer, MD As Integer, HP As Integer, FC As Integer, MCR As Integer
ES = Me.emailES.Value
HA = Me.emailHA.Value
MD = Me.emailMD.Value
HP = Me.emailHP.Value
FC = Me.emailFC.Value
MCR = Me.emailMCR.Value

If (ES = -1) Then
ES = "ES, "
Else
ES = " "
Debug.Print ES
If (HA = -1) Then
HA = "HA, "
Else
HA = " "
Debug.Print HA
If (MD = -1) Then
MD = "MD, "
Else
MD = " "
Debug.Print MD
If (HP = -1) Then
HP = "HP, "
Else
HP = " "


Debug.Print HP
If (FC = -1) Then
FC = "FC, "
Else
FC = " "
Debug.Print FC
If (MCR = -1) Then
MCR = "MCR, "
Else
MCR = " "
Debug.Print MCR
Me.Emailed_Description = ES + HA + MD + HP + FC + MCR
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++

Is this possible or do I need to do this another way?
Should I be adding more columns to my database for the separate checkboxes and then combine the checkbox
results in the database field "Emailed_Description" in table "RTIClientTracker?"
Thanks
Linda

[June7]

That code should work. However, you show = " " which would save a space and I don't think you want a space. An empty string would be quote marks with no space between. Consider:

Dim strDesc
strDesc = IIf(Me.emailES=True, "ES, ", "")
strDesc = strDesc & IIf(Me.emailHA=True, "HA, ", "")
strDesc = strDesc & IIf(Me.emailMD=True, "MD, ", "")
strDesc = strDesc & IIf(Me.emailHP=True, "HP, ", "")
strDesc = strDesc & IIf(Me.emailFC=True, "FC, ", "")
strDesc = strDesc & IIf(Me.emailMCR=True, "MCR, ", "")
Me.Emailed_Description = Left(strDesc, Len(strDesc) - 2)

[LindaRuble]

Hi,

What event should I attach this code to? Before Update? After Update? On Change?
I am associating this code to the "Emailed_Description" text box which will be hidden. Will the combined results of the check box be entered into the one field in the DB successfully if I do that? I've been trying my code but have not been successful in updating my DB. Maybe your code will be successful.

Linda

[Missinglinq]

Emailed_Description_BeforeUpdate will only fire if data is entered into it physically, i.e. through the keyboard, copy and pasting it in or scanning it in, not when it is populated through code. You need this code to run just prior to the Record being saved, which means it should be placed in the Form_BeforeUpdate event.

Having said that, Emailed_Description is a Calculated Field, and as such, should not be saved to the Table at all! Calculated Fields should simply be recalculated when needed.

Linq ;0)>

[LindaRuble]

Emailed_Description_BeforeUpdate will only fire if data is entered into it physically, i.e. through the keyboard, copy and pasting it in or scanning it in, not when it is populated through code. You need this code to run just prior to the Record being saved, which means it should be placed in the Form_BeforeUpdate event.

Having said that, Emailed_Description is a Calculated Field, and as such, should not be saved to the Table at all! Calculated Fields should simply be recalculated when needed.

Linq ;0)>

Hello Missinglinq,

Thank you for your explaination of what event to use and why.

You kind of lost me with the Calculated Field part, however. You are saying that the VBA code will calculate the results of the check boxes and the hidden form field "Email_Description" temporarly store the info but the table will not save the calculation? Where will it be saved? I need the results of the calculation to be stored so that it can be used with other outputs of different reports in Word, Excel, etc.

You say that it should be recalculated when needed. Shouldn't I just save the individual check box results in the table and then "calculate" the results when I need to generate a report, word doc, Excel spreadsheet, etc? If so, how do I do this?

Thanks for your help.


Linda

[Missinglinq]

Yes and no! The Emailed_Description Field shouldn't be stored in the Table, you should store the Values of the Checkboxes, in the Table, then use your expression to recreate Emailed_Description whenever it's needed.

Linq ;0)>

[June7]

The VBA code will save the calculated results. The trick is figuring out what event to put the code in. Ling suggests the form BeforeUpdate event.

Linq is also advising that you don't even save the concatenated string to table but instead have Yes/No fields in table that save the checkbox choices. Then on a form or report calculate the concatenated string whenever it is needed. A principle of relational database is enter raw data, calculate on reports. However, if you want the data for a mail merge with Word then I understand the need to save the result as I think mail merge doesn't work with queries and calculated fields.

[LindaRuble]

The VBA code will save the calculated results. The trick is figuring out what event to put the code in. Ling suggests the form BeforeUpdate event.

Linq is also advising that you don't even save the concatenated string to table but instead have Yes/No fields in table that save the checkbox choices. Then on a form or report calculate the concatenated string whenever it is needed. A principle of relational database is enter raw data, calculate on reports. However, if you want the data for a mail merge with Word then I understand the need to save the result as I think mail merge doesn't work with queries and calculated fields.

Hi June7,


I used the following code that you gave me:

Dim strDesc
strDesc = IIf(Me.emailES=True, "ES, ", "")
strDesc = strDesc & IIf(Me.emailHA=True, "HA, ", "")
strDesc = strDesc & IIf(Me.emailMD=True, "MD, ", "")
strDesc = strDesc & IIf(Me.emailHP=True, "HP, ", "")
strDesc = strDesc & IIf(Me.emailFC=True, "FC, ", "")
strDesc = strDesc & IIf(Me.emailMCR=True, "MCR, ", "")
Me.Emailed_Description = Left(strDesc, Len(strDesc) - 2)

I had to add columns to my table too. For some reason I needed to add these table columns in order to store the combined form field "Emailed_Description" in the table. It works but you have to save the record first by clicking the little pencil or by going to another record. If I go directly to the Open Report button to open an Access Report, the composite information is saved in the table but is not displayed in the Access Report.

The Report can be updated by manually hitting the "F5" key. I would like to do this automatically by using SendKeys. Can you tell me the proper way to code this and where to place this code. Perhaps in the "On Load" event of the Access Report?

Thanks for the help.

Linda

[June7]

Yes, need to commit record to table before data is available for report. Can be done programmatically.

In the Open Report button: DoCmd.RunCommand acCmdSaveRecord

Does that meet your requirement and eliminate need to 'update' report?

What fields did you have to add - the checkboxes? Don't understand why. Is the form in Continuous or Datasheet view?

[LindaRuble]

Yes, need to commit record to table before data is available for report. Can be done programmatically.

In the Open Report button: DoCmd.RunCommand acCmdSaveRecord

Does that meet your requirement and eliminate need to 'update' report?

What fields did you have to add - the checkboxes? Don't understand why. Is the form in Continuous or Datasheet view?

Hi June7

I was actually attaching the "DoCmd.RunCommand acCmdSaveRecord" code to the "Emailed_Description" text box using the BeforeUpdate event. I have attached the acCmdSaveRecord code to the On Click event of the Open Report button and now it the report updates great!

FYI, the form is in "Single Form" default view.

Thanks, this forum has helped me a lot.


Linda