Solved Access Compare Form 1 Text Value to Form 2 Text Value

sunnytaru

Member
Local time
Today, 04:59
Joined
Mar 24, 2022
Messages
41
I have two forms 1st Form displays a payment schedule which has a column with field Funding Amount and in the footer Total Project Amount.
2nd Form displays a invoice schedule which has a column with field invoiced amount and in the footer total invoiced.
Is it possible to check if both footer total are a match ? If I add in a text box in form 1 =IIf([TotalProjectAmount]=[Forms]![Form2]![TotalInvoiced],"Matched","Not Matched") ?

I am getting #Name? error... Is there is way to check, need guidance.

Thanks
Taruna
 
Hi. #Name? error could mean you just need to double check your spellings. Try using the Expression Builder to see if it helps.
 
Hi. #Name? error could mean you just need to double check your spellings. Try using the Expression Builder to see if it helps.
Tried that way, still Name error :unsure:
 
I have attached the Tables and Form, The billing Form Text 238 label has the formula.
Hi. I opened your forms, and the Textbox worked fine for me.

1655848513009.png


Remember to have both forms open at the same time.
 
Could it be and issue if I am inserting these forms in in a Tab control ?
Okay, not so much because of the tab control, but more of inserting them as subforms in another form. In that case, the syntax changes a little bit.

Code:
=Forms!MainFormName.SubformControlName.Form!TexboxName
 
Okay, not so much because of the tab control, but more of inserting them as subforms in another form. In that case, the syntax changes a little bit.

Code:
=Forms!MainFormName.SubformControlName.Form!TexboxName
Hello DBguy,

Perfect !Thanks a million it worked !
 
Could it be and issue if I am inserting these forms in in a Tab control
I think you could do this cleaner. That is kind of awkward to do a sum on a form and compare it to a sum on another form. Forms have to be refreshed and both open. I would work at the data level.

I would make two queries
qrySumInvoice (group by projectID)
qryTotalFunding(group by projectID)
now join those two queries by project id

Code:
SELECT qryinvoicesum.projectid,
       qryinvoicesum.invoicesum,
       qrytotalfunding.totalfunding,
       Iif([invoicesum] = [totalfunding], "matched", "not matched") AS Matched
FROM   qryinvoicesum
       INNER JOIN qrytotalfunding
               ON qryinvoicesum.projectid = qrytotalfunding.projectid;

qryMatched qryMatched

ProjectIDInvoiceSumTotalFundingMatched
4295​
$800,159.00​
$815,178.00​
Not Matched

now anywhere in the db you can do a simple dlookup and not dependent on any forms being open. You would also have a query showing all matches and unmatched.

dlookup("Matched", "qryMatched","ProjectID = " & me.ProjectID)
 
Hello DBguy,

Perfect !Thanks a million it worked !
Hi. Glad to hear you got it sorted out. Please consider @MajP's advice. It would be more flexible too, I think.

Good luck with your project.
 
I think you could do this cleaner. That is kind of awkward to do a sum on a form and compare it to a sum on another form. Forms have to be refreshed and both open. I would work at the data level.

I would make two queries
qrySumInvoice (group by projectID)
qryTotalFunding(group by projectID)
now join those two queries by project id

Code:
SELECT qryinvoicesum.projectid,
       qryinvoicesum.invoicesum,
       qrytotalfunding.totalfunding,
       Iif([invoicesum] = [totalfunding], "matched", "not matched") AS Matched
FROM   qryinvoicesum
       INNER JOIN qrytotalfunding
               ON qryinvoicesum.projectid = qrytotalfunding.projectid;

qryMatched qryMatched

ProjectIDInvoiceSumTotalFundingMatched
4295​
$800,159.00​
$815,178.00​
Not Matched

now anywhere in the db you can do a simple dlookup and not dependent on any forms being open. You would also have a query showing all matches and unmatched.

dlookup("Matched", "qryMatched","ProjectID = " & me.ProjectID)
Thanks, I will take this approach.
 
Just remember. Forms don't store data. Tables store data.

Maj's solution is far superior.
 

Users who are viewing this thread

Back
Top Bottom