List all data on subform

[Proposal Doctor]

I am trying to get one of Albert's functions to work. Albert's code was
written for Access 2003. I am sure that my problem is with the periods and
bangs.

The function needs to display all people's names in the field
"PersonnelName" on the main form that appear on the subform
"subfrmProjectPersonnel" using Access 2007. I have an unbound textbox
(=ShowPersonnel) on the main form that is supposed to retrieve the
information.

What is wrong? I get the message Error#.



The function . . .

Public Function ShowPersonnel() As String
Dim rst As DAO.Recordset
Dim strText As String

Set rst = Me!subfrmProjectPersonnel.Form.RecordsetClone

If rst.RecordCount > 0 Then
rst.MoveFirst
Do While rst.EOF = False

If strText <> "" Then
strText = strText & ","
End If

strText = strText & rst!PersonnelName

rst.MoveNext
Loop
End If

Set rst = Nothing

ShowPersonnel = strText

End Function



Thanks.

David

 

[Dirk Goldgar]

I am trying to get one of Albert's functions to work. Albert's code was
written for Access 2003. I am sure that my problem is with the periods
and
bangs.

The function needs to display all people's names in the field
"PersonnelName" on the main form that appear on the subform
"subfrmProjectPersonnel" using Access 2007. I have an unbound textbox
(=ShowPersonnel) on the main form that is supposed to retrieve the
information.

What is wrong? I get the message Error#.



The function . . .

Public Function ShowPersonnel() As String
Dim rst As DAO.Recordset
Dim strText As String

Set rst = Me!subfrmProjectPersonnel.Form.RecordsetClone

If rst.RecordCount > 0 Then
rst.MoveFirst
Do While rst.EOF = False

If strText <> "" Then
strText = strText & ","
End If

strText = strText & rst!PersonnelName

rst.MoveNext
Loop
End If

Set rst = Nothing

ShowPersonnel = strText

End Function



Thanks.

David

I don't see anything obviously wrong with the code, so I don't think your
problem is with the dots and bangs. Why not set a breakpoint at the start
of the function and step through the code to see where the error is coming
from?

Here are some things to check:

1. On the line:

Set rst = Me!subfrmProjectPersonnel.Form.RecordsetClone

.... make sure that "subfrmProjectPersonnel" is the name of the subform
*control* on the main form, which may or may not be the same as the form
object that control displays.

2. Make sure the calculated textbox on the main form doesn't have the same
name as any field in the main form's recordsource.

3. Make sure that "PersonnelName" is actually the name of a field in the
subform's recordsource.

 

[Dirk Goldgar]

David:

Why not cut out the middleman and get the data directly from the tables
with
a function like this:

But if the subform is filtered so that it doesn't show all related records,
and you want the delimited list to include only the records currently
displayed on the subform, then you'd need to modify your code to include the
subform's filter to the SQL statement. Admittedly, that doesn't sound very
likely in this particular case, but it could be a consideration.

Further, why bother opening a new recordset and looping through it when
you've already got one, already opened and waiting for you, in the subform?
I'm usually all for querying tables directly rather than manipulating UI
objects in code, but in this case it seems to me that using the subform's
RecordsetClone is the simplest and most efficient way to do it.

 

[Hans Up]

The function . . .

Public Function ShowPersonnel() As String
Dim rst As DAO.Recordset
Dim strText As String

Set rst = Me!subfrmProjectPersonnel.Form.RecordsetClone

Where did you enter that code? If you put it in a standard module
instead of your form's module, I don't think Access will know what Me
refers to.