Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Using one record in a form to trigger updates to other records

    I have a table that poses a series of questions (one record for each) with Yes/No answers (stored in the [tautological?] field ysnYesNo). The questions fall into various categories. For each category, the first question determines whether the subsequent questions are necessary. There is a ysnTrigger field which is checked to identify the first (trigger) question only, and a ysnHide field, which is initally checked for all the subsequent questions (but not the first question).

    I have a form based on the categories, with a subform showing all the questions in the current category for which the ysnHide field is False. This means that, initially, only the trigger question is displayed.

    I have tried writing VBA code to respond to the AfterUpdate event of the ysnYesNo field. If it is set to Yes, the ysnHide fields for the remaining records in the category should be set to False. If it is subsequently reset to No, the ysnHide fields for the remaining records should be set to True. (The ysnTrigger field is used to identify the one record in the category that should never have the ysnHide field set to True.)

    I have tried three methods of achieving what I need:

    (1) Create and run an Update query to reset the ysnHide field for all non-trigger records in the current category. When I try DoCmd.RunSQL, I am told all of the required records cannot be updated due to Lock Violations.

    (2) Create a recordset of all non-trigger records in the current category, cycling through each record, editing the ysnHide field and updating the record. The rst.Update code prompts the Error "Could not update; currently locked by another session on this machine". This occurs even when the records are currently hidden, and therefore not included in the query underlying the subform.

    (3) Use Me.RecordsetClone and cycle through each record, setting the ysnHide field as in (2) above. This works. Half the time. That is, when the trigger question's ysnYesNo field is set to True, and therefore all the other questions are showing, I can reset their ysnHide fields to True, requery the subform, and hide them. But when ysnYesNo is currently set to False, they are already hidden and therefore not part of the RecordsetClone.

    I understand why all this is happening. What I'm stuck on is finding a way around it! There are structural reasons why I don't want the trigger questions to be in one table and the subsequent questions in a second, linked table. I would be happy to use one method (3) for hiding and a different one (1 or 2) for unhiding, but am not sure why the hidden fields are locked when they are not part of the RecordsetClone that only displays the unhidden fields.

    Any ideas or suggestions?

    Thanks and regards

    AliC

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    First, I assume that your back-end is in Access. If you are using SQL Server tables or some other database server, the advice doesn't apply. The is an option on the Jet engine that says use record locking. If that is not selected, Access will lock all records stored in the same storage block, and depending on other settings, all records in the table. I'm not certain how the ACE engine manages record locking, but I think it somewhat similar if you are using a .accdb format database. Otherwise, I can't think of any reason why you would be getting the Lock violations.
    Wendell

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Can you send s zipped compacted database with just the form and relevant tables and just test data

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    WendellB - Yes, you're right, and I wondered if it had something to do with all the records being in the same block as the record in use, and therefore locked as a group.

    Patt - Separating out the required elements into a test database will probably take longer than I can spare on this project! But thanks for offering to have a look.

    I thought I might just get it to work by not filtering out the hidden records in the underlying query, but applying the filter on opening the form. Alas, as I feared, the RecordsetClone seems to apply to the data that remains after the filter has been applied in this way.

    I'll probably get around it by using Conditional Formatting to disable the fields on the records that are marked as hidden. That way, the user can see what questions will follow if they click Yes, but can't answer them if they leave it at No.

    Thanks for your responses.

    Cheers
    AliC

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Duh! I've got the RecordsetClone solution working by turning the filter off at the start of the routine, and back on again at the end. So, in summary:

    - The OnOpen event of the form sets the filter to hide all records with ysnHide set to True
    - If I update the ysnYesNo field on the Trigger record, it turns off the filter, and creates a RecordsetClone
    - Looping through the recordset for all records except the Trigger record, ysnHide is set to the opposite of ysnYesNo
    - The RecordsetClone is closed and the filter turned on again
    - Hey Presto! I can see the records I want to see, depending on the Yes/No answer to the trigger question.

    Thanks again for your time and consideration.

    Cheers

    AliC

Posting Permissions

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