vba code

[ptsm]

Hi,
I got a form with two text boxes
1- Received by
2- Issued to
One of these two text boxes is enabled at a time.
If the enabled box is empty I should get a message.
Please advise me the VBA code ( form before update event)
Thanks
PTSM

 

[arnelgp]

the Current Event of the Form is likely to use.
although at first you might be thinking of
BeforeUpdate Event. this event will not fire
if you did not change anything and just using
the arrow keys to go up/down a record.

Option Compare Database
Option Explicit

Dim strControlName As String

Private Sub Form_Current()
''''''''''''''''''''''''''
'*
'* arnelgp
'*
'* NOTE:
'*
'* to be able for this to work, you must add an Autonumber field
'* on the table and on this form.
'*
'* Replace 'ID' on the proceeding expression with the name of Autonumber field.
'*
''''''''''''''''''''''''''
    Const PKField As String = "ID"
    
    Static TheEnabledControl As String
    Static lngID As Long
    Static bolHandled As Boolean
    
    Dim lngCurrentID As Long
    
    lngCurrentID = Nz(Me.ID, 0)
    
    If TheEnabledControl <> "" Then
        With Me.RecordsetClone
            '* find the previous record
            .FindFirst PKField & "=" & lngID
            If Not .NoMatch Then
                '* check if the control we are validating
                '* has any value
                If .Fields(TheEnabledControl).Value & "" = "" Then
                    '* there is no value...
                    '* go back to this record
                    Me.Bookmark = .Bookmark
                    '* the validation msg will fire 2X
                    '* so we have a flag that it has been
                    '* handled, so it will not show the
                    '* messagebox again.
                    If Not bolHandled Then
                        MsgBox "Cannot go to next/previous record until '" & TheEnabledControl & "' is filled up"
                        bolHandled = True
                    Else
                        bolHandled = False
                    End If
                Else
                    '* there is value on the control
                    If lngCurrentID <> 0 Then
                        '* go to next/previous record
                        .FindFirst "ID=" & lngCurrentID
                        Me.Bookmark = .Bookmark
                        lngID = Nz(Me(PKField).Value, 0)
                    End If
                End If
            End If
        End With
    Else
        '* when the form's initial opening, it will go
        '* here
        lngID = Nz(Me(PKField).Value, 0)
        
        ''''''''''''''''''''''''''''''''''''''''''
        '* NOTE:
        '*
        '* Replace 'F3' with the name of the Enabled control (field)
        '* you want to validate. in this example i am using F3 field.
        '*
        ''''''''''''''''''''''''''''''''''''''''''
        TheEnabledControl = "F3"
    End If
End Sub

 

[isladogs]

Here's another approach to consider

private sub Form_Current()

If (me.[Received by].enabled =true And Nz(me.[received by],"")="") _
Or (me.[issued by].enabled=true And Nz(me.[issued by],"")="") Then _
MsgBox "Your message here"  

End Sub

NOTE you can make it shorter still by omitting both "=true".

 

[arnelgp]

mr.ridder your code will work but will not prevent from leaving the record with blank field.

 

[isladogs]

mr.ridder your code will work but will not prevent from leaving the record with blank field.

The OP didn't request that.

 

[arnelgp]

he did post 1 (before update of form).

 

[isladogs]

I just read that as the OP saying the code should in his view be in the form update event.