Yes/No boxes in forms

  • Thread starter Thread starter Stephen Glynn
  • Start date Start date
S

Stephen Glynn

I'm working on a record-keeping system for a traditional Chinese
medicine practice and am trying to work out a way of recording the
needle points used in each individual acupuncture treatment session.

I've got this working on a form/subform where the acupuncturist selects
the patient and the treatment session on a main form and then selects on
a subform(using Yes/No boxes) the points he's used in that particular
session. The subform is currently a list of all the available
acupuncture points in continuous form view and the practitioner scrolls
down it clicking on the Yes/No box to record the needling points he's
used in that particular session.

That works without problems.

What I'd like to do -- well, what I've been asked to do -- is to use an
acupuncture schematic of the human body as a background for the subform
(I can do that) and then separate all the Yes/No boxes out and drop them
onto the subform in appropriate places.

What, in short, I want to do is to drop lots of captioned Yes/No boxes
around the graphic of the human body and somehow reference each
particular Yes/No box as referring to a particular fldNeedlingID in my
tblNeedlings and recording whether the Yes/No property for that
particular row is Yes or No.

In short, what I suppose I'm trying to do is drop several Yes/No boxes
onto a graphic, have each Yes/No box referring to a specific
tblNeedlings.NeedlingsID and set the Yes/No field for that particular
needle point to Yes/No.

Alternatively, and I'm not sure how to do this either, I could have a
list of acupuncture points in continuous form view on the left-hand side
of the subform (ideally on the left-hand and right-hand sides both) and
draw lines from them to a graphic on the right of (or between) my
entries in continuous form view.

Steve
 
i like the way you differentiated between what you'd like to do and what
you've been asked to do. <g>
yes, i think you can "drop lots of captioned Yes/No boxes around the graphic
of the human body". set your subform as single form view, so you can show
the body graphic. make all the checkboxes *unbound*, but identify each one
as referring to a specific fldNeedlingID; perhaps via the control name
(example: ck1, ck2, ck3) or perhaps using the Tag property. then i think
you'll need a Save button on the subform.
so the acupuncturist pulls up the patient's record, clicks the appropriate
checkboxes for the points to be used, and clicks the Save button. at that
point you'll need code on the button's Click event to check the value of
each checkbox. if it's true, then the value is saved to the corresponding
fldNeedlingID record in tblNeedlings.

hth
 
Thanks for the suggestion. This is the way I've got it set up at the
moment: tblNeedlings has a NeedlingsID, a NeedlingsName, and a Yes/No
field. The main form contains information about the patient and the
session (including, of course, PatientID and SessionID). The
acupuncturist uses the subform, which is NeedlingsID (invisible),
NeedlingsName and Yes/No in continuous form view, clicking on the
needling points that are used in the session. This sets the
appropriate Yes/No field in tblNeedlings to "Yes".

When the session ends, clicking a button on the form runs a query that
takes the PatientID and the SessionID from the main form and takes the
NeedlingIDs from tblNeedlings where tblNeedlings.Yes/No = Yes. It then
appends the PatientID, the SessionID and the NeedlingIDs to
tblPatientTreatmentRecords.

The acupuncturist then either clicks a reset button to run an update
query that sets all the Yes/No fields in tblNeedlings to "No" or just
closes the form, thus running the update query as part of the OnClose event.

How do I use the control name or the tag property to reference a
specific entry in the tblNeedlings, which is what I think I have to do
using the method you suggest? Or have I misunderstood the logic?

Essentially what I think I want/need (g) to do is to find a way of
binding a specific Yes/No box, which I can then move around the subform
when I'm designing it, to a specific row in tblNeedlings, and I don't
know how to do that.

