Access: Automatic checkboxes

[Maelin]

Hello, I am making an Access database and I have a problem that I'm not sure how to solve.

I have a main form, which looks at records from a main table. All other tables in the database contain records, and each of those records pertains to one particular record in the main table. That is to say, all other tables exist in many-to-one relationships with the main table.

The main form has a subform which displays one record at a time from one of those other tables, specifically, records that correspond to whichever record the main form is displaying. Let's call the record that this subform is looking at 'Record A'. All fairly simple so far.

Now, on the subform, I have two checkboxes. They aren't for the user, these are automatic. One is for "Before an event" and one is "After an event". I want them to be automatically checked on or off, depending on whether records exist in a table (a list of events that correspond to records in the main table) that suit some requirements. Consider Record X, in this third table of events. Record X suits the requirements if:

• Record X corresponds to the same item on the main table as Record A
• The 'date' field of Record X is before (or after, depending on the checkbox) the date of Record A

So if there exists at least one event such that Record A is before it, then the "Before an event" box is checked on, and if there exists at least one event such that Record A is after it, then the "After an event" box is checked on.

However, I don't know how to do it. I played with Count() and some queries and things but I got hopelessly lost. Can anybody help me?

Thanks very much in advance,

Maelin

 

[OBP]

It would be much easier for us to help you if you posted the database with some representative data in it.
Basically there are 2 ways to approach it , the first is with a recordset clone or record set and the second is with a query that has it's criteria row set to the value in your main form.

 

[Maelin]

I'm not sure if putting the database on the net would be entirely okay, since I'm doing it for work.

But I'll explain the actual situation. It's a database of cancer patients. The main table is the patient database, and I have other tables including a list of surgery events and a list of radiotherapy courses. The tables are related so that one patient may have several surgery events and radiotherapy courses. It is useful to know whether radiotherapy occurred before or after surgery.

On the main form (looking at one patient record) there is a subform (looking at radiotherapy courses for that patient). The subform is set to Single Form view (one radiotherapy course at a time). The checkboxes are there to say whether that radiotherapy course occurred before or after a surgery event. So, I want them to be checked on if there are records in the Surgery table that fit:

• The surgery event occured for the same patient as the radiotherapy course
• The surgery event occurred before / after the radiotherapy course

I don't know what a recordset clone is. I think I can get a query to find the right records, but I don't know how to test how many such records exist, and I don't know how to use that information to set the checkbox value.

Maelin

 

[OBP]

I assume that you only need one of each or either one to set the respective check box?
I can foresee one snag, if the patient hasn't had surgery there won't be a record to find.
Or will there be a record?

 

[Maelin]

The checkboxes are entirely independent. If there has been some surgery before the radiotherapy, then there's a tick in the "After surgery" box. If there has been some surgery after the radiotherapy, then that's a tick in the "Before surgery" box. If no surgery has occurred, then both boxes are unticked, and if the radiotherapy occurs between two different surgery events, then both boxes are ticked.

 

[OBP]

Do you want the Check boxes to update when you enter a date as well as when the form opens?

 

[cristobal03]

Hey there Maelin, welcome to TSG.

Are the checkboxes bound? I'm assuming they are, but I guess it doesn't really matter if your subform is in Single Form view (just noticed that). Anyway.

These are all guesses on my part. Your main table is a list of patient records. One of your other tables is a list of surgery dates per patient. Another of your many-side tables is a list of radiotherapy dates per patient. And this subform's RecordSource is a third of the many-side tables. You're trying to correlate the surgery dates table and the radiotherapy table for this third table, is that correct? Or am I completely off target?

My first instinct is that if that's the case, you don't need to store the information (the checkbox ticks) in a table. Once you figure out the right query structure, you can implement that logic into unbound form controls and reports.

chris.

 

[OBP]

Hello Chris, long time no see. I have actually carried out this work for Maelin. He sent me the database as he did not wish to post it on the Forum. There is a minor complication in that the Subforms are on Tabs, which makes it more difficult to use recordsets. The check boxes are unbound.
What I did was to create a query and form of the Surgery data based on the Main forms currently displayed Patient ID. I then used VBA to work through the returned records checking if the surgery date(s) were before or after the radiotherapy.
This is the VB
Private Sub Date__Commencement__AfterUpdate()
Dim recount As Integer, count As Integer
Me.Check14 = "no"
Me.Check12 = "no"
'Exit Sub
If IsNull(Me.Date__Commencement_) Or Me.Date__Commencement_ = "" Then Exit Sub
DoCmd.OpenForm "Surgery Dates", , , , , acHidden
DoCmd.GoToRecord acForm, "Surgery Dates", acLast
If Forms![Surgery Dates]![Date] = "" Then Exit Sub
recount = Forms![Surgery Dates].CurrentRecord
For count = 1 To recount
DoCmd.GoToRecord acForm, "Surgery Dates", acGoTo, count
If Forms![Surgery Dates]![Date] < Me.Date__Commencement_ Then
Me.Check14 = "yes"
Else
Me.Check12 = "yes"
End If
Next count
DoCmd.Close acForm, "Surgery Dates"
Me.Parent.Refresh
End Sub

Chris, do you know how to set up RecordSet Clones for Tabbed Subforms?

 

[cristobal03]

Well, the subform objects are still within the parent form's umbrella, so something like

Me.subformControlName.Form.RecordsetClone​

should work.

chris.