show only the first Entry from Field1 but all from Field2

[batwings]

Hi there I hope one of you guys can help me!

I have a Table or Query and it contains data as below


FIELD_1 | FIELD_2
AAAA____|1111
AAAA____|2222
AAAA____|3333
BBBB____| 5555
BBBB____| 9999

How in a form can I display the data as below not using cascading combi boxes but using Text Boxes, the one for FIELD_1 will show only one entry per record but the Text Box for FIELD_2 will show up to 20 entries.

1st Record
AAAA____|1111
_________|2222
_________|3333

2nd Record
BBBB____|5555
________|9999


I hope you understand what I mean, I just do not want the data in FIELD_1 repeated for every entry in FIELD_2

Thanks

 

[WayneRyan]

bat,

Query
=====
Field1: [Field_1]
Field2: fnGetChild([Field_1])

Then make a public function:

Public Function fnGetChild(Field1 As String) As String
Dim rst As DAO.Recordset
Dim Temp As String
Temp = ""
Set rst = CurrentDb.OpenRecordset("Select * from YourTable Where Field_1 = '" & Field1 & "'")
While Not rst.EOF And Not rst.BOF
   Temp = Temp & rst!Field2 & vbCrLf
   rst.MoveNext
   Wend
fnGetChild = Temp
End Function

Wayne

 

[Premy]

A Q&D (quick and dirty) solution would be by implementing conditional formatting on field 1, setting the condition up like:
"The expression is [Field1] <> 1111". If the condition is met the font should get the same color as th background, thus becoming invisible. If the first record of field2 is not fixed or not always known beforehand, u should implement this conditional formatting thru code, rather than thru the Formatting menu.

A more decent way would be to use a mainform with a subform, the main form being in form mode and the subform in continuous. This has the added advantage of not displaying columns with lots of blanks.

HTH
Premy