search/ filter criteria of query to generate report - using new/different macros from form/reports

[ERS37]

Status: I have a single report type that is generated from a query. I use a form (Call it "F") with search box macros ("Combo1" and "Combo2") that selects criteria from the query (call it "Q") to then generate the report (call it report "A"). This works fine.



Question: Now I'd like to set a macro from a different/unique report (call it report "B"), and make that macro'ed Text Box (called "DiseaseName") value then select criteria from the "Q" query and generate report "A" back to me, as this new value criteria. I hope that makes sense.

Problem: When I add in this new criteria/macro from "B", it'll pop up a box that wants me to input criteria values for "combo1" and "combo2" again (or leave them blank). If I leave them blank, it's fine and the A report generates as I would like.

WHAT I HOPE TO DO: How do I get rid of that pop-up box looking for the "Combo1" an then "Combo2" value. Likewise when I search from the initial form, it works great but will pop up a box looking for value "DiseaseName" from the "B" report.... again, if I leave the pop-up blank, my report generate fines.


FROM (([newHGNC protein-coding genes] INNER JOIN Clinvar_gene_condition_source_id ON [newHGNC protein-coding genes].[Approved Symbol] = Clinvar_gene_condition_source_id.GeneSymbol) INNER JOIN [XYZ fields] ON Clinvar_gene_condition_source_id.GeneID = [XYZ fields].GeneID) INNER JOIN Clinvar_gene_condition_source_id_NO_DUPL ON ([XYZ fields].GeneID = Clinvar_gene_condition_source_id_NO_DUPL.GeneID) AND ([newHGNC protein-coding genes].[Approved Symbol] = Clinvar_gene_condition_source_id_NO_DUPL.GeneSymbo l)
WHERE ((([newHGNC protein-coding genes].[Approved Symbol]) Like "*" & [Forms]![F]![Combo1] & "*") AND ((Clinvar_gene_condition_source_id.DiseaseName) Like "*" & [Forms]![F]![Combo3] & "*")) OR (((Clinvar_gene_condition_source_id.DiseaseName) Like "*" & [Reports]![B]![DiseaseName] & "*"));

[June7]

Both form F and report B must be open for the query filter to work.

[ERS37]

Indeed. It does work without an error if I leave windows open. There is no way around that? No way to code a where that essentially says "use F OR B, and if one isn't present, then ignore it"

I'm new to all this and much greatful for the help!

[June7]

Not within query.

This could be done with VBA code. The code would construct the filter criteria as appropriate then pass the criteria to form or report by argument to filter records as the object opens. The code could be in a command button Click event.

Review this article http://allenbrowne.com/ser-62code.html

Sub cmdOpenReport _Click()
Dim strFilter As String
If CurrentProject.AllReports("B").IsLoaded Then
strFilter = strFilter & ...
End If
If CurrentProejct.AllForms("F").IsLoaded Then
strFilter = strFilter & ...
End If
'See if the string has more than 5 characters (a trailing " AND ") to remove.
'see code from Allen Browne article
DoCmd.OpenReport "ReportA", , , strFilter
End Sub

[ERS37]

Thank you! I will take a look and give this a try.

[ERS37]

***How about this... can I set a macro to do this?:

when I click the textbox/"diseasename" in Report B, can that then auto-populate into the combobox2?

*Thinking I can have a macro from the "diseasename" text box that would: openformF;populate combo2;click command button... thus generate report A again.

[June7]

Reports are not intended to be interactive, and in PrintPreview definitely can't (except for right click shortcut menu). I have never designed process that pulls value from a report as parameter for another report. I have parameters entered on forms.

It is possible to set focus on a particular record in ReportView and code can read values. The button would have to be in Group or Detail section.

I don't use macros, only VBA.

[ERS37]

Here's what I did:

In the Query I removed the whole WHERE clause... no criteria.

Then in the Form, I changed the CommandBox Macro. Now there is a command box for each of the ComboBox1/2 and the respective Macro for the CommandBox is: OpenReport, Report Name = A ... with a WHERE=[Approved_Symbol]=[Forms]![F]![Combo1] (or Combo2).

That gave me the same functionality from the form search as the previous use of Criteria/WHERE in the Query and makes a Report A that is filtered for the values in ComboBox

Then in Report B.... so that I could use a text box as the factor to Generate a Report A from: I made a macro off the text box that again is: OpenReport, Report Name=A ... WHERE=[DiseaseName]=[Reports]![B]![DiseaseName]

This works. Full functionality the same using the WHERE within a Query, but rather applying the WHERE as a the command to build Report.

You totally got me thinking and looking in the right direction. I thank you for that.