Help Populating List box on a subform

[Pulse99]

Can someone help with an easy method for populating a list box on a subform based on the selection in a list box on a main form. So far I've setup the main form to display an SQL selection from several tables. A list box displays a couple of columns from which the user can select a individusl record (row), which drives the display of the complete record in columnar format on the same form. The Main form seems to work fine by itself.

Then I have a command button (on the main form) which opens a sub form (intended) to display a selection of related records from another set of tables based on the currently selected record on the main form. Ive used the following SQL to select the records for the subform:

SELECT [TeamMemberWeeklyStatus].[WeekEnding], [TeamMemberWeeklyStatus].[ProjectID], [TeamMemberWeeklyStatus].[Status], [TeamMemberWeeklyStatus].[WorkEffort], [TeamMemberWeeklyStatus].[DateActionTaken], [TeamMemberWeeklyStatus].[DatePosted], [TeamMemberWeeklyStatus].[ClassCode], [TeamMembers].[firstname], [TeamMembers].[lastname] FROM TeamMembers INNER JOIN TeamMemberWeeklyStatus ON [TeamMembers].[ContactID]=[TeamMemberWeeklyStatus].[ContactID] (((WHERE [TeamMemberWeeklyStatus].[ProjectID])=(form_frmProjects.projectID));

The SQL seems to work fine in the query QBE grid. However, I get a runtime error when I plug into the DAO openrecordset method in VBA code.

 

[PaulBricker]

Well, first, to use this you would probably have to set the Select statement into a string like this
Dim strSQL as String
strSQL = "SELECT [TeamMemberWeeklyStatus].[WeekEnding], [TeamMemberWeeklyStatus].[ProjectID], [TeamMemberWeeklyStatus].[Status], [TeamMemberWeeklyStatus].[WorkEffort], [TeamMemberWeeklyStatus].[DateActionTaken], [TeamMemberWeeklyStatus].[DatePosted], [TeamMemberWeeklyStatus].[ClassCode], [TeamMembers].[firstname], [TeamMembers].[lastname] FROM TeamMembers INNER JOIN TeamMemberWeeklyStatus ON [TeamMembers].[ContactID]=[TeamMemberWeeklyStatus].[ContactID] (((WHERE [TeamMemberWeeklyStatus].[ProjectID])= '" & (form_frmProjects.projectID))& "'"
Me.SubformName.Form.RecordSource = strSQL

The catch is going to be that the subform has to be bound to something that allows you to bind the controls on the subform to the fields in your SQL statement. Otherwise, your controls will all show #Name when you open the form. Then you can change the RecordSource on the fly.
Try it out and post back with specific problems.

Paul

 

[Pulse99]

Thanks Paul...It took quite a bit of work to get it to function, as I was still having problems with the Where clause. However, your suggestion helped a lot. Thanks.