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

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!

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

.

thanx a lot both of you..

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...