Function FillControl() Problem

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

Can someone help out I found this code on one of the
boards that should have done what I need but I can not
seem to make it work did I do something wrong?

I have a table PTFile_Tbl only one field called
PtFile_Name the field is set to text

I have a Form with one unbound control I set the event to
click = FillControl()

In my Module I have the code for the Function
_________________________________
Public Function FillControl()

' Will fill an unbound control with a list of names from
' each record in one table field
' i.e. John Smith, Joe Jones, Henry Adams
' without a comma after the final name.

Dim db As Database
Dim rstTemp As Recordset
Dim F As Field
Dim FillString As String

Set db = CurrentDb
Set rstTemp = db.OpenRecordset("PTFile_Tbl")
Set F = rstTemp("PtFile_Name")

With rstTemp
Do While Not rstTemp.EOF
FillString = FillString & F & ", "
.MoveNext
Loop
End With
FillControl = Left(FillString, Len(FillString) - 1)

rstTemp.Close
Set db = Nothing

End Function


What I thought would happen is when I click on the control
on the form it would read the records in PTFile_Tbl and
fill all of the records into the unbound control on the
form.

When I try I do not get an error and it does not fill in
the form it just remains blank.
 
FillString = FillString & F & ", "
Click to expand...

should be a semicolon: see help on the Listbox control.

Oh, and I am really not sure if the value of F will get updated; I would
just use a reference directly to the recordset itself.

Oh, and you want to skip the line altogether if the field is null - an easy
way to do this is with the plain plus(+) operator:

FillString = FillString & (rstTemp!PtFile_Name + ";")

Oh, and you'll find it runs _much_ faster if you use a ForwardOnly
Snapshot, and it won't lock out your other users

Oh, and it'll go even quicker if you only ask for the one column rather
than the whole table:

strSQL = "SELECT DISTINCT PtFile_Name " & _
"FROM PtFile_Tbl " & _
"ORDER BY PtFile_Name"

Set rstTemp = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)



HTH


Tim F
 
Back
Top