Excel check box values to Access Check Box values

[Guest]

I have been given a series of Excel worksheets that have 23 check boxes for
things like "currently outstanding", "testing required", etc. The user is
suposed to check/uncheck the box to indicate status or needs as appropriate.

I need to read these values into an Access database where the corresponding
Access Table Field is formatted as "Yes/No". In some cases the code needs to
be reversed (e.g. In Excel the user checks the box when the form has been
"completed" but in Access the field name is "Currently outstanding" which
would only be checked if the Excel box were not.)

Code for this is:
If ActiveWorkbook.Sheets("EMG").[Check Box 32] Then
datatbl![Currently Outstanding].Value = False
Else
datatbl![Currently Outstanding].Value = True
End If

Unfortunately this does not work. I'm thinking it's because the Access field
[Currently outstanding] is formatted "Yes/No" and not "True/False".

if I try:

If ActiveWorkbook.Sheets("EMG").[Check Box 32] Then
datatbl![Currently Outstanding].Value = No
Else
datatbl![Currently Outstanding].Value = Yes
End If

I get a variable not defined error for "Yes" and "No"; and if I try:

If ActiveWorkbook.Sheets("EMG").[Check Box 32] Then
datatbl![Currently Outstanding].Value = "No"
Else
datatbl![Currently Outstanding].Value = "Yes"
End If

I get type mismatch (text into boolean)

How do I get Access to correctly "translate" the checked box into a "yes/No"
value for the field.

TIA

BAc

 

[Guest]

I have been given a series of Excel worksheets that have 23 check boxes for
things like "currently outstanding", "testing required", etc. The user is
suposed to check/uncheck the box to indicate status or needs as appropriate.

I need to read these values into an Access database where the corresponding
Access Table Field is formatted as "Yes/No". In some cases the code needs to
be reversed (e.g. In Excel the user checks the box when the form has been
"completed" but in Access the field name is "Currently outstanding" which
would only be checked if the Excel box were not.)

Code for this is:
If ActiveWorkbook.Sheets("EMG").[Check Box 32] Then
datatbl![Currently Outstanding].Value = False
Else
datatbl![Currently Outstanding].Value = True
End If

Unfortunately this does not work. I'm thinking it's because the Access field
[Currently outstanding] is formatted "Yes/No" and not "True/False".

In Access, there's no difference between True/False, Yes/No, or On/Off. The
values are stored as either 0 (false) or -1 (true). In Excel, are you using
the ActiveX check box control or the Forms check box control? In either case,
I think you can link the check box to a cell. I would suggest modifying your
code to look at the cell values (true/false).
If ActiveWorkbook.Sheets("EMG").Range("C5") = "FALSE Then
datatbl![Currently Outstanding].Value = 0
Else
datatbl![Currently Outstanding].Value = -1
End If


Barry

if I try:

If ActiveWorkbook.Sheets("EMG").[Check Box 32] Then
datatbl![Currently Outstanding].Value = No
Else
datatbl![Currently Outstanding].Value = Yes
End If

I get a variable not defined error for "Yes" and "No"; and if I try:

If ActiveWorkbook.Sheets("EMG").[Check Box 32] Then
datatbl![Currently Outstanding].Value = "No"
Else
datatbl![Currently Outstanding].Value = "Yes"
End If

I get type mismatch (text into boolean)

How do I get Access to correctly "translate" the checked box into a "yes/No"
value for the field.

TIA

BAc