Check boxes and sub form

[Guest]

I am redesigning a patient database with appr. 2800 records in it. I have a
form with 5 tab controls on it, 1 tab control "procedures" with 27 check
boxes for 27 different procedures and i have a seperate tab control "patient
summary" with a sub form that i want to list those checked "procedures" in a
datasheet view.

tblepdata
-----------
CompEPS
Ablation
Bi-V ICD
""
""


Meaning, if i check a box or multiple boxes on page 1 (ie...CompEPS and
Ablation) I want the subform on page 3 to list only those choices in a
datasheet view. Of the 27 procedures, a patient will typically only have 2 or
3 checked but i thought doing a seperate check box for each was easier than a
multiselect list box. Now the problem is listing only the checked procedures
on a serperate subform. Any suggestions would be greatly appreciated?

 

[tina]

not quite clear as to your intent. is the main form bound to a Patients
table, perhaps? and the subform to a PatientProcedures table? and are you
wanting to pull up patient A in the main form, check specific procedures on
one tab page, then run code that appends the chosen procedures to the
PatientProcedures table and then requery the subform, so the selected
procedures are now listed in the subform for patient A?

if the above is not what you have in mind, please explain in more detail.

hth

 

[Guest]

Tina,
That's a pretty good breakdown of what i'm looking for. I use the EP data
table for the main form with the check boxes and I have a procedures table
for the subform.

1 caveat may be that patients will come in multiple times for different
procedures so ideally, I would like the Patient Summary Subform for that
patient, to be appended with the procedures rather than only reflect boxes
currently checked. Is that possible or am i stretching it a bit too far?

Thanks in advance for the help.

 

[tina]

ideally, I would like the Patient Summary Subform for that

patient, to be appended with the procedures rather than only reflect boxes
currently checked. Is that possible or am i stretching it a bit too far?

well, no, i don't think so. that's basically what i outlined in my previous
post. (i hope we're not talking at cross-purposes, that happens sometimes.
<g>) one way to add the records would be to have a tblProcedures that is
simply a list of all available procedures, as

tblProcedures
ProcID (primary key, probably Autonumber data type)
ProcName (Text data type)

in the form, on the checkboxes tabpage, go to each checkbox in turn and do
the following: in the checkbox's Tag property, type the ProcID value for
the corresponding record in tblProcedures.
add an "Add Procedures" command button, that the user can click at will. in
the command button's Click event procedure, run the following code, as

Dim ctrl As Object

On Error Resume Next

For Each ctrl In Me.Controls
If TypeOf ctrl Is CheckBox Then
If ctrl = True Then
CurrentDb.Execute "INSERT INTO tblPatientProcedures " _
& "( fkPatientID, fkProcID, ProcedureDate ) SELECT " _
& Me!PatientID & ", " & ctrl.Tag & ", " & Date,
dbFailOnError
End If
End If
Next

Me!SubformControlName.Form.Requery

the above assumes that 1) tblPatientProcedures has a foreign key field named
fkPatientID, that relates the record to a patient in tblPatients, and 2)
tblPatientProcedures has a foreign key field named fkProcID, that relates
the record to a procedure in tblProcedures, and 3) you want to capture the
date that each record is added to tblPatientProcedures.

so the user checksmarks the appropriate procedures for a particular patient,
then clicks the command button. the code runs, appending a record to
tblPatientProcedures for each checkmarked procedure, for the current
patient, for today's date. then the code requeries the subform. so now when
you go look at the subform, the procedure records are displayed.

hth

 

[Guest]

Thanks for the reply. I've made the updates but no luck so far. I keep
getting an "Invalid use of property" error on the dbFailOnError part. If i
remove the code i don't get the error but it still isn't working for me yet.
It's not even updating the tblPatientProcedures.

Here's what i have at this point:
frmPatientData - record source is tblEP Data

