Filtering sub-form based on combobox in main form issues

[huv123]

Okay I created a database that I am going to use to collect data about a study we are doing.

When a participant is added to the database on the main (Participation Details form), it automatically generates an ID and creates a record in a second table/form - a questionnaire (i.e. Athens Questionnaire) that the participants fill out preserving the ID number that was created in the participation details form (I have been able to achieve this).

However what I wanted to do next is be able to filter the Athens questionnaire using a combo box which contained all the participant's ID's.

I was able to create the combo-box and populate it with the ID's using a query and did not put a control source on it (i used SELECT tblAthens.ParticipantID ....) iand created a sub-form that contained the questionnaire. I did this by creating the questionnaire in a separate form called frmAthens_sub and dragging it from the "explorer window" into my open Athens Questionnaire form. However when I attempted to place code in the "After Update" field of the combo box I keep getting errors and I am not sure why.

The code I am using is:

Option Compare Database

Sub SetFilter()

Dim LSQL As String

LSQL = "select * from tblAthens"
LSQL = LSQL & " where ParticipantID = '" & cboSelected & "'"

Form_frmAthens_sub.RecordSource = LSQL

End Sub

Private Sub cboSelected_AfterUpdate()

'Call subroutine to set filter based on selected ParticipantID
SetFilter

End Sub

Private Sub Form_Open(Cancel As Integer)

'Call subroutine to set filter based on selected ParticipantID
SetFilter

End Sub

It is highly possible that I am just not doing something right as this is the first time I have used sub-forms.

The error seems to be coming from this line --> Form_frmAthens_sub.RecordSource = LSQL


Is there an easier way or alternative code for filtering a sub-form from a combo-box?

 

[jedel]

Why don't you just use the combo box wizard. It will set up the combo box for you and from what I can understand do just what you need it to do.

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[huv123]

Thanks. In the end I did this because I couldnt get a response . Rathen than having a sub-form I just made it one big form and created a combo box at the top with the wizard.

Will this combo box update automatically or do I need to put in a requery event?