Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    58

    Unanswered: Automatic checkbox in table/query

    I have a table with subject names as headings and formatted as check boxes, eg:

    ref, english, maths, science, etc
    1,Y,N,Y,etc
    2,N,N,Y,etc
    3,N,N,N,etc

    I want to add another column either onto the table or as part of a query that will enable the check box if there are any enabled check boxes in the other columns, ie, in the above example it would be enabled for rows 1 and 2 but not for 3. Ideally it would be fully automatic, as the number of subject colums might change and I don't really want to have to modify the code each time.

  2. #2
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    It seems to me you should have a table of Subjects.
    English
    Maths
    Science
    etc.
    Then, whatever subject is related to this table will serve as a foreign key field in tblSubject (or a linking table, probably).

    w

  3. #3
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    If you don't want to redesign your as application (ref: wazz) then you could use an update query. It's not very elegant and the most concise code I can think of relies on the fact that a False/No value in a check box is zero. So if the sum of all checkboxes is non zero then at least one of them is marked. Here's a sample SQL string.

    UPDATE Table1 SET Table1.anysubject = Yes
    WHERE (([english]+[math]+[french])<>0);

    Of course this does not cover the situation where the summary check box presently is true but should be reset to false.

    Neither does it cover the situation if your checkbox can have the third, null value interpreted as 'unknown'.

    Finally it does not cover the situation where you have non standard values in your checkboxes. By convention Access uses zero as false/no and minus one as true/yes. However it is possible to have any non zero integer as your checkbox value and this will also be interpreted as true. Just suppose you have plus one and minus one; the above SQL will 'think' that none of the checkboxes are marked.
    Rod

    fe_rod@hotmail.com

  4. #4
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Just had another thought. (That's two already this week and I'm only allowed five!)

    Rereading your post you may want to update this checkbox in 'real time' and not in a mass batch update as I describe above. Presumably you have a form with each checkbox displayed. You need to link some code to the AfterUpdate event of each and every checkbox, plus you need to include the new column/checkbox on your form, either disabled or not visible.

    The code behind each event is very similar to the SQL in my previous post.

    If Me.math + Me.french + ...... <> 0 then
    Me.anysubject = true
    else
    Me.anysubject = false
    end if
    Rod

    fe_rod@hotmail.com

  5. #5
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    whoa. a bunch of questions if you have the time:

    how does the code work if you are just browsing and don't update anything? should it also be on the oncurrent event? or is a field effectively 'updated' when a new record is opened?
    is the new checkbox excluded from the first line of code?
    does making the new column/checkbox disabled/hidden exclude it from the calculation?
    i'll stop there. i dig the -1/0 logic.
    w

  6. #6
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    wazz, hi.

    "how does the code work if you are just browsing and don't update anything?"

    If you don't change the value in any check box then the AfterUpdate event is never raised and the code never executes.

    "should it also be on the oncurrent event? "

    This is somewhat redundant, kind of like belt and braces (or belt and suspenders if you are 'Stateside). To put the code also in the OnCurrent event would provide a self-correcting feature: if there was an error then just visiting the record would correct that error even if you changed none of the check boxes.

    "or is a field effectively 'updated' when a new record is opened?"

    Much the same answer as above. If the default value for all check boxes (or more accurately, all boolean attributes on the table) is set to 'No' then any brand new record is automatically correct. If you change a value in any check box when adding a record then the AfterUpdate event is raised and the code executes.

    "is the new checkbox excluded from the first line of code?"

    Yes

    "does making the new column/checkbox disabled/hidden exclude it from the calculation?"

    No. I suggest it is disabled to stop the user fiddling with it - they tend to do these things if left alone even for a short time. Alternatively you can hide it; now not only can the user not fiddle with it he/she can't see it and does not get confused. Users also get confused very easily. However it needs to be on the form for the code to be able to 'poke' the value into it (second and fourth lines of the code).

    (With apologies to users everywhere.)
    Rod

    fe_rod@hotmail.com

  7. #7
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    No. I suggest it is disabled to stop the user fiddling with it - they tend to do these things if left alone even for a short time.

    LOL

    thanks. gtg. 'preciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •