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;

 

[Pat Hartman]

A continuous form is simply ONE form shown multiple times but the ONE form has only ONE set of properties. That is why unbound controls will show the same value for each row.

There is something wrong with your structure. You seem to have two subforms that you want to have the same value in for one field. The scrolling of each subform is independent so there is no way to ensure that both are on the "same" row.

Please post your schema, preferably in a database rather than as a picture. We will help to fix the design problem.

PS, if one of the forms is "dependent" on the other, then YOU, in code will populate the FK field of the dependent form. You can't leave this to the user. The FK field will not even be visible on the dependent subform.

 

[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).

 

[Pat Hartman]

That was going to be my solution except that the two fields are in two different subforms and so not in the same query.

 

[stu_c]

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

 

[Pat Hartman]

OOps, you're right. I got lost in the square brackets. I agree, the query is the best solution. But why the two fields must be the same is still questionable. I would do it differently though.

Select fld1, fld2, IIf(fld3 & "" <> fld4 & "", "Do not Match", Null) As MatchVal
From YourTable.

Concatenating the ampersands will avoid errors when one of the fields is null. I also prefer to "mark" only one side of the condition. It will make the text stand out more. So, show either Match or No Match which ever you want to draw the users attention to. So the IIf() could be:
IIf(fld3 & "" <> fld4 & "", Null, "Match") if showing the "match" result is more important.

In either case, bind the display field to MatchVal and it will change as the values in each row change.

 

[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

 

[Pat Hartman]

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.

 

[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 :/

 

[Pat Hartman]

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

 

[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;

 

[Pat Hartman]

I knew someone would beat me. I've been out all day. Question though, arnel made the assumption that you wanted the words to be null if one f the fields was null. Perhaps you could make a definitive statement since we can't really guess all your business rules. We only know what you want when both of the fields has a value. What happens if one of the fields is null? what happens if both of the fields are null?

 

[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

 

[Pat Hartman]

You cannot compare null to anything. The result of such comparison will always be null which will normally fall into the false path.


null=null is an invalid comparison. as is 3= null.

 

[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