Continuous form problem.

[John Sh]

I have a continuous form where the field "Notes" is reliant on the results of a few tests done on other fields.
All of the test need to be done on each record as the form loads.
These tests include checking for no data in a field, dcount a list for duplicate entries and comparing the entries in two fields.
The results of these tests will populate the "Notes:" field.
How do I achieve this as the form loads, or is scrolled?
I imagine each test is to be done with a query but how to combine the three queries?

test 1 checks for no data in a particular field and returns "No Data"
test 2 "dcounts" the table for duplicate entries in an image number field and returns "Duplicate" if the result is greater than 1.
test 3 Checks two fields with the same data from different sources. If different returns "Family".
I assume the tests are to be done with queries but how do I run all three queries on each record as it loads?
I am running the tests using "if" statements in the "oncurrent" event but this only populates as I change records.

 

[arnelgp]

you can create a User-Defined Function on Module1 and call it on your Query.
here is a sample function:

' there should be an Autonumber field
' you should pass the autonumber field value
Public Function fnTest(ByVal ID As Long) As String

    Dim strMsg As String
    Dim s1 As String, s2 As String
    
    'test 1
    'check for no data in a particular field.
    If Len(DLookup("TheFieldToCheck", "YourTable", "ID = " & ID) & "") = 0 Then
        strMsg = strMsg & vbCrLf & "Test1: No Data"
    End If
    
    'test 2
    'check for duplicate
    If DCount("TheFieldToCheck", "YourTable", "ID = " & ID) > 1 Then
        strMsg = strMsg & vbCrLf & "Test2: Duplicate"
    End If
    
    'test 3
    'check for same data
    s1 = DLookup("Source1", "YourTable", "ID = " & ID) & ""
    s2 = DLookup("Source2", "YourTable", "ID = " & ID) & ""
    
    If Len(s1) <> 0 And Len(s2) <> 0 Then
        If s1 <> s2 Then
            strMsg = strMsg & vbCrLf & "Test 3: Family"
        End If
    End If
    
    If Len(strMsg) <> 0 Then
        strMsg = Mid$(strMsg, 3)
    End If
    
    fnTest = strMsg
    
End Function

to call it it your Query:

SELECT *, fnTest([ID]) As Notes FROM YourTable;"

 

[John Sh]

you can create a User-Defined Function on Module1 and call it on your Query.

code]
SELECT *, fnTest([ID]) As Notes FROM YourTable;"
[/code]

Once again I thank you for your response.
I had the statements all there, just didn't think to put them in a function and call that from a query.
John