form code 1

[hu5]

I created a form based on a table, the TASK text box on the form is to enter text string. When user presses refresh records, the text typed in the TASK text box should be transferred into the first blank field (starting from field 13 )on that record in the table.

The program looks for the blank field starting from field 13, if 13 is blank, then the text will be inserted into Field13, if Field13 is not blank, it should check Field14, and so on. The total fields can be as long as Field 40.
The program below doesn’t work. Your help will be greatly appreciated.


Private Sub Text18_AfterUpdate()
Dim i As Integer
Dim fieldArray(1 To 40) As Field

For i = 13 To 40
If fieldArray(i).Value = "" Then
fieldArray(i).Value = Text18.Text
Exit For
End If
Next

End Sub

 

[RoyVidar]

To me this sounds like a very un-normalized structure, and that you should perhaps take some time looking at that.

That said, if your form controls are named "field13", "field14"..., something like this might be used:

[tt]For i = 13 To 40
If isnull(Me("field" & i).Value) Then
Me("field" & i).Value = Me!Text18.Value
Exit For
End If
next i[/tt]

Roy-Vidar

 

[PHV]

Try something like this:
Private Sub Text18_AfterUpdate()
Dim i As Integer
With Me.Recordset
.Edit
For i = 13 To 40
If .Fields(i).Value = "" Then
.Fields(i).Value = Text18.Text
Exit For
End If
Next
.Update
End With
End Sub


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[hu5]

Thank you all for your help.

I wish to add time/date stamp to TASK text box, can it be set up automatically?

When user type " .... completed." in the TASK text box and refresh record, that data get loaded into the record in the table, will it be possible for that completed record to transfer to another archived table? Thanks