Solved Access Compare Form 1 Text Value to Form 2 Text Value (1 Viewer)

sunnytaru

Member
Local time
Today, 05:31
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:31
Joined
Oct 29, 2018
Messages
21,357
Hi. #Name? error could mean you just need to double check your spellings. Try using the Expression Builder to see if it helps.
 

sunnytaru

Member
Local time
Today, 05:31
Joined
Mar 24, 2022
Messages
41
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:
 

sunnytaru

Member
Local time
Today, 05:31
Joined
Mar 24, 2022
Messages
41
As long as it clearly demonstrates the current problem...
I have attached the Tables and Form, The billing Form Text 238 label has the formula.
 

Attachments

  • Database6.accdb
    640 KB · Views: 118

theDBguy

I’m here to help
Staff member
Local time
Today, 02:31
Joined
Oct 29, 2018
Messages
21,357
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:31
Joined
Oct 29, 2018
Messages
21,357
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
 

sunnytaru

Member
Local time
Today, 05:31
Joined
Mar 24, 2022
Messages
41
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 !
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:31
Joined
May 21, 2018
Messages
8,463
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)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:31
Joined
Oct 29, 2018
Messages
21,357
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.
 

sunnytaru

Member
Local time
Today, 05:31
Joined
Mar 24, 2022
Messages
41
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:31
Joined
Feb 19, 2002
Messages
42,970
Just remember. Forms don't store data. Tables store data.

Maj's solution is far superior.
 

Users who are viewing this thread

Top Bottom