Using a Select query on Access report

Question

I have two tables and a report, tbl1, tbl2 and rptAllOrders.
tbl1 and tbl2 are not identical.

The report is not bound to any recordsource and so is it's the controls.

Is this doable in VBA? Or is there a better alternative way?

Private Sub btnReport_Click()
DoCmd.OpenReport "rptAllOders" , , acViewPriview 
End Sub

Private Sub rptAllOders_Open(Cancel As integer)

Dim strq As string

strq = "SELECT tbl1.ItemNo, tbl1.ItemName, 
tbl2.OderDate, tbl2.OderNo FROM tbl1 UNION ALL ON 
tbl1.ItemNo = tbl2.ItemNo AND ItemName = 
'"Forms!comboItemName.Value"' ORDER BY oderDate 
ASC"

 End Sub

What exactly is your question? How to set the RecordSource dynamically? Yes, this can be done like this: `Me.RecordSource = strq`. But to make your code compile, you have to arrange the string preparing `strq` properly, but I guess you know that. — AHeyne, Oct 18 '19 at 06:20

AHeyne · Answer 1 · 2019-10-18T06:39:05.550

Following your sample the solution with correct string concatenation would be this. It embeds Forms!comboItemName.Value as a string.

Private Sub rptAllOders_Open(Cancel As integer)
    Dim strq As String
    strq = "SELECT tbl1.ItemNo, tbl1.ItemName, " & _
           "tbl2.OderDate, tbl2.OderNo FROM tbl1 UNION ALL ON " & _
           "tbl1.ItemNo = tbl2.ItemNo AND ItemName = '" & _ 
           Forms!comboItemName.Value & "' ORDER BY oderDate ASC"

    Me.RecordSource = strq
End Sub

You should check your query in the Query Designer with hard coded parameter to prove it, I'm not sure if the query itself (even without or hard coded parameter) works well.

And as always: Take a look on how to use parametrized queries, because using string concatenation for embedding parameters is a risk in manner of SQL injection!

See here: https://stackoverflow.com/a/49509616/7658533

Using a Select query on Access report

1 Answers1