vba on focus/lost focus help (1 Viewer)

[miken5678]

I did the below and apologize for me not catching your wording as i figured it would work as a defined function.. this has no issues but does not catch the empty text box.. also when you use len it is to cont characters so why is there a & "" added?

this will work by itself

If (Len(Forms!WindOptOutV12.IndStatmentHandwrittenComment & "") = 0) Then

but not when i make it an and statement

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim blnError As Boolean
    Dim strError As String
 
    If (Forms!WindOptOutV12.IndStatmentHandwrittenComment = -1 And Len(Forms!WindOptOutV12.IndStatmentHandwrittenComment & "") = 0) Then
        blnError = True
        strError = "Other Deviations Comment" & vbCrLf
    End If
 
    'If Len(Me.AnotherControlNameHere & "") = 0 Then
    '    blnError = True
    '    strError = strError & "YourOtherControlNameHere" & vbCrLf
    'End If
 
    ' and so on for how ever many controls you want to validate that
    'something has been entered.  You can also validate other stuff about the values, etc.
 
    If blnError Then
        Cancel = True
        If MsgBox("You need to fill out these fields before we can save the record: " & vbCrLf & _
                  strError & vbCrLf & _
                  "Do you wish to cancel this record?", vbQuestion + vbYesNo, "Validation Failure") = vbYes Then
            Me.Undo
        End If
    End If
End Sub

 

[boblarson]

When checking for text, you can have nulls or empty strings. You can check for nulls like:

If IsNull(Me.YourTextBoxName) Then

or for an empty string:

If Me.YourTextBoxName = "" Then

But if you want to check for both, you can use

If Len(Me.YourTextBoxName & "") = 0 then

to convert a null, if it is one, to an empty string and then if the length is 0 ...etc.

But you can also use

If Nz(Me.YourTextboxName, "") = "" Then

(so maybe change and use that form of it)

 

[miken5678]

maybe this doesnt accept and statements or maybe I am doing this wrong

    If Forms!WindOptOutV12.IndStatmentHandwrittenComment = -1 And Nz(Forms!WindOptOutV12.IndStatmentHandwrittenComment, "") = 0 Then
        blnError = True
        strError = "Other Deviations Comment" & vbCrLf
    End If

still wont catch it upon going to next record.


Bout ready to give up.

 

[boblarson]

You have to change this:

Nz(Forms!WindOptOutV12.IndStatmentHandwrittenComment, "") = 0

to this:

Nz(Forms!WindOptOutV12.IndStatmentHandwrittenComment, "") = [COLOR=red][B]""[/B][/COLOR]

and if you still can't get it after this, upload what you have (with bogus data of course) and I'll see if I can't troubleshoot it.

 

[miken5678]

here you go, as the revision doesnt work.

Can I ask your opinion on .visible items. I have several items setup to change the view however I originally tried on the before events but issues popped up while scrolling through records. My final decision was to do before after and on the form. It doesnt seem like the correct route for me however it is what worked at the time. Is it better for me to write one huge long function with every if statement in it and reference that inside the button before/after reference or is it better to do small public functions and just reference those to each applicable button?

 

[boblarson]

It would probably help if you referenced the correct control and with the correct values (and get rid of the Forms!...etc. reference. When using code on the form you are referring to, use the keyword ME:

    If [B][COLOR=red]Me.ComboIndStmtHandWritten = "Yes"[/COLOR][/B] And Nz([B]Me.[/B]IndStatmentHandwrittenComment, "") = "" Then
        blnError = True
        strError = "Other Deviations Comment" & vbCrLf
    End If

Second, you can use a large function but there are limitations on how long it can be. Third, your database is not normalized and really should be re-designed at the table level first before moving on.

 

[miken5678]

the only reason I went with the forms reference is do to an issue when someone was selecting a record in the split form none of the vba visible items would work. When i did a direct reference to these, after finding the fix on here, things started to work.

I have always read about normalization but never tried it and usually stick with 1 to two tables. The main reason for this is way back when i designed my first database I tried to integrate more than one table/sources in the field source area and in 03 it would always crash.. i think its probbaly better to do a query for the source but never felt comfortable with it.

I am always open to suggestions/pointers on improving what little I know now.

It would probably help if you referenced the correct control and with the correct values (and get rid of the Forms!...etc. reference. When using code on the form you are referring to, use the keyword ME:

    If [B][COLOR=red]Me.ComboIndStmtHandWritten = "Yes"[/COLOR][/B] And Nz([B]Me.[/B]IndStatmentHandwrittenComment, "") = "" Then
        blnError = True
        strError = "Other Deviations Comment" & vbCrLf
    End If

Second, you can use a large function but there are limitations on how long it can be. Third, your database is not normalized and really should be re-designed at the table level first before moving on.