VBA code to populate data sheet sbfrm based on combo box selection "ALL"

[brharrii]

I have an unbound combobox on a form that is used to filter the records of a subform based on who they have been assigned to. The combo box is based off of a query to the personnel table and utilizes a UNION querry to add the option "**ALL**" with a id number "111111" as the first option in the combo box.

In other words: the combo box has a list of names to choose from and the word "ALL" at the top as the first selection.

The idea is that when "**ALL**" is selected, the subform should display all records regardless of who they have been assigned to. I'm trying to accomplish this with VBA. I've started developing the code to try to impliment this, however currently when "**ALL**" is selected, I'm getting a datatype missmatch that seems to be caused by my use of recordset.

This is what I have so far:

Option Compare Database
Option Explicit
-------------------------------------

Private Sub cboTaskListName_AfterUpdate()
'On Error GoTo cboTaskListName_AfterUpdate_Err
 Me.Refresh
    
 Dim db As DAO.Database
 Dim SQL As String
 Dim rs As DAO.Recordset
 If Me.cboTaskListName = "111111" Then
 
 Set db = CurrentDb()


 SQL = "SELECT DiscrepancyID from tblDiscrepancy" & " where DiscrepancyTest = '53'"
 
Set rs = CurrentDb.OpenRecordset(SQL)
 Debug.Print SQL

 MsgBox "Must enter" & (SQL)
 
   
 
  End If
    
End Sub

Could someone let me know if you can tell what I'm doing wrong? Also once the queried records are saved into the record set, how can I communicate that to the subform so that it displays them?

Thank you for your help,
Bruce

 

[TJPoorman]

Re: VBA code to populate data sheet sbfrm based on combo box selection "ALL"

Rather than trying to use a recordset, why don't you just filter the subform? Something like me.subformname.form.filter = "[FieldName] = 'Test Value'"
me.subformname.form.filteron = true

 

[Cronk]

Re: VBA code to populate data sheet sbfrm based on combo box selection "ALL"

If the field DiscrepancyTest is numeric, then the inclusion of single quotes eg '53' would generate an error.

Incidentally, the OP would make a lot easier by advising the line on which the error is occuring.