Filtering a query

[downhilljon]

OK, new problem to solve. I don't know if I am going about this the right way, so please feel free to make suggestions on better ways to acheive an outcome!

I have a query "qryBuildsPrinted" which searches a specific table for appropriate records, feeds these selected records into a report "rptBuildSlips" and prints them.

I am now trying to allow the user to print these build slips selectively, instead of printing out the whole lot pulled in by the query every time. My approach is to use the existing query, but filter the query based upon some selections made by the user in one of my forms. I am having trouble getting the query to filter successfully.

I realise that you can filter both the query and the report independently, so I probably only need to filter the report, but that is not working for me either!

Here is my code:

Private Sub cmdPrint_Build_Slip_Click()
Dim intBikeID As Integer, intBuildID As Integer
Dim varItm As Variant
Dim ctl As Control
Dim intCounter As Integer
Dim intRecords As Integer
Dim varArray() As Long
Dim strFilter As String
Dim blnPrinted As Boolean
Dim msgMessage As Variant
   
'Print Build Slip for selected bike

'Set Control
Set ctl = Me.lstResults
intRecords = 0
intCounter = 0

'check if a selection has been made (kinda dodgy)
For Each varItm In ctl.ItemsSelected
    GoTo Selection_Made
Next

GoTo CleanUp

Selection_Made:
'Count how many records have been selected
For Each varItm In ctl.ItemsSelected
    intRecords = intRecords + 1
Next

'Clear Array and Re-allocate Upper Bound of array (Array starts at 0)
ReDim varArray(intRecords - 1)

For Each varItm In ctl.ItemsSelected
    
    'determine BuildID of selected record
    intBikeID = ctl.ItemData(varItm)
    intBuildID = DLookup("[BuildID]", "tblBuilds", "[BikeID] = " & intBikeID)
    blnPrinted = DLookup("[PrintedSlip]", "tblBuilds", "[BikeID] = " & intBikeID)
    If (blnPrinted = True) Then
        msgMessage = MsgBox("One of the bikes selected has already had a Build Slip printed. Please adjust your selection", vbOKOnly, "Build Slip Already Printed")
        GoTo CleanUp
    End If
    
    'Fill array with values, given how many values are present
    varArray(intCounter) = intBuildID
    
    'Adjust counter to go to next array value
    intCounter = intCounter + 1
    
Next
    
'Select Filter String
Select Case intRecords
    Case 1
        strFilter = "[BuildID] = " & varArray(0)
    Case 2
        strFilter = "[BuildID] = " & varArray(0) & " Or " & varArray(1)
End Select


'Open query which displays the bike selected
DoCmd.OpenQuery ("qryBuildsPrinted")
'DoCmd.ApplyFilter , strFilter

'Print report of query with filter applied
DoCmd.OpenReport "rptBuildSlips", , , strFilter

'Close query, remove filter
DoCmd.SetWarnings False
DoCmd.Close acQuery, "qryBuildsPrinted"
DoCmd.SetWarnings True
    
ctl.Requery

GoTo CleanUp

CleanUp:
intBikeID = Empty
intBuildID = Empty
Set varItm = Nothing
ctl.Value = Empty
Set ctl = Nothing
intCounter = Empty
intRecords = Empty
strFilter = Empty
blnPrinted = False

    
End Sub

A further question that is probably on the same topic is that once this has been completed, I also need to modify my existing Update Query which makes a modification to the data in the table. Is it possible to apply a filter to an Update query also?

I realise this may not be the most effective use of Access, so as I said, please feel free to suggest alternate methods I can research.

Regards,
Jon

 

[pono1]

Jon,

Consider filtering the report using the code below as a model for your own solution...

'Run a report displaying only the records chosen
'by the user in the form's listbox.

    Dim v As Variant
    Dim frm As Form
    Dim ctl As Control
    Dim theId As Long
    Dim filterdata As String

'If nothing is selected, notify user...
   If Me.ListBoxName.ItemsSelected.Count = 0 Then
      MsgBox "Please select a supplier or two.", vbExclamation, "No Filter Data Selected"
   'and then scram.
      Exit Sub
   End If

'Begin building filter.   
    Set Frm = Forms!FrmSampleList
    Set ctl = Frm!LstFindings
    filterdata = "SupplierID = "

'Get the filter items.
    For Each v In ctl.ItemsSelected
    'Coordinates: 1st column (0); row v
    'where v changes for each round of the loop.
         theId = ctl.Column(0, v)
    'Add it.
         filterdata = filterdata & theId & " OR SupplierID = "
    Next v

'Clean-up the filter by removing the trailing text.
'NOTE:  " OR SupplierID = " is 17 characters.
    filterdata = Left(filterdata, Len(filterdata) - 17)
    
'Test (see Immediate Window).
   'Debug.Print filterdata

' Launch the report.
    DoCmd.OpenReport "RptSuppliers", acViewPreview, , filterdata

End Sub

Regards,
Tim

 

[downhilljon]

Thanks heaps for the reply Tim. This looks like it will suit my situation exactly. I'll get cracking and let you know how it goes!

Cheers,
Jon

 

[downhilljon]

Yep, worked a treat. And much more efficient than my code too!

Thanks again.