Use expression in MS access

[techreader]

Hi all, I am new to here,

I want to highlight a main form employee id in red if it found in other form.

I have a form named "Main" that has continuous records. The first column is employee-ID. I want to check this employee-id against employee-id in another form named "subform".

I went to mainform design view and selected the employee-ID field, right clicked and selected conditional formatting.

I wrote a expression as below:

[forms]![main]![employee-ID]=[forms]![subform]![employee-ID]
and selected red color from the drop down, but didn't yield the result., the employee-ID color in main form remains unchanged.. Kindly suggest

 

[arnelgp]

add a "hidden" Unbound textbox to your main form.
on it's controlSource:

=[Forms]![subform]![employee-ID]

use the "hidden unbound textbox for your conditional format (Expression)

Nz([employeeID], 0) = Nz([hiddenTextboxName], -1)

 

[techreader]

Thank you. When I enter the first formula.. #nameerror appears in he hidden textbox

 

[arnelgp]

are the two forms using different tables?
you can use Query as rowSource of your Main form:

select [employee-ID], DLookup("[employee-id]", "subformTable", "[employee-id] =" & nz([employee-id], 0)) As OtherID
from mainTable;

then use [employee-id] and otherID for your CF.

 

[arnelgp]

here is a demo, see Query1 form.

 

[techreader]

here is a demo, see Query1 form.

Here is my database look like.

I dont have acess to change the Employee table or Employee Form. In Employee form I can add objects in deisgn, but cant change the layout.

In other_Details I can edit.

THe Employee form is based on Employee table.

When ever I open the employee table, It should compare it with Other_details tables or query, if EMPID exists in other_Details table, the color of EMPID in Employee must be in red.

 

[arnelgp]

you don't change the table.
you only add "unbound" textbox to the form (see CountOfID textbox and it's ControlSource).
set its Visible property to No.
add code to it's Open event to hide CountOfID Column (if you go to datasheetview).
also add code to the Timer event of the form (1 secs).
to everytime Refresh CountOfID textbox.

 

[Pat Hartman]

I want to highlight a main form employee id in red if it found in other form.

Forms don't store data. Tables store data.

Your database does not include a subform. And the second table has the name rather than the ID of the employee which doesn't make sense. Employee name is not unique but employee ID is.

Also, I moved the thread. It does not belong in the FAQ section. That section is NOT for questions. It is for use by experts to post information that you will find helpfu.

 

[XPS35]

As said, data is stored in tables.
I only see one form so in don't know what you mean with "if it found in other form".
If the other form is the table "Other_Details", that table should not hold the Emp_name as it should only be stored in the Employee table.

 

[techreader]

you don't change the table.
you only add "unbound" textbox to the form (see CountOfID textbox and it's ControlSource).
set its Visible property to No.
add code to it's Open event to hide CountOfID Column (if you go to datasheetview).
also add code to the Timer event of the form (1 secs).
to everytime Refresh CountOfID textbox.

Thanks ans really works, but the refresh rate happens every 10 sec.. Sometimes 20 sec etc..

 

[arnelgp]

it will highlight in Red upon saving the record in Other_Details.

 

[techreader]

Thank you so much, IS there a possibility to save the other_details from the main form. and I nee one more assistance. I need to sort Employee according to the field "Code" eg. CSK1, Sam, this record should appear only once, irrespective of their EMP_ID. I wrote a query group by code and distinct, but didnt work. the output query should display SAM only once if the code is same.

and, since Sam country is Poland, I need to add all records from Poland table under Sam record, and so on like NY. I have attached an xls for the format. Kindly suggest

 

[arnelgp]

first why do you need to have 2 table for NY and poland when
they can be identified by the Country?
suggest you use only.
if additional country is needed, will you add another table for it?
that is not the optimum way to do it. Just use one table.

see query1 for the Group query, i have duplicate Randy (but different code?).

 

[techreader]

first why do you need to have 2 table for NY and poland when
they can be identified by the Country?
suggest you use only.
if additional country is needed, will you add another table for it?
that is not the optimum way to do it. Just use one table.

see query1 for the Group query, i have duplicate Randy (but different code?)

 

[techreader]

These are in different database, once I filter the main data removing duplicates by codes, I have merge a few details into the main form by their countries.. I don't have a full access to these database just read-only.. So i need to merge a prepare a report