Results 1 to 3 of 3

Thread: Subform Problem

  1. #1
    Join Date
    Aug 2004

    Red face Unanswered: Subform Problem

    I would like to design a form that pulls data from another by comparing two values.

    The form has to compare two tables an produce records greater than 100 and less than 100 from an inputed value. i.e

    Table 1: Size: 500
    Table 2 (Sub form): contains may sizes but only shows 400, 475, 510, 535 as they are greater than 400 and less than 600.

    Sorry if my problem is as clear as mud but its quite difficult to explain.

    Anyone have any suggestions?

    Last edited by weedavie23; 02-22-06 at 07:06.

  2. #2
    Join Date
    Oct 2004
    Oxfordshire, UK
    1. Create the main form, frmMain. The RecordSource property should be Table 1. Make sure that there is a control, txtSize, bound to the field [Table 1].[Size]
    2. Create the subform, frmMain_Sub. Create a parameter query which will be the RecordSource of frmMain_Sub. The query SQL will look like something this:

    SELECT [Table 2].* FROM [Table 2] WHERE ((([Table 2].[Size Field])>=([Forms]![frmMain]![txtSize]-100)) AND (([Table 2].[Size Field])<=([Forms]![frmMain]![txtSize]+100)));

    Alternatively you could copy and paste the SQL string directly into the RecordSource property of frmMain_Sub.

    3. Create a subForm control, subMain, on frmMain. Make sure the SourceObject is frmMain_Sub. If there is a relationship between Table 1 and Table 2 make sure that the values of LinkChildFields and LinkMasterFields are correct. Problems might occur if the size fields in Table 1 and Table 2 have the same name. Access tries to intelligently create LinkMasterField and LinkChildField values, and could create a link between the "Size" fields if your Tables are not related.

    This scheme should mean that as you navigate records in frmMain, related and/or filtered records should appear in your frmMain_Sub.
    Last edited by MyNewFlavour; 02-22-06 at 10:34.

  3. #3
    Join Date
    Aug 2004
    Thanks a lot, I really appreciate this. I'm also very suprised that anyone could make sense of my question in the first place

    Thanks again mate.


Posting Permissions

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