0

I have a Subform which has a datasheet, a textbox and a button. I want to be able to input the records on the datasheet onto a report using the value on the textbox by clicking the subform button.

The report has a record source linked to query qryrpt. If I put a criteria on the qrypt, it keeps asking for a parameter to be entered. I want to avoid this since the parameter is the same as the value on the subform textbox. Is there a better way of doing this? Could someone please point me to the right direction?

Private Sub createreport_Click()

Dim Task As String
Dim SearchResults

Me.txtSearch.Value = Me.SearchResults
Task = "SELECT * FROM tblA WHERE ((Location Like ""*" & SearchResults & 
"*""))"

Me.frmDatasheet.Form.RecordSource = Task

Me.SearchResults.Value = Reports!rptName.txtheading
DoCmd.OpenReport "rptName", acViewPreview

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Fil
  • 469
  • 3
  • 12
  • 1
    Either have the parameter in query reference form control or apply filter criteria with OpenReport command: `DoCmd.OpenReport "rptName", acViewPreview, , "Location LIKE '*" & SearchResults & "*'"` – June7 Jun 10 '19 at 23:01
  • @June7 I have tried that but the i keep getting the error report is misspelled or does not exist. – Fil Jun 11 '19 at 06:33
  • So what is name of report? – June7 Jun 11 '19 at 09:42
  • Thanks June7, also i found out that if i use: If Application.CurrentProject.AllReports("rptName").IsLoaded = True Then DoCmd.OpenReportt "rptName", acViewPreview, , "Location LIKE '*" & SearchResults & "*'" End If works. – Fil Jun 11 '19 at 20:42

2 Answers2

0

as @june7 suggested is one possibility

I would rather approach differently

create a query and save as reportQuery_Task with a query as below

SELECT * FROM tblA

use this query as a Recordsource in your report rptName

Private Sub createreport_Click()

Dim Task As String
Dim SearchResults


Me.txtSearch.Value = Me.SearchResults
Task = "SELECT * FROM tblA WHERE ((Location Like ""*" & SearchResults & 
"*""))"

Me.frmDatasheet.Form.RecordSource = Task

'used this to change your query at run time based on the user selected criteria.
CurrentDb.QueryDefs("reportQuery_Task").sql = Task

Me.SearchResults.Value = Reports!rptName.txtheading
DoCmd.OpenReport "rptName", acViewPreview

End Sub
Tarun. P
  • 422
  • 7
  • 16
0

I did just go through this myself. Please see this. Using the subform's button's _Click Event Procedure, use DoCmd's last parameter OpenArgs to send a prebuilt delimited list (recordset first followed by other parameteres) with the .OpenReport procedure. In the report's _Open Event Procedure use Split with the aforementioned delimiter to breakout your parameters appropriately.

Tim
  • 71
  • 9