Conditional VBA If Statement per row

[stu_c]

Hi All
I have a Table in a form with multiple fields I am trying to do an IF Statement that if two fields do not Match then the status Text box Box reads "DO NOT MATCH", on after update of the field, I have managed to do it but the issue I am having is that it also updates the other entries, is there a way to do the IF Statement per entry rather than overall?

Obviously Once I have figured this out then I can make Multiple IF statements for what I need but cannot work out why its doing it

Basically I did the top one and showed as THEY MATCH but when I tried the second entry it changed both

Public Function Func_ReferenceStatus()
    If [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTHRReferenceNumber] <> _
    [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTOCCReferenceNumbers]Then

    [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTReferenceStatus] = "DO NOT MATCH"


Else
     [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTReferenceStatus] = "THEY MATCH"

End If
End Function

 

[arnelgp]

just a guess:

Public Function Func_ReferenceStatus()
    Dim a As String, b As String

    a = [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTHRReferenceNumber] & ""
    b = [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTOCCReferenceNumbers] & ""

If a <> b Then
 
    [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTReferenceStatus] = "DO NOT MATCH"


Else
     [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTReferenceStatus] = "THEY MATCH"

End If
End Function

 

[arnelgp]

I think you are using Continuous Form?
use Query and use it as Recordsource of your Form.
Create your new function:

Public Function Func_RefStatus(ByVal TXTHRRef As Variant, ByVal TXTOCCRef As Variant) As String

    If IsNull(TXTHRRef) Or IsNull(TXTOCCRef) Then
        Exit Function
    End If
    
    TXTHRRef = TXTHRRef & ""
    TXTOCCRef = TXTOCCRef & ""
        
    Func_RefStatus = IIf(TXTHRRef = TXTOCCRef, "THEY", "DO NOT") & " MATCH"

End Function

Call it in a New Query:

SELECT TXTReferenceStatus,
TXTOCCReferenceNumbers,
Func_RefStatus(TXTReferenceStatus, TXTOCCReferenceNumbers) As RefStatus
FROM yourTable;

 

[ebs17]

I have a Table in a form

You simply react via a calculation in a query. This also reacts immediately to value changes.

SELECT
   anything,
   IIF(FieldA <> FieldB, "DO NOT MATCH", "THEY MATCH") AS Info
FROM
   TableX

Strictly speaking, such a display is superfluous and redundant. Actually, one makes a test in order to do further with the corresponding quantity depending on the case (= filtered).

 

[stu_c]

Hi all
The two fields are in the same form but within a subform?

 

[stu_c]

Basically they two reference numbers from different departments sometimes they match which means it goes to a different sorting agent and other times they don't, the text "DO NOT MATCH" will be the name of the sorting agent but ease of use for the code I have used that until I get it working

 

[stu_c]

Did you try changing your form's RecordSource to a query that calculates the "match" result? That is essentially a no code solution once you build the query and bind the calculated field to a form control. Access takes care of populating the calculated control as the underlying values get changed.

That's a little beyond me tbh, I. Still learning VBA :/

 

[stu_c]

Okay I'll have a look and post it up tomorrow, thank you

 

[arnelgp]

without using a Function.
and you don't want to say "Do Not Match" if either of your fields is Null.

SELECT TXTReferenceStatus,
TXTOCCReferenceNumbers,
    Switch(IsNull(TXTReferenceStatus + TXTOCCReferenceNumbers), Null, 
                          TXTReferenceStatus <> TXTOCCReferenceNumbers, "Do Not ", 
                          True, "They ") + "Match" As RefStatus
FROM yourTable;

 

[ebs17]

FieldA = FieldB ==> "match" ... that's a simple and, in my opinion, clear statement. In this way, you can perform a logic inversion (<> => =) in the test, and the expression remains simple and can be used directly in SQL.

? IIF(3=3,"match","no"), IIF(3=7,"match","no"),IIF(3=null,"match","no"),IIF(null=null,"match","no")
match         no            no            no

 

