Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    nested forms (not) refreshing (Access 2002/sr1)

    Hi, hope someone can point me in the right direction (hope I've included enough info!).

    I have a DB with 5 tables in it, Employee_Details, Roles, Employee_Roles, Training, Targets. The relationships are:
    (Employee_Details) 1:Many (Employee_Roles)
    (Roles) 1:Many (Employee_Roles)
    (Employee_Roles) 1:Many (Training)
    (Employee_Roles) 1:Many (Targets)

    I have a form based on Employee_Details showing an employee's name & other details, within that I have a subform in list view listing an employee's roles within the institution. Also on the main form I have 2 further subforms with the employee's training and targets. The aim is for the training & targets subforms to show the records related to the highlighted role in the role subform. It is these 2 subforms that have me pulling (what's left of) my hair out.

    I think I have the parent/child links set up correctly (they are the same for both the Training and Targets subforms) and while the Training subform works like a gem, updating correctly, the Targets form stubbornly displays only the records related to the employee's first role; what it _should_ show when initially invoked, but not changing as different roles are selected.

    I also have these forms and subforms based directly on the underlying tables, and have a gut feeling that they should be based on queries (?).

    Hope someone has persevered with all the waffle above and understands it! I can post (example) files to the board if necessary/desired.

    regards

    Stuart

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: nested forms (not) refreshing (Access 2002/sr1)

    What are the parent/child linking fields for both the subform that works and the one that doesn't? You could put a line of code in the Current event of your Roles subform to force the Targets subform to requery. Something like "Parent.subTargets.form.requery".

    And there should be a problem with using tablenames as the recordsource for the subforms.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: nested forms (not) refreshing (Access 2002/sr1)

    Ta for the quick response. I am a little wary of resorting to code (not being particularly au fait with VBA, I tend to think that I'm more likely to add problems that way than solve them!). The names below are a little different as I was using the names from memory, hope that isn't too confusing; now I am cutting and pasting. I am stuck with the spaces and names as this isn't my own DB but a colleague's (to whom I am the mug 'expert' (ha!)). The fields are as follows:

    For the [Frm Staff Targets Subforrm] (spelling error I now have to live with!) - which doesn't work
    Child: EmployeeRolesID (PK of the Employee_roles)
    Parent: [Frm Staff Roles Subform].Form![EmployeeRolesID] - where [Frm Staff Roles Subform] is the first subform listing staff responsibilities.

    For the Frm Staff Training Subform] - which works
    Child: EmployeeRolesID (PK of the Employee_roles)
    Parent: [Frm Staff Roles Subform].Form![EmployeeRolesID]


    I thought that if I linked the PK to the FK in both cases it would work. It's probably some schoolboy error I've made.

    Many thanks again

    Stuart

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: nested forms (not) refreshing (Access 2002/sr1)

    I see no apparent reason why it shouldn't work. Does the Targets subform show the correct info for the first role for each employee you select?

    I would try adding that simple line of code to your Current event in your subform [Frm Staff Roles Subform]. For whatever reason, Access doesn't seem to be requerying that Targets subform. By forcing it to requery, we can at least determine if that is indeed the problem.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    New Lounger
    Join Date
    Jul 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: nested forms (not) refreshing (Access 2002/sr1)

    Ta for that. I looked at the employee roles subform (the one controlling the 2 Training & Targets subforms).

    The nesting had been created by a wizard and it had automatically put in "Me.Parent![Frm Staff Training Subform].Requery" on the OnCurrent event, so I added the line "Me.Parent![Frm Staff Targets Subforrm].Requery" on the next line and it works like a treat. I hadn't delved into there, so didn't realise that the code was helping the initial refresh, so the refresh had to be hardcoded in.

    Many thanks for your help.

    regards

    Stuart

Posting Permissions

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