Access Form Open query On Click (Macro)Button

[Mac1206]

I have a Form with a On Click macro which runs this query below and opens in data sheet view but I need it to be able to open several BGroup.bg_Type's. Is there a way to do this without having to create another Button for Each type listed "A", "B", "C", "D", "E"??? Any suggestion would be greatly appreciated...


SELECT BGroup.ag_ID, BGroup.ag_Type, BData.Bill_To, BData.Payable_To, BData.Payee, BData.PayeeID, BData.TPA_Code, BData.TPA_Name, BData.Plan_Number, BData.Plan_Name, BData.Platform, BData.Rep, BData.Participant_Name, BData.Participant_Acct, BData.Trust_Account_Number, BData.Custodian_Name, BData.TradingLink, BData.FeeType, BData.Descript, BData.BaseAmount, BData.Units, BData.Rate, BData.Amount, BData.LiquidatePlanAssets, BData.Trd_Amt, BData.Settle_Amt, BData.WO_Amt, BData.Move_Amt, BData.InvAmount, BData.Move_Dt, BData.Inv_ExportDt, BData.GP_InvNbr, BData.[3Ppaid_Amt]

FROM BData INNER JOIN BGroup ON BData.BGroupCode = BGroup.bg_ID

WHERE (((BGroup.bg_ID)=[Forms]![Pymt_RPymts]![Pymts_BillGrp]) AND ((BGroup.bg_Type)="A"));

 

[welshgasman]

Use the IN() clause?

AND ((BGroup.bg_Type) IN ("A",'B','C','D','E')));

You might want tp order by BGroup.bg_Type so you keep the same type together?

If they are not ging to be the same each time, say they are selected from a list box, then build the sql from those selected.

HTH

 

[Mac1206]

Hi Welshgasman,

I tried your solution but it didn't yield any data...

 

[welshgasman]

Really?
I use that exact method to obtain records for a report.?
I select multiple entries in a multi select listbox then use the IDs selected in an IN clause ?
Admittedly the query for the report has all the data, but I use the WHERE clause to only bring in what I need for the report?

Private Sub cmdReport_Click()
On Error GoTo cmdReport_Click_Err

Dim ctl As Control
Dim varItem As Variant
Dim strParam As String, strRptQuery As String

Set ctl = Me!lstCrew
' Now select what records from listbox
If ctl.ItemsSelected.Count > 0 Then
    For Each varItem In ctl.ItemsSelected
        strParam = strParam & ctl.ItemData(varItem) & ","
        'Debug.Print ctl.Column(2, varItem) 'Use to get any column in row in format column,row
    Next varItem
  Else
    MsgBox ("At least one name required")
    Exit Sub
End If

' Need to pass the criteria as a string
strParam = " IN (" & Left(strParam, Len(strParam) - 1) & ")"
Debug.Print strParam
strParam = "[QryServed].[Crew]" & strParam
DoCmd.OpenReport "rptServedWith", acViewReport, , strParam


cmdReport_Click_Exit:
    Set ctl = Nothing
    Exit Sub

cmdReport_Click_Err:
    MsgBox Error$
    Resume cmdReport_Click_Exit

End Sub

 

[Mac1206]

I got it now Welshgasman, I'm still learning the VBA side as well...Still a novice in VBA...Thanks for your help...Awesome

 

[welshgasman]

I got it now Welshgasman, I'm still learning the VBA side as well...Still a novice in VBA...Thanks for your help...Awesome

Great Good luck with the rest of it.