[stu_c]

Open the QBE.
Select the table that the form is bound to.
Switch to SQL view.
Copy the code and paste it here - not a picture of the code, post the actual code.

I will add the IIf() to the query to achieve the result you attempted with your original If statement unless someone beats me to it

the SQL is:

SELECT TBLALLHRDetails.HRRefID, TBLALLHRDetails.IDFKFullStaffDetailsID, TBLALLHRDetails.HRReferenceNumber, TBLALLHRDetails.OCCReferenceNumbers

FROM TBLALLHRDetails;

 

[arnelgp]

SELECT TBLALLHRDetails.HRRefID,
    TBLALLHRDetails.IDFKFullStaffDetailsID,
    TBLALLHRDetails.HRReferenceNumber,
    TBLALLHRDetails.OCCReferenceNumbers,
    Switch(IsNull(TBLALLHRDetails.HRReferenceNumber + TBLALLHRDetails.OCCReferenceNumbers), Null, 
                  TBLALLHRDetails.HRReferenceNumber <> TBLALLHRDetails.OCCReferenceNumbers, "Do Not ", 
                          True, "They ") + "Match" As RefStatus
FROM TBLALLHRDetails;

 

[stu_c]

SELECT TBLALLHRDetails.HRRefID,
    TBLALLHRDetails.IDFKFullStaffDetailsID,
    TBLALLHRDetails.HRReferenceNumber,
    TBLALLHRDetails.OCCReferenceNumbers,
    Switch(IsNull(TBLALLHRDetails.HRReferenceNumber + TBLALLHRDetails.OCCReferenceNumbers), Null,
                  TBLALLHRDetails.HRReferenceNumber <> TBLALLHRDetails.OCCReferenceNumbers, "Do Not ",
                          True, "They ") + "Match" As RefStatus
FROM TBLALLHRDetails;

Thank Arne,
I will have to learn SQL by the looks of things , just for my learning if I wanted to add another statement to this for example if one of the fields has "N/A" then the RefSatus shows blank how would I do this? hopefully then I can learn a little more form this

 

[arnelgp]

change this portion to:

Switch(IsNull(TBLALLHRDetails.HRReferenceNumber + TBLALLHRDetails.OCCReferenceNumbers) Or
        TBLALLHRDetails.HRReferenceNumber="N/A" Or
        TBLALLHRDetails.OCCReferenceNumbers = "N/A", Null,
        TBLALLHRDetails.HRReferenceNumber <> TBLALLHRDetails.OCCReferenceNumbers, "Do Not ",
         True, "They ") + "Match" As RefStatus

 

[stu_c]

so the "N/A", Null, the null it here is the actual outcome of what will show?

 

[arnelgp]

Null, the null it here is the actual outcome of what will show?

the output is Null. and Null means "nothing", so you will see blank/nothing.

 

[gemma-the-husky]

Hi All
I have a Table in a form with multiple fields I am trying to do an IF Statement that if two fields do not Match then the status Text box Box reads "DO NOT MATCH", on after update of the field, I have managed to do it but the issue I am having is that it also updates the other entries, is there a way to do the IF Statement per entry rather than overall?

Obviously Once I have figured this out then I can make Multiple IF statements for what I need but cannot work out why its doing it

Basically I did the top one and showed as THEY MATCH but when I tried the second entry it changed both

Public Function Func_ReferenceStatus()
    If [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTHRReferenceNumber] <> _
    [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTOCCReferenceNumbers]Then

    [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTReferenceStatus] = "DO NOT MATCH"


Else
     [Forms]![FRM_TBLALL_Details]![SSFRM_TBLALL_HRDetails].[Form]![TXTReferenceStatus] = "THEY MATCH"

End If
End Function

View attachment 106216

This may already have been asked, but why do you get to a situation where you are trying to compare two values like this? It sort of indicates that there is somethnig amiss with the table design.