Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

check box is checked if value of textbox appears in another table

Discussion in 'Information Technology' started by socasteel21 via AccessMonster.com, Jul 28, 2006.

  1. socasteel21 via AccessMonster.com

    Guest

    I have 2 forms based on 2 seperate tables(tblWarrantyReg & tblPDI). Each
    table includes a field called "UnitSerialNumber". The 2 textboxes on the
    forms are called "txtUnitSerialNumber". I would like to put a check box on
    the form called "frmWarrantyReg" (based on tblWarrantyReg) that is checked
    whenever the serial number that is in the txtUnitSerialNumber on
    frmWarrantyReg also appears somewhere in the UnitSerialNumber field of the
    tblPDI.

    Does anyone know how to do this? Any help is greatly appreciated.

    Shannan

    --
    Message posted via http://www.accessmonster.com
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    In the ControlSource of your text box, use DLookup() to see the the value
    exists in the field of the other table.

    DLookup() returns Null if the value is not there, so use IsNull() around the
    DLookup() gives you the True if the field is not found. Use Not to reverse
    that.

    The ControlSource of your text box will be something like this:

    =Not IsNull(DLookup("UnitSerialNumber", "tblPDI",
    "UnitSerialNumber = """ & [txtUnitSerialNumber] & """"))

    Remove the extra quotes if UnitSerialNumber is a Number type field (not a
    Text field.)

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "socasteel21 via AccessMonster.com" <u11033@uwe> wrote in message
    news:61128c8c009cc@uwe...
    >I have 2 forms based on 2 seperate tables(tblWarrantyReg & tblPDI). Each
    > table includes a field called "UnitSerialNumber". The 2 textboxes on the
    > forms are called "txtUnitSerialNumber". I would like to put a check box
    > on
    > the form called "frmWarrantyReg" (based on tblWarrantyReg) that is checked
    > whenever the serial number that is in the txtUnitSerialNumber on
    > frmWarrantyReg also appears somewhere in the UnitSerialNumber field of the
    > tblPDI.
     
  4. socasteel21 via AccessMonster.com

    Guest

    This will not change the value of the check box. I already have about 500
    records. I need to add a check box that will be checked if the serial number
    on one form (frmWarrantyReg) exists in the table (tblPDI) related to the
    other form.

    Thanks for your help.

    Shannan

    Allen Browne wrote:
    >In the ControlSource of your text box, use DLookup() to see the the value
    >exists in the field of the other table.
    >
    >DLookup() returns Null if the value is not there, so use IsNull() around the
    >DLookup() gives you the True if the field is not found. Use Not to reverse
    >that.
    >
    >The ControlSource of your text box will be something like this:
    >
    > =Not IsNull(DLookup("UnitSerialNumber", "tblPDI",
    > "UnitSerialNumber = """ & [txtUnitSerialNumber] & """"))
    >
    >Remove the extra quotes if UnitSerialNumber is a Number type field (not a
    >Text field.)
    >
    >>I have 2 forms based on 2 seperate tables(tblWarrantyReg & tblPDI). Each
    >> table includes a field called "UnitSerialNumber". The 2 textboxes on the

    >[quoted text clipped - 4 lines]
    >> frmWarrantyReg also appears somewhere in the UnitSerialNumber field of the
    >> tblPDI.


    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access/200605/1
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    See:
    Calculated fields
    at:
    http://allenbrowne.com/casu-14.html

    As the article explains, you do not want to store a calculated result in
    your database, unless there is a reason why that box should sometimes be
    inconsistent with the data in the tables.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "socasteel21 via AccessMonster.com" <u11033@uwe> wrote in message
    news:61162de019a68@uwe...
    > This will not change the value of the check box. I already have about 500
    > records. I need to add a check box that will be checked if the serial
    > number
    > on one form (frmWarrantyReg) exists in the table (tblPDI) related to the
    > other form.
    >
    > Thanks for your help.
    >
    > Shannan
    >
    > Allen Browne wrote:
    >>In the ControlSource of your text box, use DLookup() to see the the value
    >>exists in the field of the other table.
    >>
    >>DLookup() returns Null if the value is not there, so use IsNull() around
    >>the
    >>DLookup() gives you the True if the field is not found. Use Not to reverse
    >>that.
    >>
    >>The ControlSource of your text box will be something like this:
    >>
    >> =Not IsNull(DLookup("UnitSerialNumber", "tblPDI",
    >> "UnitSerialNumber = """ & [txtUnitSerialNumber] & """"))
    >>
    >>Remove the extra quotes if UnitSerialNumber is a Number type field (not a
    >>Text field.)
    >>
    >>>I have 2 forms based on 2 seperate tables(tblWarrantyReg & tblPDI). Each
    >>> table includes a field called "UnitSerialNumber". The 2 textboxes on
    >>> the

    >>[quoted text clipped - 4 lines]
    >>> frmWarrantyReg also appears somewhere in the UnitSerialNumber field of
    >>> the
    >>> tblPDI.
     

Share This Page