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

[socasteel21 via AccessMonster.com]

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

 

[Allen Browne]

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.)

 

[socasteel21 via AccessMonster.com]

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

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.

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.