Combo BOx Filter Issues - Can get passed it

[ahuvas]

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 =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. I tried to attach a copy of the database but cant get it below the max.

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?

 

[Uncle Gizmo]

>>>>cant get it below the max<<<<

Make sure compact on close is selected and then add your database to a Zip file.

 

[ahuvas]

Thanks Uncle.

Okay I do not have the database with me since its night in Aus but I really need an answer so I tried to replicate it as best I could without all the other questions etc.

Please find attached.

 

[alelover]

Re: Combo Box Filter Issues - Can get passed it

I too am having the same issue. I'm pretty new to Access so I'm an idiot when it comes to database stuff. I have a combo box with serial numbers for machines. When I select a serial number from the drop down I want it to show the data from that machine in the subform. It always wants to debug on the line "Form_MatrixCustomerInfo_sub.RecordSource = LSQL". I copied the code from a form I know works. I replaced all the names with mine. Here is the code. I triple checked my form,table and field names. I have also attached the database as well. What have I done wrong? Thanks.

Sub SetFilter()

Dim LSQL As String

LSQL = "select * from MatrixCustomerInfo"
LSQL = LSQL & " where MatrixSerialNumber = '" & MatrixSerialNumbers & "'"

Form_MatrixCustomerInfo_sub.RecordSource = LSQL

End Sub

Private Sub MatrixSerialNumbers_AfterUpdate()

'Call subroutine to set filter based on selected MatrixSerialNumber
SetFilter

End Sub

Private Sub cmdClose_Click()

'Close form
DoCmd.Close

End Sub

Private Sub Form_Open(Cancel As Integer)

'Call subroutine to set filter based on selected MatrixSerialNumber
SetFilter

End Sub