Alternatively I could somehow store the NeedlingIDs on the subform
itself (I don't know how to do that either) and take the data for my
append query from there but that sounds like poor database design
practice (and a lot of extra work) because I'll end up with my
NeedlingID fields stored in duplicate and unrelated places.

Help! Me confussled!

Steve
 
i know how to make my solution work in your setup, but i can't think how to
explain it to you clearly without writing a book. (i have a problem with
"wordiness" even on simple solutions!)
if you want to send me a copy of your db with a few "dummy" records in it,
i'll set it up and send it back so you can see how i did it. if so, zip the
copy to under 1 MB, refer to the newsgroups in the subject line, and email
to ttaccKILLALLSPAMess1 at yahoo dot com, removing all the capital
letters.
 
Hi Stephen,

I don't understand your data structure. You need something like this:

tblPatients
PatientID
PatientName
etc.

tblSessions
SessionID - PK
PatientID - FK into tblPatients
SessionDate
TherapistID

tblNeedlings (one record for each acupuncture point)
NeedlingID
NeedlingName

tblSessionsNeedlings
SessionID - FK into tblSessions (which gives you the PatientID)
NeedlingID - FK into tblNeedlings

The existence of a record in tblSessionsNeedlings means that that
acupuncture point was used in that session; no record means the point
was not used.

To do it with checkboxes on a form with a background graphic, work along
these lines:

1) Use *unbound* checkboxes. As you place each checkbox, set its Tag
property to the corresponding NeedlingID value.

2) To save a session (i.e. generate the corresponding records in
tblSessionsNeedlings), do something like this untested air code (e.g. in
the Click event of a Save Session button). I assume there's a textbox on
the form called txtSessionID that contains the current SessionID.

Const SQL_1 = "INSERT INTO tblSessionsNeedlings " _
& "(SessionID, NeedlingID) VALUES ("
Dim dbD as DAO.Database
Dim strSQL as String
Dim ctlC As Control

Set dbD = CurrentDB()
For Each ctlC In Me.Controls
If ctlC.ControlType = acCheckBox Then
strSQL = SQL_1 & Me!txtSessionID & ", " _
& ctlC.Tag & ");"
dbD.Execute strSQL, dbFailOnError
Next

3) To set the checkboxes when retrieving a session, you could do
something like this:

Dim ctlC As Control

For Each ctlC in Me.Controls
If ctlC.ControlType = acCheckBox Then
ctlC.Value = (DCount("NeedlingID", "tblSessionsNeedlings", _
"SessionID = " & Me!txtSessionID)) > 0)
Next

It may be faster to open a recordset and use it's Seek method to check
whether there are matching records.
 
Thanks. I'm trying to use your code, but I get an error message 'Run
time error 91 -- object variable or block variable not set' at

dbD.Execute strSQL, dbFailOnError

The full code is:

Private Sub SaveSession_Click()

