Records Incomplete status

[Narender Sagar]

Hi All
I have created a table in which multiple users have to maintain some data (one after other user). So I can't have option of having compulsory fields (Required property- Yes), because all the fields will not be maintained at once. However, I want to show Record status as incomplete (logically or by any means) until all the records are maintained.
Any advise..?
thanks

[ben1988]

i guess;

u can do on load the form vba which check

if isnull(field1) & isnull(field1) then
textbox.value="this form is incomplete"
end if

or

textbox.value= ((isnull(field1) + isnull(field1))/2)*100 & "% of the form complete"

[Narender Sagar]

Thanks Ben
I'm looking for for similar kind of solution. However, when I tried first option, I'm getting Run Time error '13' Type Mismatch
Please help..
thanks

[zmbd]

if ((rs![Field1] + rs![Field2] + rs![Field2] & "")="") Then
'(...)
END IF

This way if the value is null or an empty string then the returned is an empty string which is tested against.
If the fields are numeric then you might also test against the zero value. I personally will set a default of -2,147,483,648 for numeric(long) fields as it is the most negative value for numeric(long) fields and test against that value.

Simularly for the control data source:

=IIf(([Field1] + [Field2] & "")="","Fail","Pass")

See if this article will help:
Bytes > Sitemap > Microsoft Access / VBA Insights>What is Null?

[Narender Sagar]

Thank ZMBD
I'm able to understand your statement. But not able to get the final result I want. I tried control data source method, but its only showing "pass". Although various fields are null.

[zmbd]

opps,
Sorry, the expression should have had "+" for all but the last empty string:

=IIf(([Field1] + [Field2] & "")="","Fail","Pass")

[jforbes]

I know I'm jumping in here a little late. What ZMBD has going on there is great and a good solution, and for a lot of cases probably the best solution. But when I run into something like this, I sometimes like to consider building a a function to validate a Status as it tends to be more versatile.

Usually the function takes in the RecordID, looks it up and determines the Status, then Returns the Status. The following is a generic version of it, it probably has some errors as I copied and pasted the bulk of it:

Public Function validateThing(ByRef sThingID As String) As String
On Error GoTo ErrorOut
 
    Dim sSQL As String
    Dim oRst As DAO.Recordset    
 
    sSQL = ""
    sSQL = sSQL & "SELECT "
    sSQL = sSQL & "  Status "
    sSQL = sSQL & ", Quantity "
    sSQL = sSQL & ", Description "
    sSQL = sSQL & "FROM Things "
    sSQL = sSQL & "WHERE ThingID='" & sThingID & "' "
 
    Set oRst = CurrentDB.OpenRecordset(sSQL)
    If oRst.RecordCount > 0 Then
 
        ' Status
        Select Case Nz(oRst!Status, "C")
             Case "C"
                validateThing = "Closed"
                GoTo ExitOut
             Case "H"
                validateThing = "Hold"
                GoTo ExitOut
        End Select
 
        ' Quantity
        If Nz(oRst!Quantity, 0) <= 0 Then 
            validateThing = "Quantity Missing"
            GoTo ExitOut
        End If
 
        ' Description
        If Len(Nz(oRst!Description, "")) = 0 Then 
            validateThing = "Description Missing"
            GoTo ExitOut
        End If
 
        validateThing = "Ready"
    Else
        validateThing = sThingID & " was Not Found"
    End If
    oRst.Close
 
 
ExitOut:
    Exit Function
 
ErrorOut:
    Call MsgBox(Err.Description, vbCritical + vbOKOnly, gLongAppName)
    Resume ExitOut
 
End Function

Once you have a function like this, you can set the ControlSource of a TextBox to something like:

=validateThing([ThingID])

Then if you decide to build a Form Validator:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Cancel = (validateThing(Me!ThingID) <> "Ready")
End Sub

[Narender Sagar]

Hi ZMBD,
When I used following fields :

=IIf(([VariantCode]+[FGCode]+[ProductName]+[PaymentTerms]+[ProductRegistration]+[DlvTerms] & "")="","Fail","Pass")

the result is ok. But if I add any numeric field in the above formula :

=IIf(([VariantCode]+[FGCode]+[ProductName]+[BatchSize]+[QtyInNumber]+[QtyInPack]+[Price]+[PaymentTerms]+[ProductRegistration]+[DlvTerms] & "")="","Fail","Pass")

, I'm getting #Type!" error.

[Narender Sagar]

Hi Jforbs,
Thanks for your response. To be honest, I think code you have given is too much for me to understand or even take a rough trial.
Thanks again.

[zmbd]

If the fields are numeric then you might also test against the zero value.

Its quirk in Access, I'm guessig, as the string method I offered seems to break for numerics as I mentioned.

Here's a bit if a kludge work around for the numeric fields use the isnull() and for the text fields the string approach so we get something like:

=IIf(
      IsNUll([NF1]+[NF2]+[NF3])
         OR
     (([TxtF1] + [TxtF2] & "")="")
   ,"Fail"
   ,"Pass")

You might also need to have a refresh/requery in the after_update of the record against the flag.

[Narender Sagar]

Thanks zmbd.
This gave me desired result. I guess if its date fields, it will be treated as numeric fields!
regards,