Report prompts for criteria

[amberdextrous]

Hello. I'm new to Access and VBA. I'm developing a database for my company and I've basically been teaching myself as I go along, and I've been using A LOT of code help from forums. We have a cabinet full of CDs with archived project files on them and if you need to look at an old project for whatever reason, there's no way to see what's on each disc, which needless to say causes a lot of headaches. So I'm making a database that stores project data and disc data, so that you can do a search by project #, client #, etc. and the database will pull up the names of the disc(s) the data is stored on.

That being said, I have a form that displays all records and when you click the Search button, a popup form displays so the user can search by various criteria...once the search executes, the popup closes and the data on the first form is filtered to show the data matching the user's criteria.

There is also a "Generate Report" button that when clicked, generates a report of only the records matching the criteria. Here's my problem: I want it to perform so that when clicked, the report is automatically generated based on the Search Results. As it stands, when it is clicked, Access prompts for criteria, which means the user has to re-input their criteria. The report works when you do this, but it is redundant for the user. I want the data to pass to the report without further input.

Here is the SQL for my query used to search the records. The report is also based off this query, which is probably what's causing my problem:
SELECT Client.[Client ID], Project.[Project ID], Disc.[Disc ID], Client.[Client Description], Project.[Project Description], Project.[Project Manager]
FROM Client, Project, Disc, [Project On Disc]
WHERE Client.[Client ID]=Project.[Client ID] And Project.[Project ID]=[Project On Disc].[Project ID] And [Project On Disc].[Disc ID]=Disc.[Disc ID] And Client.[Client ID] Like "*" & Forms!SearchForm!EnterClientNumber & "*" And Client.[Client Description] Like "*" & Forms!SearchForm!EnterClientName & "*" And Project.[Project ID] Like "*" & Forms!SearchForm!EnterProjectNumber & "*" And Project.[Project Description] Like "*" & Forms!SearchForm!EnterProjectDescrip & "*" And Project.[Project Manager] Like "*" & Forms!SearchForm!EnterProjectManager & "*" And Disc.[Disc ID] Like "*" & Forms!SearchForm!EnterDiscID & "*"
ORDER BY Client.[Client ID], Project.[Project ID], Disc.[Disc ID];

This is the code for "View all" and "Generate report" on the main form where the records are displayed:
--------------
Private Sub ShowAllButton_Click()

Dim LSQL As String

'Display all customers
LSQL = "SELECT Client.[Client ID], Project.[Project ID], Disc.[Disc ID], Client.[Client Description], Project.[Project Description], Project.[Project Manager] FROM (Client INNER JOIN Project ON Client.[Client ID]=Project.[Client ID]) INNER JOIN (Disc INNER JOIN [Project On Disc] ON Disc.[Disc ID]=[Project On Disc].[Disc ID]) ON Project.[Project ID]=[Project On Disc].[Project ID]"

Form_ViewDataForm.RecordSource = LSQL
Form_ViewDataForm.Caption = "View all records"

MsgBox ("All records are now displayed.")

End Sub

Private Sub GenerateReportButton_Click()
On Error GoTo Err_GenerateReportButton_Click

Dim stDocName As String

stDocName = "rptSearchResults"
DoCmd.OpenReport stDocName, acPreview

Exit_GenerateReportButton_Click:
Exit Sub

Err_GenerateReportButton_Click:
MsgBox Err.Description
Resume Exit_GenerateReportButton_Click

End Sub
--------------

This code is on my Search popup form:
--------------
Private Sub SearchButton_Click()

'Filter data based on search criteria
Form_ViewDataForm.RecordSource = "SearchQuery2"
Form_ViewDataForm.Caption = "Search Results"

'Close Search Form
DoCmd.Close acForm, "SearchForm"

MsgBox "Results have been filtered."

End Sub
--------------

If anyone knows how I can get the main form to pass the query results to the report without having Access prompt for the criteria again, I would REALLY appreciate the help!!

 

[pbaldy]

One solution would be to hide the search form rather than close it, so its values would still be available for the report.

Paul
MS Access MVP 2007/2008

 

[PaulBricker]

Look at the Filter property for the Form. Basically in the click event for your open report button you put

Private Sub GenerateReportButton_Click()
On Error GoTo Err_GenerateReportButton_Click

    Dim stDocName As String
    [blue]Me.FilterOn = True[/blue]
    stDocName = "rptSearchResults"
    DoCmd.OpenReport stDocName, acPreview, ,[blue]Me.Filter[/blue]

Exit_GenerateReportButton_Click:
    Exit Sub

Err_GenerateReportButton_Click:
    MsgBox Err.Description
    Resume Exit_GenerateReportButton_Click
    
End Sub

Paul

 

[amberdextrous]

How would I code it to use the hide technique? I tried to myself using a VBA book someone lent me, but I kept getting errors because I don't know what I'm doing

I tried the Filter property method too, but it still prompted me to input the criteria again when I clicked the Generate report button.

 

[pbaldy]

From inside the search form:

Me.Visible = False


Paul
MS Access MVP 2007/2008

 

[PaulBricker]

pbaldy's suggestion is fine, but I'm curious what value is written to the Filter property once you do all your filtering? You can find it by leaving the property box for your form visible while filtering your form.

Paul

 

[pbaldy]

Based on the code above, the OP isn't using the Filter property; they're setting the recordsource.

Paul
MS Access MVP 2007/2008

 

[PaulBricker]

The filter property is written to when the form is filtered from it's original recordsource. It's that property that is read when you use it in the Where argument of the OpenReport method. We use it without any reprompting issue here so I was hoping to see what exactly was being written to that property when the OP's form was filtered.

Paul

 

[amberdextrous]

I didn't see anything in the Filter property box, neither before nor after running the query.

Oh great I noticed another problem...the search only works on my old records...whenever I add a new record and try to search for it, it won't come up in Search Results...but it will come up when I hit the "Show all records" button.

 

[amberdextrous]

Ok, i found a solution to my first problem...I just combined my Search and View records forms into one form, and when I hit "Generate report" it passes the criteria to the report. Yay! But now I have that second problem I mentioned in my previous post...I will start a new thread for that one.