1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access: Automatic checkboxes

Discussion in 'Business Applications' started by Maelin, Jul 17, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. Maelin

    Maelin Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    3
    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
     
  2. OBP

    OBP It's My Birthday!

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  3. Maelin

    Maelin Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    3
    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
     
  4. OBP

    OBP It's My Birthday!

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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?
     
  5. Maelin

    Maelin Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    3
    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.
     
  6. OBP

    OBP It's My Birthday!

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Do you want the Check boxes to update when you enter a date as well as when the form opens?
     
  7. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    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.
     
  8. OBP

    OBP It's My Birthday!

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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?
     
  9. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Well, the subform objects are still within the parent form's umbrella, so something like

    Me.subformControlName.Form.RecordsetClone​
    should work.

    chris.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/483955

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice