Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    86

    Unanswered: Subform Help - Hopefully easy :-)

    Hi,

    I have a tblMaster and tblScreeningForm and a frmMaster and frmSubMaster

    tblScreeningForm has questions such as salutation complete? etc...
    frmMaster has name, dept. etc.. that is stored in tblMaster,
    what i want is to have a frmSubMaster inside frmMaster as a subform
    frmSubMaster will show the questions stored in tblScreeningForm, and also when these questions are answered (yes/no) the values are stored in tblScreeningForm

    I want the ID from frmSubMaster stored in tblMaster so that the following scenario would apply


    tblMaster captures agent name etc... Sub form would display the questions,
    when tblMaster goes to nextrecord the subform is stored in tblScreeningForm and is cleared for the next assessment, also frmMaster is cleared.
    When going back to the records i want it to show agent name and the answered questions for that agent.

    hope this makes sense.


    thank you

    Scott

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    It's early yet, but I think it makes sense!

    Sounds like you want the basic Parent/Child Form setup. The easiest way to get started is to create your Parent Form, in this case the frmMaster. When you add the subform, make sure the Wizard is turned on (it's the icon with the "magic wand" on the toolbox toolbar.

    The way to "connect" the recordset of the Parent and Child is through the Link Parent/Child Fields properties. You need to have the matching key value on both parent and child forms. The wizard tries to guess your relationship, you can also define your own.

    When you pull up a specific record on the Parent form, it essentially "filters" the child form to all records with a matching key value. When you add a new record to the Child form, it automatically gets the key from the parent.

    Ideally, you use an "Autonumber" field as your parent key so that when you go to a new record, a key value is generated for the child table, otherwise some issues may arise.

    Good luck!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Oct 2004
    Posts
    86
    so...

    tblMaster has PK autonumber
    tblScreeningTable has FK number

    and then relate these two in a one to one relationship?

    the parent would then be tblmaster and child tblscreeningtable?
    is this correct or am i way off?

    thanks

  4. #4
    Join Date
    Oct 2004
    Posts
    86
    Ok ive got this working thanks

    however, i have frmmaster, and this contains agent details etc...
    the sub form is a list of questions asking how an evaluation went on that agent,
    it would be ideal if i could press next record on the sub form, so potentially the the agent could have more than one evaluation on them (per master record)

    but as the ID in the master and child are linked creating a second child causes problems
    can anyone help on this?

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I'm not quite sure what your after - it should work as you describe it.

    Do not setup a relationship in the "Relationships" window - it is not necessary. If you do, make sure it is set to "One to Many" by right clicking on the link. If you have a relationship setup One-To-One, that may be the problem.

    On your Form/SubForm, when a new record is created on the Subform, it automatically applies the lkey value from the parent form to the matching field on the sub form.

    Good luck!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  6. #6
    Join Date
    Oct 2004
    Posts
    86
    great thank you i understand it now, i was under the impression that the child could "create" the next number in the master, but it dosnt it just keeps the same one

    thanks for your help
    much appriciated

Posting Permissions

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