Conditional Formatting based on another form?

[ThePoz]

Is it possible to format a field in a datasheet view of a form if the value of a field on another form is not equal to the field I want formatted?

To keep simple: say i have a form that shows invoices and a person enters the summary or totals in a variety of catagories. I have another form that will roll up individual charges that make up the catagories...entered by someone else. what i would like is for the form with the invoice summary to highlight red in the catagories where the summed total of the detail does not equal the summary entered.. kind of a bit of quality control to know that the people data entering details are doing correctly....

i can get to work perfectly if i query the summary and detailed totals in a query because i just say if field "x" <> field "y" but that renders the form incapable of putting in new invoices or editing an existing one because of the calculated query bit.

so i tried the update table query thing figuring if i hard wire the sums into the
table i could just select... but ran into the cant update because calculated blah blah...

i read to try temp tables but am not that skilled i think... so i figure if i can just have the conditional formatting compare the forms and records it might be easy.

I basically need form 1 to do something like this... to make sure its comparing the right records.. so i figure form 1 [invoice #] = form 2 [invoice #] and form 1 [field in question] <> form 2 [field in question]... if the field in question is not equal then to highlight red.... on form 1.

sorry if in advance if im using incorrect terms. but i have used this site before and done some really cool things thanks to so many of you out there who are just brilliant on Access. my utmost thanks in advance for any thoughts...

 

[spikepl]

Can you explain the purpose of this again?

Because it seems that you have a table with some numbers from which Access can calculate a sum, and then in some other table you want people to type the same number, and let Access complain if they typed some other number in stead? What is the point of asking for input when you already know the value you want input?

 

[ThePoz]

Spike,

In essence you got it..... why? well because, well not really sure j/k... let me be the first to say i am more than positive i have probably broken every cardinal rule for access and normilization but i work for government and well my office needed a way to have information captured for the 15 different proframs we run and prevent cross enrollment and dupilication and all that. and while im sure it could be designed better or more proper .. it has worked beautifuly for someone who had never picked up a bookm on access... thanks in part to guys like you on this site.

ok so back yo my issues.. yes this is clearly duplication (government ) but heres why, when a invoice/ claim comes in the summary information must be tracked and entered so we can estimate the amount of claims that will be paid that month.... these claims come in on vouchers (in excel another of my designs) it has a summary page so its just a simple transfer of the summary numbers.. however the invoice will later be fully eneterd (all individual claims not summary) by another unit member. this may be days or a week later.. at that time i want the detail to sum up and check against the summary entered earlier and there i want it to compare as a quality control..

this is where my previous attempts fall apart because it rendered the ability change the summary entered if it was wrong or in error...hmm i guess i could make a data entry form for the sumary info and leave the datasheet just as viewing, but being a novice and limited in my knowledge that i designed it to how i want it to look for us and it has worked. so i figured if i could find a way to conditional format based on another form ... id be able to keep my current configuration

sorry for long winded reply

 

[vbaInet]

I've extracted the relevant parts:

Is it possible to format a field in a datasheet view of a form if the value of a field on another form is not equal to the field I want formatted?

I basically need form 1 to do something like this... to make sure its comparing the right records.. so i figure form 1 [invoice #] = form 2 [invoice #] and form 1 [field in question] <> form 2 [field in question]... if the field in question is not equal then to highlight red.... on form 1.

Will form 2 be opened before form 1? Otherwise, include the field in form 1's recordset and perform the comparison that way.

 

[ThePoz]

vbaInet,

I think, if im following you right, I tried to do that by including the summed totals in the record set by querying them. That worked perfectly to highlight if the records didn't match. However that left the record un-updatable. If im not following you correctly I apologize.

To answer your question they will both be open. I have a main form and on that form I have a subform (form 1 in my example where the summary info is added) I then have a tabbed pageon main form (which includes another sub form, Form 2 ,in example, that allows for the detailed info to be added) So I would like subform 1 to read the rolled up detail info on subform 2 and highlight if off. In my question I just kept it as can a form reference another form to just try and keep simple...I could work out the additional sub form references if need be later... I just wanted something to start with.

and the datasheet views will show all the invoices...so if there were 5 I would like it to compare all 5 invoices to the relevant summed categories of al 5 in the detail section

Many thanks.

 

[vbaInet]

Ok. Let's see the SQL of both forms.

 

[ThePoz]

Vba,

I got it to work.... sort of.... I was not using the correct naming rules to go between the main form to the other sub etc..

so i got it to work sort of by doing this....

[Forms]![mainform]![subform1].[Form]![Invoice #]= [Forms]![mainform]![subform2].[Form]![Invoice #] And [Forms]![mainform]![subform1].[Form]![Salary]<> [Forms]![mainform]![subform2].[Form]![Salary check]

It highlights nicely, however, it only seems to work on whatever record that subform 2 is on (that is it will higlight all of the records in teh subform 1 if they do not match the current record of sub form 2).... it doesnt match the invoice numbers then check to see if the corresponding Salary for that inovice number matches....

so i removed the first part of the equation and it does the same... so its seems that it is not cycling though all the records..... does this have somethign to do with a better way of telling Access what two records to compare... like an ID field... but since both queries they might not = same number?

I'ts easy to thik it in my mind but a little hard to express sorry if im confusing you.

Luke

 

[vbaInet]

I'll re-iterate...

Ok. Let's see the SQL of both forms.

 

[ThePoz]

By that you just want the SQL langauge of the quries i used to populate each form?

 

[vbaInet]

That's right!

 

[ThePoz]

ok this would be the SQL for the 1st subform (the main form link is where the user selects the contract and it opens a pop up modal form so that you can never navigate away from the form (unless close) so the "contract link" will always pertain. Thats how i filter out the other contracts. information for all forms..
This query pulls from a table and on the form the user would enter in the summary info..striaght across a row
SELECT [Contract Category Expenditures].ID, [Contract Category Expenditures].ContractID, [Contract Category Expenditures].[Invoice #], [Contract Category Expenditures].[Salary], [Contract Category Expenditures].[Fringe], [Contract Category Expenditures].Contractual Services], [Contract Category Expenditures].[Travel], [Contract Category Expenditures].[Equipment], [Contract Category Expenditures].[Space/Property & Utilities], [Contract Category Expenditures].[ Operating Expenses], [Contract Category Expenditures].[Other]
FROM [Contract Category Expenditures]
WHERE ((([Contract Category Expenditures].ContractID)=[Forms]![frmMain]![Contract Link]))
ORDER BY [Contract Category Expenditures].[Invoice #];

this is SQL for the 2nd sub form. this pulls info from a cross tab query that sums up all the category detail claim totals... so that invoice 1 on this form would have all the category totals in the record. The numbers 0-7 are because the form that enters the details is tabbed for each category...salary, fringe, etc...so i had the form filter only the correct items for the catagory baed on the tab number...

SELECT [Exp Detail Sum].ContractID, [Exp Detail Sum].[Invoice #], [Exp Detail Sum].[0] AS [Salary Check], [Exp Detail Sum].[1] AS [Fringe Check], [Exp Detail Sum].[2] AS [Contractual Services Check], [Exp Detail Sum].[3] AS [Travel Check], [Exp Detail Sum].[4] AS [Equipment Check], [Exp Detail Sum].[5] AS [Space/Property & Utilities Check], [Exp Detail Sum].[6] AS [Operating Expenses Check], [Exp Detail Sum].[7] AS [Other Check]
FROM [Exp Detail Sum]
WHERE ((([Exp Detail Sum].ContractID)=[Forms]![frmMain]![Contract Link]));

hope this is what you were looking for

 

[ThePoz]

vba,

I solved it....Not by reference the other subform and trying to tell Access how to compare the records I wanted it to and to cycle through all records and do same...But,

I decided to try again on getting the info on the 1st subform. So I used the dlookup function to populate fields with the data from the sum query on subform 1. This got the info I needed while also allowing the records on subform 1 to be edited or new ones added etc...

Then the conditional format was easy... Just a simple [Field "x"] "does not equal" Field "y"].. works perfect for all records in datasheet view not just current one.

 

[vbaInet]

I decided to try again on getting the info on the 1st subform. So I used the dlookup function to populate fields with the data from the sum query on subform 1. This got the info I needed while also allowing the records on subform 1 to be edited or new ones added etc...

Yes, that's what I mentioned you should do in the first place so good to see that you did it successfully.

However, I'm curious why you're using a crosstab query in your form. Is it only for reference? Can you upload a cut down version of your db so that I can see what you've done?