tblEP data: tblPatientProcedures:
tblProcedures:
PatientID - text fkPatientID - text
ProcID - text
CompEPS - Yes/No fkProcID - text
ProcName - text
EP Map - Yes/No
Ablation - Yes/No

I have an Indeterminate relationship between tblEP dataatientID and
tblpatientProcedures:fkPatientID.

I have a one-to-many between fkProcID and ProcID

Here is my code for the cmdbutton:
Private Sub Add_Procedures_Click()
Dim ctrl As Object

On Error Resume Next

For Each ctrl In Me.Controls
If TypeOf ctrl Is CheckBox Then
If ctrl = True Then
CurrentDb.Execute "INSERT INTO tblPatientProcedures" &
"(fkPatientID,fkProcID,ProcedureDate) SELECT " & "Me!fkPatientID & ",
"&ctrl.Tag & " '"& Date,

End If
End If
Next

Me!SubformControlName.Form.Requery
End Sub

When i click the cmd button it doesn't update the table. Do you see anything
i might have miskeyed? thanks again for the help.

 

[tina]

comments inline.

Thanks for the reply. I've made the updates but no luck so far. I keep
getting an "Invalid use of property" error on the dbFailOnError part. If i
remove the code i don't get the error but it still isn't working for me yet.
It's not even updating the tblPatientProcedures.

Here's what i have at this point:
frmPatientData - record source is tblEP Data

tblEP data:
PatientID - text
CompEPS - Yes/No

tblPatientProcedures:
fkPatientID - text
fkProcID - text

tblProcedures:
ProcID - text
ProcName - text

because of the way your post came through, i can't tell which table these
two fields belong to:

EP Map - Yes/No
Ablation - Yes/No

so for the moment i'm going to assume it isn't important to the issue we're
trying to solve.

I have an Indeterminate relationship between tblEP dataatientID and
tblpatientProcedures:fkPatientID.

that's a problem. a table relationship, as defined in the Relationships
window, should always be one-to-one or one-to-many. in Access, relationships
are "directional" - FROM the primary key field in TableA, TO the foreign key
field in TableB. in [tblEP data], is the PatientID field the primary key? in
the Relationships window, did you drag 'n drop the PatientID field FROM
[tblEP data] TO tblPatientProcedures? in tblPatientProcedures, is the
PatientID field's Indexed property set to "Yes (Duplicates OK)"?

I have a one-to-many between fkProcID and ProcID

again, make sure that the ProcID field of tblProcedures is the table's
primary key. and make sure that the direction of the relationship is FROM
tblProcedures TO tblPatientProcedures.

Here is my code for the cmdbutton:
Private Sub Add_Procedures_Click()
Dim ctrl As Object

On Error Resume Next

For Each ctrl In Me.Controls
If TypeOf ctrl Is CheckBox Then
If ctrl = True Then
CurrentDb.Execute "INSERT INTO tblPatientProcedures" &
"(fkPatientID,fkProcID,ProcedureDate) SELECT " & "Me!fkPatientID & ",
"&ctrl.Tag & " '"& Date,

End If
End If
Next

Me!SubformControlName.Form.Requery
End Sub

you needed to use the exact syntax that i gave you - compare your code to
the original code i posted, and note the very specific use of spaces in the
strings - EXCEPT that i believe i screwed up on the Date syntax. and my
original code assumed that PatientID and ProcID were Number data type. since
you say they're Text data type, the syntax there is different too, so try

CurrentDb.Execute "INSERT INTO tblPatientProcedures " _
& "( fkPatientID, fkProcID, ProcedureDate ) SELECT '" _
& Me!PatientID & "', '" & ctrl.Tag & "', #" & Date & "#", _
dbFailOnError

again, note the specific use of spaces in the strings.

hth

 

[Guest]

Tina,
Fixing the code got it working. Thanks for the great help.

 

[tina]

you're welcome

Tina,
Fixing the code got it working. Thanks for the great help.