consolidating code

[galorin]

I have several text boxes that get updated with information pulled from a query and stuck into an array. It's long, code that I want to shorten. I tried putting each text field into an array, an array but got type mismatch and other errors. There are 2 blocks of code I want to shorten, the first is

FromRecordset.MoveFirst
If Not FromRecordset.EOF Then
Do
With FromRecordset

If IsNull(!Profession) Then
FData(1) = " "
Else
FData(1) = ![Profession]
End If

If IsNull(![Direct Dial]) Then
FData(2) = " "
Else
FData(2) = ![Direct Dial]
End If

If IsNull(![Mobile]) Then
FData(3) = " "
Else
FData(3) = ![Mobile]
End If

(repeated 11 times)

The second block of code is:

Prf.SetFocus
Forms!People.Prf.Text = FData(1)

DD.SetFocus
Forms!People.DD.Text = FData(2)

Mobile.SetFocus
Forms!People.Mobile.Text = FData(3)

(Also repeated 11 times)

I have a need to do this in many more places, so if I can get a solution here, it'll reduce my frustration levels and help me beat a deadline. Many thanks

 

[jwrocko]

If you need to run the exact code the same way each time, you can create a public function inside a module.

public function func1(byval something as integer)
your code
end function

implemented on each form with

call function(something)

 

[jwrocko]

sorry - there is a typo on the implementation code

should be

call func1(something)

 

[TheAceMan1]

How are ya galorin . . .

A starting point:

[blue]   FData(1) = Nz(![Profession]," ")[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[JoeAtWork]

Is there a reason to first copy to an array before using the data? I.e., could you do this:

Forms!People.Prf.Text = Nz(FromRecordset("Profession"),"")

 

[galorin]

That's looking better, but it still leaves me with 11 lines of repetitive code. On another form, I will be doing the same thing but with more than 30 fields. There must be a way that is easier to maintain and easier to re-use.

I was thinking something like

for X = 1 to 11
FieldOnFOrm(X) = Nz(FromRecordset(Value(X),"")
Next X

or something similar that will actually work. It may be more code, but it should give me a block of re-useable code. An I trying to do too much here?

 

[JerryKlmns]

You could do that if your text boxes are an array of a textbox and reffer to the fields of the recordset with their ordinal position.

Dim iCounter As Integer
Dim iStop As Integer
'Here you get the number of recordset fields
iStop = FromRecordset.Fields.Count-1
For iCounter=0 to iStop
  txt(iCounter)= Nz(FromRecordset(iCounter)," ")
Next iCounter

You have to be sure that the ordinal position of the fields never changes.