Show linked records in continuous form view 1

[VBill57]

Hi everybody,

This is probably easy, but my brain isn't working yet. I have a list of people, and they can belong to one or more groups. I wanted a form that shows each of the people and their information (address, phone, etc) in a continuous form. I thought about using a subform that lists the groups they are in, linked by the PersonID value.

I guess I've never attempted to do this before, because it says you can't use a subform on a continuous form. Does anyone have any suggestions how I can list each person - and have the groups they are in shown in a list box along the right side of the form. I still want it to be a continuous form, not just one person per page.

I have a table with a PersonID field, and the other information (address, etc.) I also have a table with the GroupID. Then I have a third table with both the PersonID and GroupID values in it. I just need a way to have a form such as this:

John Smith Group 3
(address, phone, etc.) Group 7

Fred Jones Group 2
(address, phone, etc.)

Mary Johnson Group 3
(address, phone, etc.) Group 4
Group 8

If someone could point me in the right direction, I would appreciate it. Thanks!

 

[lupins46]

As you realise, you are not going to be able to show the groups for each person using subforms. The only solution I can think of using continuous forms would be to develop a function which created a single field containing the groups for a given person. You could then include this function as a column in the query that the continuous form is based on. This should work OK but obviously you cannot modify the group membership in any way in the continuous form.
An alternative would be to put the continuous form as a subform onto a main form. Then add another subform to show the group membership for the currently selected record. Obviously you can only see the groups for one person at a time this way but it could give you an updateable list or at least a list where you can identify any record selected in it.

 

[VBill57]

lupins46,

Thanks for your response. I've been out all day, so couldn't reply sooner. I'll give the function suggestion a try. I just want a list of the names and groups, so being able to edit from that form is not an issue.

Thanks for your help.

 

[ZmrAbdulla]

Hi,
Microsoft has provided an example in their famous Northwind database.
Check out how "Customer Orders" form and the subforms are connected!
regards

Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[VBill57]

lupins46,

I tried your function in the query suggestion - and it worked! I now have a CONTINUOUS form that shows each person's information, with a large text box on the right side showing the groups associated with that person.

I made a query that pulls the person info (address, phone, etc.) along with the PersonID field. I then added an expression field to the query, defined as this -
GroupList: GetList([tblPeople].[PersonID])

The code for the GetList function is this:

  ' The Person argument is the PersonID value from the query's recordsource
  Public Function GetList(Person) As String
  Dim rs As DAO.Recordset
  Dim strList As String
  Set rs = CurrentDb.OpenRecordset("qryGroups", dbOpenSnapshot)
  With rs
    .MoveFirst
    Do While Not .EOF
      If !PersonID = Person Then
        strList = strList & !GroupName & vbCrLf
      End If
      .MoveNext
    Loop
  End With
  GetList = strList
  rs.Close
  Set rs = Nothing
  
End Function

The new GroupList field contains the list of groups associated with each person, including multiple lines if necessary. I can't believe it worked!!

ZmrAbdulla, I don't have the NW db right now. I'll put it back and check out your suggestion also. Thanks.

 

[lupins46]

VBill57

Great - happy to have given you a hint here.

You could speed up the function by using something like:

"Select * from qrygroups where personid = " & person

as the source for your recordset.
(Assumes personid is a number)
Ideally the code would be changed to reflect this but it's not going to be that significant.