Better Coding

[momon]

Hello,

I have the following code and I was wondering if it could be reduced

Private Sub Form_Load()
On Error GoTo form_load_err

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("select * from [core surplus]&quot

prefix0 = rs("drawing # prefix&quot
rs.MoveNext
prefix1 = rs("drawing # prefix&quot
rs.MoveNext
prefix2 = rs("drawing # prefix&quot
rs.MoveNext
prefix3 = rs("drawing # prefix&quot
rs.MoveNext
...
rs.close

The code opens a table and fills in the values to a form. The names of the fields in the form are prefix1, prefix2, ...

As you can see, the code is pretty repetitive so I was wondering if there is a way to shorten it.

Thanks

 

[jebry]

Hi!

Try this:

Dim intCounter As Integer
Dim strControl As String
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("select * from [core surplus]&quot

If rs.EOF = True And rs.BOF = True Then
Call MsgBox("No Records Returned&quot
Else
rs.MoveFirst
For intCounter = 0 To YourMaxNumber
strControl = "prefix" & Format(intCounter)
Me.Controls(strControl) = rs("drawing # prefix&quot
rs.MoveNext
If rs.EOF = True Then
Exit For
End If
Next intCounter
End If

Set rs = Nothing

This will work if you have a certain number of text boxes to fill. If you want the number of records returned to control the loop, then do this:

Dim intCounter As Integer
Dim strControl As String
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("select * from [core surplus]&quot

If rs.EOF = True And rs.BOF = True Then
Call MsgBox("No records returned&quot
Else
rs.MoveFirst
intCounter = 0
Do Until rs.EOF = True
strControl = "prefix" & Format(intCounter)
Me.Controls(strControl) = rs("drawing # prefix&quot
intCounter = intCounter + 1
rs.MoveNext
Next intCounter
End If

Set rs = Nothing

hth


Jeff Bridgham
bridgham@purdue.edu