Populate subform via listbox

[hawg1]

Hi,

I am having trouble trying to get a subform to show a record selected from listbox on the main form.

Using the list box, when I click on the desired info, I can get a seperate form (InstructorList) to open and show the complete record using the following code:

In the listbox rowsource:

SELECT [Instructor Info].Instructor_ID, [Instructor Info].Rank, [Instructor Info].Last_Name, [Instructor Info].First_Name, [Instructor Info].Middle_Initial FROM [Instructor Info];

in the On Double Click event:

Private Sub List0_DblClick(Cancel As Integer)

DoCmd.openform "InstructorList", , , "[Instructor Info].[Instructor_ID]=" & Me.List0.Column(0)

DoCmd.close acForm, "InstructorSearch"
End Sub

When I try to embed the form InstructorList as a subform (using the wizard) on the main form (InstructorView) , I can't get it to populate using the following code in the afterupdate event:

Option Compare Database
Sub SetFilter()

    Dim LSQL  As String
    
    LSQL = "select * from InstructorInfo"
    LSQL = LSQL & " where Instructor_ID = '" & List0 & "'"
    
    Form_InstructorList.RecordSource = LSQL
    
End Sub


Private Sub List0_AfterUpdate()
'Call subroutine to set filter based on selected last name
    SetFilter
End Sub

Private Sub Form_Open(Cancel As Integer)
    
    'Call subroutine to set filter based on selected last name
    SetFilter
    
End Sub

It gives me a visual basic error message "Run-Time error '424' Object required" and highlights the line Form_Instructorlist.RecordSource = LSQL when I debug.

I'm at a loss to figure it out. Any help would be appreciated.

Thanks

 

[boblarson]

Well for one, to refer to the subform recordsource from code not on the subform, you use

Forms!YourMainFormNameHere.YourSubFormContainerNameHere.Form.RecordSource

If the code is on the subform, then you can just use

Me.Recordsource

Also, in your SetFilter sub, you refer to List0 but you don't show which form it's on. So, if it is on the main form and the sub is on the main form then you could use Me.List0 or if the sub is on the subform then you would need to use the

Forms!YourMainFormNameHere.YourSubformContainerNameHere.Form.List0

And if the sub is in a module, then you would need a fully qualified form name as well.

Also, if InstructorID is numeric, you don't want the single quotes. And, in this:

DoCmd.openform "InstructorList", , , "[Instructor Info].[Instructor_ID]=" & Me.List0.Column(0)

You shouldn't need the table name nor the column number if it is using 0, unless the bound column is a different column.

 

[hawg1]

Boblarson,

Thanks for the help. I tried your suggestions and they still didn't work. So when all else fails....start over from scratch. With new forms/subforms everything is now working. I went with the linkchild/linkmaster method.

Again , thanks for the quick response and the helpful pointers.

Hawg1