View subform in main forms datasheet

[cbd]

I have a form based on a table called "List of Materials". Within this form I have a subform(really its a list box) with only one column in it called "Risk Phrase Number". I would like it so that when i view the form as a datasheet I would be able to see the subform field "Risk Phrase Number". What I am think about is joining all the records in the subform into a string(Risk Phrase Number is of the form "RH 33" or "RH 46" etc.) so that it looks like "RH 33, RH 46" in a column in the datasheet. Unfortunately i have no idea of how to do this or if it is even posssible.

Can anyone provide a solution or any other sugggestions of how how do this?

 

[FoxProProgrammer]

Hi,

I just want to make sure that I understand what you want to do before I suggest code to do it.

Do you want to concatenate a field from every record in a table into a single string? I don't understand why you would want to do this, but I can suggest code to do it if that's really what you want to do. How many records are there? dz
dzaccess@yahoo.com

 

[cbd]

Basically yes. I know it seems like an unusual thing to do but there would be only about 2-3 records in the subform for each record in the main form.

Thanks

 

[FoxProProgrammer]

Ok, here you go. This code will concatenate the "Risk Phrase Number" field for all records in the table. This code assumes that want to concatenate duplicate values in the field if they exist. If you want to delete duplicate fields, let me know and I'll post a modification.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strConcat As String

Set db = CurrentDb
Set rs = db.OpenDynaset("List of Materials", dbReadOnly)

While Not rs.EOF
strConcat = strConcat & ", " & rs.[Risk Phrase Number]
rs.MoveNext
Wend

' Strip off the beginning comma and space
strConcat = Right(strConcat, Len(strConcat) - 2)

rs.Close
db.Close dz
dzaccess@yahoo.com

 

[cbd]

Thanks for that. I'll try it out tomorrow and let you know how I get on.

 

[cbd]

i have run into a problem. I believe you code is meant for a different version of MS Access. I have Access 97 and the compiler complains about the line:

rs.[Risk Phrase Number]

It gives the error "Expected: identifier or bracketed expression"

Any ideas of how to over come this error.

 

[FoxProProgrammer]

Oops, that was a typo! It should be:

rs![Risk Phrase Number]

The code is designed to work on Access 97 or Access 2000. That's the purpose of explicitly defining the database and recordset as DAO. dz
dzaccess@yahoo.com