Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unanswered: criteria under design view on a control in the main form

    I have two tables, tblChangeControlFormDetails and tblInfrastructureRes and they are related by the field -- ChangeControlFormDetailsID. It is a one to many relationship. That is, I have multiple records in the table tblInfrastructureRes for each record in the table tblChangeControlFormDetails.

    (On the side note, table tblInfrastructureRes is also related to table tblCobbCountyContact)

    This field ChangeControlFromDetailsID is the primary key for the table tblChangeControlFormDetails. It is an autonumber.

    I have a form called frmChangeOfControlForm which is used to input data to the tblChangeControlFormDetails. I have a subform frmInfrastructureResAB being called up from within the form frmChangeOfControlForm to hold all the records related to the table tblChangeControlFormDetails.

    In the frmChangeOfControlForm form, I have a list box (lstInfrastructureRes) to list all the records from tblInfrastructureRes which is related to the table tblChangeControlFormDetails. It is not working right at this moment and it is my question. Note, when I list the records from the table tblInfrastructureRes, I list the related record(names) from the table tblCobbCountyContact. Again, tblInfrasturcutreRes and tblCobbCountyContact are related by CobbCountyContactID

    I use the design view to run the query in the list box (lstInfrasturctureRes). it is an unbound list box. Here is my Row Source

    SELECT tblInfrastructureRes.InfrastructureResID,
    tblInfrastructureRes.ChangeControlFormDetailsID,
    tblCobbCountyContact!FirstName &","&[LastName] AS Expr1 FROM
    tblChangeControlFormDetails INNEW JOIN (tblInfrastructureRes INNER JOIN
    tblCobbCountyContact ON tblInfrastructureRes.CobbCountyContactID =
    tblCobbCountyContact.CobbCountyContactID) ON
    tblChangeControlFormDetails.ChangeControlFormDetai lsID =
    tblInfrastructureRes.ChangecontrolFormDetailsID;

    I know this Row Source works because when I hard code one ChangecontrolFormDetailsID number (e.g. say 12345) as the criteria for tblInfrastructureRes.ChangeControlFormDetailsID, it did only list all the records in the tblInfrastructureRes table which is related to the ChangeControlFormDetailsID 12345. That is, even I jump to other ChangeControlFormDetailsID from the from frmChangeOfControlForm, the list box still only gives me all the records related to 12345 in the tblInfrastructureRes table even thought I am in different ChangeControlFormDetailsID. That is a correct behavior I think.
    I want to put some critera under the tblInfrastructureRes.ChangeControlFormDetailsID in the design view so that only those records whose ChangeControlFormDetailsID in the table tblInfrasturcture are matching to the control txtChangeControlFormDetails in the main form will be listed. The control txtChangeControlFormDetails in this main form (frmChangeOfControlForm) holds the ChangecontrolFromDetailsID in the table tblChangeControlFormDetails.

    I don't have any critera right now, the list box lists every record in the table tblInfrastructureRes.

    I tried everything criteria I can think of but nothing work. Here is the one I think it should work but it doesn't

    Forms!frmChangeOfControlForm!ChangeControlFormDeta ilsID (this criteria gave me blank list in all record)

    Any help is most welcome.

    Thanks
    Last edited by sweetmail; 12-25-07 at 12:05.

  2. #2
    Join Date
    Sep 2007
    Posts
    148

    Smile

    With this new select statement, I am able to accomplish what I need. Don't fully understand why it works this way and not the other way. But it works, who cares.

    Thanks

    SELECT tblCobbCountyContact!FirstName & "," & [LastName] AS Expr1
    FROM tblChangeControlFormDetails INNER JOIN (tblInfrastructureRes INNER JOIN tblCobbCountyContact ON tblInfrastructureRes.CobbCountyContactID = tblCobbCountyContact.CobbCountyContactID) ON tblChangeControlFormDetails.ChangeControlFormDetai lsID = tblInfrastructureRes.ChangeControlFormDetailsID
    WHERE ((([tblInfrastructureRes]![ChangeControlFormDetailsID]=[Forms]![frmChangeOfControlForm]![txtChangeControlFormDetailsID])=True));

Posting Permissions

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