Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update form based on other forms' list (03)

    Hello guys,

    I'm trying to create a Address database, which will hold records of all Personnel which ever took part in this project (tblPersonnel.
    The relational table joinPersonnelPosition links that table to the function over the years.

    I could have done this all in one table, but my idea is that some people participate over 5 years and it would be easier just selecting them over "frmPersonnelSelection" and not entering every detail of their addres every year.
    To see what i basically mean run the database and refer to frmPersonnel and "Add new contact" button.

    Now my two problems: "New Record" works just fine a completely new address gets added to tblPersonnel and the Position info the jointable.
    But I just don't know how to put data from the previous years into the data fields.

    Hmm this is hard to explain...

    Let's run through a small example:
    Mr. Dennis Gaida (FirstName, LastName) was Conference Manager (Position) in 2004 (Year)
    in 2005 (Year) he will be the Financial Manager (Position)
    Instead of entering his Name, Address etc. new in 2005 I'd like to have an option to copy the data from 2004 so that I only have to select 2005 and give him a certain position.

    My final goal will be that I can show what every person did over the years (e.g. Dennis Gaida Conference Manager 2004, Financial Manager 2005).

    Hope I gave you guys enough information and the database will tell you more than I could.

    Dennis

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update form based on other forms' list (03)

    Just set the Navigation Buttons property of the sbfPersonnel subform to Yes. The user will be able to add and view new positions very easily.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update form based on other forms' list (03)

    Not what I wanted.
    Let me try to explain again:

    User wants to Add Contact "Gaida, Dennis" to 2005's Personnel, so this is what he does:

    Open frmPersonnel, click on Add Contact
    Select "Refer to existing Address", hit OK
    select Gaida, Dennis, Conference Manager, 2004 from List hit OK

    NOW the Gaida, Dennis record should be visible in frmPersonnel, but with "NEW" sbfPersonnel Data, meaning Year 2005 and Position etc. yet to be set.

    Hope this helps.
    Dennis

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update form based on other forms' list (03)

    I don't understand, it seems unnecessarily complicated to me. Entering a record directly in the subform is much easier.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update form based on other forms' list (03)

    I just tried what you suggested and you are right it works this way.

    I still wanna go the way over the new form (could be done on the same form too of course). But what would the code be for insterting old data?

    The rowsource of the whole frmPersonnel and sbfPersonnel is restricted to show ONLY records from the selected year - how to show some record (via the list in frmPersonnelSelection) with another year than chosen?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update form based on other forms' list (03)

    Take away the restriction?

  7. #7
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update form based on other forms' list (03)

    That would bloat up the form after some years. IF it would show everything we'd have 200 records after some while.

    The form should only show the current years' Personnel.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update form based on other forms' list (03)

    You could keep the restriction for the main form, but remove it from the subform, so that you only see the selected year's personnel in the main form, but all related records in the subform. If you want the subform to display records for the selected year exclusively, but records for other years too, you have a contradiction.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update form based on other forms' list (03)

    Right, now here is where my seperate form comes in:

    Open up frmPersonnelSelection and select somebody available. Now with hitting OK the record of Mr. X should be available in frmPersonnel but with data from the actual year!.
    So what cmdOK has to do is to ApplyFilter to frmPersonnel to show only Mr.X and it should ADD a record to joinPersonnelParticipation with the actual year for Mr. X
    - this way all restrictions could remain active and the form would stay nice and clean.

    I betcha that must be some SQL Code i'm not familiar with yet.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update form based on other forms' list (03)

    See if the attached does what you want.

    I added a first, hidden column to the list box in frmPersonnelSelection, because we need the PID.

    The code behind the OK button adds a new record to the join table, requeries frmPersonnel then locates the person and the subrecord just added.

  11. #11
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update form based on other forms' list (03)

    If have to understand the code yet - but this is IT. Thanks a lot Hans. Exactly what I intended.

    Now I only have to add 2 buttons to delete the whole contact (in tblPersonnel and joinPersonnelPosition) and only the reference in joinPersonnelPosition. Then the form will be finished.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update form based on other forms' list (03)

    You should create relationships between your tables. If you join tblPersonnel and joinPersonnelPosition on PID, with referential integrity enforced and cascading deletes turned on, deleting a record in tblPersonnel will automatically delete all related records in joinPersonnelPosition.

  13. #13
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update form based on other forms' list (03)

    Yeap did that already, I'm right now trying to figure out the SQL Code for a button which only deletes the data in joinPersonnelPosition.

    This could be done over the data markers - yes - but I think an additional button wouldn't be wrong here.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update form based on other forms' list (03)

    You can put a button in sbfPersonnel, with the line

    RunCommand acCmdDeleteRecord

    in the On Click event procedure for the button.

  15. #15
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update form based on other forms' list (03)

    Thank you Hans.

    Problem solved [img]/forums/images/smilies/smile.gif[/img]

    Dennis

Posting Permissions

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