enable or disable a text box after checking if its null or not

[hotmalepiyush]

here's what i wish to do:
i have fields named status1,status2,...,status5 abd caller1... caller5
(i know this kind of field names are a lil absrd but i wanna keep them like this cuz the end users want them to be so)

when i add record i fill status1 and caller1. thats fine

i need to build another form.. update_status: which will show me all the status and caller fields but only one status textbox will be allowed to be filled up by the user...

for example.. if only status 1 and status 2 has been filled uptill now.. and then the form should allow only status 3 to be filled...

here's how i have thought to do this.. but dunno the vba coding

on open all the status textbox will be disabled.. the code will check which textbox is empty starting from the 1st.. and the one it finds empty is enabled.. the caller textbox will be disabled and shud be filled by currentuser() function.

then on click of a button say update... the form closes and the text written by user will be filled to appropiate field

i am new to vba.. have done little excel vba.. thats it.. please help me out on thsi one..

thanx

piyush

 

[Tanner65]

First off, I'm more of an Excel guy, but I've spent some time recently working on forms in Access. From what I can tell in order to test the value, the text box has to have focus which means the text box has to be enabled.

My first tip is to not open the form with everything disabled but disable it with a form load event.
This would allow you to decide at that point what is null and not without having to enable/setfocus/disable the object.

So on form_load you could do something like the following:

    Dim obj As FullFormName
    With obj.status1
        .SetFocus
        If .Text = "" Then .Enabled = False: obj.status2.Enabled = True
    End With
    With obj.status2
        .SetFocus
        If .Text = "" Then .Enabled = False: obj.status3.Enabled = True
    End With

FullFormName is the Class Object name for the form; from what I can tell it's usually Form_(The name you've given it).

Or you could loop through all the objects in your form and do something like this:

Public Function CheckTextBoxesForValue(ByVal frm As Object) As Boolean
    On Error GoTo ErrorOut
    Dim ctrl As Control
    For Each ctrl In frm.Controls
        If ctrl.ControlType = acTextBox Then
            ctrl.SetFocus
            Select Case ctrl.Name
                Case status1.Name
                    If ctrl.Text = "" Then
                        ctrl.Enabled = False
                        status2.Enabled = True
                    End If
                Case status2.Name
                    If ctrl.Text = "" Then
                        ctrl.Enabled = False
                        status3.Enabled = True
                    End If
                Case etc
            End Select
        End If
    Next
    
CommonExit:
    CheckTextBoxesForValue = True
    Exit Function
ErrorOut:
    MsgBox Err.Number & vbCrLf & vbCrLf & Err.Description & vbCrLf & vbCrLf _
        & "Occured in CheckTextBoxesForValue", vbCritical + vbOKOnly, "Error"
    CheckTextBoxesForValue = False
    Exit Function
End Function

But ya gotta send the form name in order for it to be referenced.

May be kind of redundant, but there are two ways you can do it, hope that helps!

 

[CyberLynx]

Here is another way....

Place this Sub procedure code into the Forms code module:

Private Sub CheckFilled()
   Dim Indx As Integer, Live As Integer
   Indx = 1

   While (Indx <= 5)
      If IsNull(Me.Controls("Status" & Indx)) = True And Live = 0 Then
         Me.Controls("Status" & Indx).Enabled = True
         Me.Controls("Caller" & Indx).Enabled = True
         Live = 1
      Else
         Me.Controls("Status" & Indx).Enabled = False
         Me.Controls("Caller" & Indx).Enabled = False
      End If
      Indx = Indx + 1
   Wend
End Sub

and then call the procedure from the Forms OnCurrent event OnCurrent event, like this:

Private Sub Form_Current()
Call CheckFilled
End Sub

.

 

[hotmalepiyush]

thanx a lot both of you..

 

[hotmalepiyush]

Here is another way....

Place this Sub procedure code into the Forms code module:

Private Sub CheckFilled()
   Dim Indx As Integer, Live As Integer
   Indx = 1
 
   While (Indx <= 5)
      If IsNull(Me.Controls("Status" & Indx)) = True And Live = 0 Then
         Me.Controls("Status" & Indx).Enabled = True
         Me.Controls("Caller" & Indx).Enabled = True
         Live = 1
      Else
         Me.Controls("Status" & Indx).Enabled = False
         Me.Controls("Caller" & Indx).Enabled = False
      End If
      Indx = Indx + 1
   Wend
End Sub

and then call the procedure from the Forms OnCurrent event OnCurrent event, like this:

Private Sub Form_Current()
Call CheckFilled
End Sub

.

i am using this code as of now..to update status of each person

i want to use vba.. and get the last status of all records in the sense.. suppose for person1 i have status1,status2 and status3.. but rest are null.. text in status3 must be shown as latest status for person1.. i knw that above code with some atering shud work.. i tried but i did not get the result.. i think cuz of lack of syntax knowledge.. can some one help..

P.S: i want to run this thruogh a form.. and as of now i have no field named last status or so...