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

[sunnytaru]

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]

Hi. #Name? error could mean you just need to double check your spellings. Try using the Expression Builder to see if it helps.

 

[sunnytaru]

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

 

[theDBguy]

Tried that way, still Name error

Can you post a sample db to demonstrate the problem?

 

[sunnytaru]

Can you post a sample db to demonstrate the problem?

There are other tables associated with forms e.g. dropdown selections, if I do not add those will it be ok?

 

[theDBguy]

There are other tables associated with forms e.g. dropdown selections, if I do not add those will it be ok?

As long as it clearly demonstrates the current problem...

 

[sunnytaru]

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.

 

[theDBguy]

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.

Remember to have both forms open at the same time.

 

[sunnytaru]

Hi. I opened your forms, and the Textbox worked fine for me.

View attachment 101354

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 ?

 

[theDBguy]

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.

=Forms!MainFormName.SubformControlName.Form!TexboxName

 

[sunnytaru]

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.

=Forms!MainFormName.SubformControlName.Form!TexboxName

Hello DBguy,

Perfect !Thanks a million it worked !

 

[MajP]

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

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

ProjectID	InvoiceSum	TotalFunding	Matched
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]

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]

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.

Surely will do !

 

[sunnytaru]

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

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

ProjectID	InvoiceSum	TotalFunding	Matched
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]

Just remember. Forms don't store data. Tables store data.

Maj's solution is far superior.