Const SQL_1 = "INSERT INTO tblSessionsNeedlings " & "(SessionID, _&
NeedlingID) VALUES ("

Dim dbD As Database
Dim strSQL As String
Dim ctlC As Control


For Each ctlC In Me.Controls
If ctlC.ControlType = acCheckBox Then
strSQL = SQL_1 & Me!textSessionID & ", " & ctlC.Tag & ");"
dbD.Execute strSQL, dbFailOnError
End If

Next
End Sub


Where am I going wrong?

Steve
 
Stephen Glynn said:
Thanks. I'm trying to use your code, but I get an error message 'Run
time error 91 -- object variable or block variable not set' at

dbD.Execute strSQL, dbFailOnError

The full code is:

Private Sub SaveSession_Click()

Const SQL_1 = "INSERT INTO tblSessionsNeedlings " & "(SessionID, _&
NeedlingID) VALUES ("

Dim dbD As Database
Dim strSQL As String
Dim ctlC As Control


For Each ctlC In Me.Controls
If ctlC.ControlType = acCheckBox Then
strSQL = SQL_1 & Me!textSessionID & ", " & ctlC.Tag & ");"
dbD.Execute strSQL, dbFailOnError
End If

Next
End Sub


Where am I going wrong?
Click to expand...

Your database object (dbD ) has not been set to anything.
Add Set dbD = CurrentDB before the for each loop.
 
Thanks.

I'm still having problems, though.

It's now saving everything -- all the tag values -- whether or not the
check boxes are checked. It's doing that even if I don't check any of
them!

Help!

Code now is:

Private Sub SaveSession_Click()
Const SQL_1 = "INSERT INTO tblSessionsNeedlings " & "(SessionID,
NeedlingID) VALUES ("
Dim dbD As Database
Dim strSQL As String
Dim ctlC As Control

Set dbD = CurrentDb

For Each ctlC In Me.Controls
If ctlC.ControlType = acCheckBox Then
strSQL = SQL_1 & Me!textSessionID & ", " & ctlC.Tag & ");"
dbD.Execute strSQL, dbFailOnError
End If

Next
End Sub

Shouldn't there be a WHERE clause in the code to specify that the Check
Box has to be set to TRUE? How do I write that?

Steve
 
Stephen Glynn said:
Thanks.

I'm still having problems, though.

It's now saving everything -- all the tag values -- whether or not the
check boxes are checked. It's doing that even if I don't check any of
them!

Help!

Code now is:

Private Sub SaveSession_Click()
Const SQL_1 = "INSERT INTO tblSessionsNeedlings " & "(SessionID,
NeedlingID) VALUES ("
Dim dbD As Database
Dim strSQL As String
Dim ctlC As Control

Set dbD = CurrentDb

For Each ctlC In Me.Controls
If ctlC.ControlType = acCheckBox Then
strSQL = SQL_1 & Me!textSessionID & ", " & ctlC.Tag & ");"
dbD.Execute strSQL, dbFailOnError
End If

Next
End Sub

Shouldn't there be a WHERE clause in the code to specify that the Check
Box has to be set to TRUE? How do I write that?
Click to expand...

You don't need a Where clause you need to check the value of the
control in the for each loop.

Something like:

For Each ctlC In Me.Controls
If ctlC.ControlType = acCheckBox And ctlC.value = True Then
strSQL = SQL_1 & Me!textSessionID & ", " & ctlC.Tag & ");"
dbD.Execute strSQL, dbFailOnError
End If
Next
 
rkc wrote:

Sorry about this, but it's still not working. Clearly I haven't got
the right library (or something) installed because it doesn't recognise
the "value" bit of

ctlC.value = True

("value" isn't among the options I get when I type "ctlC.")

I'm lost.

Steve
 
Stephen Glynn said:
rkc wrote:

Sorry about this, but it's still not working. Clearly I haven't got
the right library (or something) installed because it doesn't recognise
the "value" bit of

ctlC.value = True

("value" isn't among the options I get when I type "ctlC.")
Click to expand...

Try typing ctlC. and looking for ControlType. Is it there?
Nope. Does it work? Yup. Same deal with Value. Why?
Beats me. I guess because not all controls have a value,
but a checkbox does.

Did you try the code or just quit after value didn't show up in the
drop down list?
 
Stephen Glynn said:
rkc wrote:

Sorry about this, but it's still not working. Clearly I haven't got
the right library (or something) installed because it doesn't recognise
the "value" bit of

ctlC.value = True

("value" isn't among the options I get when I type "ctlC.")
Click to expand...


O.K. Seems there was a bit of a problem with my first swat at this.
All controls on the form were being included with the AND test.
Some, like command buttons, don't have a value and would
raise an error. Air code. My bad.

Try ths.

For Each ctlC In Me.Controls
If ctlC.ControlType = acCheckBox then
If ctlC.value = True Then
strSQL = SQL_1 & Me!textSessionID & ", " & ctlC.Tag & ");"
dbD.Execute strSQL, dbFailOnError
End if
End If
Next
 
Hi RKC,

Thanks for catching this one. (I said it was air code<g>).

I'd test the control type and the value separately, because all controls
have a ControlType but not all have a Value (e.g. a Label doesn't have a
Value property):

For Each ctlC In Me.Controls
With ctlC
If .ControlType = acCheckBox Then
If .Value = True Then
strSQL = SQL_1 & Me!textSessionID & ", " & .Tag & ");"
dbD.Execute strSQL, dbFailOnError
End If
End If
End With
Next
 
Back
Top