Code found not sure how to use it exactly

[terryechols1]

I found a post with some code I thought would work for my needs but I don't know how to use it exactly.

The post (with the code) can be seen here:
Best way to prompt for report parameters in microsoft access

I have the form for the date inputs set up, I have the reports OnOpen using [Event Procedure] and the code pasted in there. When I generate the report, the pop up form is triggered but once I enter the dates it doesn't do anything. The original post doesn't go into details about the use of the code so I thought I'd ask here.

Here are the two code snippets:

Private Sub Report_Open(Cancel As Integer)
  Dim dteStart as Date
  Dim dteEnd As Date
 
  DoCmd.OpenForm "dlgGetDates", , , , , acDialog 
  If IsLoaded("dlgGetDates") Then
     With Forms!dlgGetDates
       dteStart = !StartDate
       dteEnd = !EndDate
     End With
     Me.Recordsource = "SELECT * FROM MyTable WHERE DateField Between #" _
        & dteStart & "# AND #" & dteEnd & "#;" 
     DoCmd.Close acForm, "dlgGetDates"
  End If
End Sub

AND:

Function IsLoaded(ByVal strFormName As String) As Boolean
 ' Returns True if the specified form is open in Form view or Datasheet view.
  Const conObjStateClosed = 0
  Const conDesignView = 0
 
  If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
     If Forms(strFormName).CurrentView <> conDesignView Then
        IsLoaded = True
     End If
  End If
End Function

I'm sure I have to change MyTable to my actual table [Calls] and DateField to my field name [Resolved Date] but then what?

Thanks,
Terry

[nico5038]

I usually try to prevent prompting for report parameters, as people use to make typo's, causing an eroneous report or an empty report.
My approach is to have the parameters on the form and test in my form code or it's resulting in a filled report. When that's the case I activate the [Print] button and show the report in preview mode, still allowing the user to cancel the printing when (s)he has second toughts about the selection.

Idea ?

Nic;o)

[Cbold]

Not sure about the code, here is what I do for date ranges
On the form you created, name one field begindate and the other enddate.
The user will enter the dates on this form. This form could also have a list box with your reports, or command buttons for the report...I always call this form F_SW.

In your query, set the criteria of the date field as
BETWEEN [FORMS].[nameofform].[begindate] AND [FORMS].[nameofform].[enddate]

Now when the report is select it will query for the date range. Hope this helps. If I'm not on the right track, please post.


I use this in all my report queries.

[PhilOfWalton]

Have a look at my article https://bytes.com/topic/access/answe...filter-reports

Phil

[terryechols1]

Good morning all.

I have this working, somewhat, but not as I'd like nor as, I think, it was intended to work.

Here is what I'd like to accomplish.

The way I read the code is that when the report is opened/selected (in the reports drop-down list) the parameters pop-up form is supposed to load then put in the dates - and here is where a bit of code is throwing me - once the dates are added then what? There is no code for an "OK" button or a "Run Query" button so how does one tell the pop-up form to run the code behind the form?

From my understanding this bit of code goes on the reports "On Load" event which pops up the date form.

Private Sub Report_Open(Cancel As Integer)
Dim dateStart As Date
Dim dateEnd As Date
 
    DoCmd.OpenForm "dlgParameters", , , , , acDialog
        If IsLoaded("dlgParameters") Then
            With Forms!dlgParameters
            dateStart = !txtStartDate
            dateEnd = !txtEndDate
        End With
    Me.RecordSource = "SELECT * FROM [Calls] WHERE [Resolved Date] Between #" & dateStart & "# AND #" & dateEnd & "#;"
    'DoCmd.Close acForm, "dlgParameters"
        End If
End Sub
 

And the IsLoaded function as well:

Function IsLoaded(ByVal strFormName As String) As Boolean
 ' Returns True if the specified form is open in Form view or Datasheet view.
  Const conObjStateClosed = 0
  Const conDesignView = 0
 
  If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
     If Forms(strFormName).CurrentView <> conDesignView Then
        IsLoaded = True
     End If
  End If
End Function
 

This bit of code:

DoCmd.Close acForm, "dlgParameters"

I commented out as it was throwing errors. I put the close code on an "OK" button on the date form:

Private Sub cmdOK_Click()
    DoCmd.OpenReport "rptCalls - Resolved", acViewPreview
    DoCmd.Close acForm, "dlgParameters"
End Sub

Here is where the code starts to break, at least in the way I want it to work.

Everything I have setup now works BUT the report has to be opened in the background before opening the date form for the code to work and run.

The way I thought this would work is when the report is selected the date form would pop up (which it does - more below) the dates would be entered (missing run code here for the original code) then the report would be generated.

The way I would like this to work is the way I thought it would work when I found the code. Select report, date form opens, enter dates, --- run code and generate the report.

I have tried everything I can think of but I keep running into errors. I've tried with using a query but get errors then too.

The way it is now I get error "94" Invalid use of Null if I try to load the report first. The line highlighted is

dateStart = !txtStartDate

so it's looking for form data before I get to enter the dates.

So how can I get this to work as intended? Select report, dlgParameters pops up (and does not error), I enter the dates for the report click the "OK" button and the report generates and the pop up form closes?

The only code for the report is the IsLoaded function and the "On Open" event to pop up the form "dlgParameters". Also, as I have it now there is not record source or control source for the report.

The form "dlgParameters" is unbound, no record or control source, has two text boxes for txtStartDate and txtEndDate and an "OK" and "Cancel" buttons with the following code for the buttons:

Private Sub cmdCancel_Click()
    DoCmd.Close
End Sub
 
Private Sub cmdOK_Click()
    DoCmd.OpenReport "rptCalls - Resolved", acViewPreview
    DoCmd.Close acForm, "dlgParameters"
End Sub
 

I'm not sure what I've done right or wrong here and would love some help/guidance.

Terry

[jforbes]

There are a lot of different ways you can fire off a Report:

• You can have the Parameters in the Query that the report is based on. These can point to a Form or Variables.
• Display a parameters dialog box in the Form's on open event to change the Filter or Recordsource.
• Open the Form with a Filter.
• Set some TempVars or OpenArgs when opeing the Report and then having the Report decipher the values on open.

I'm sure there is more, but in my experience, nearly all of these different ways of opening an Report are a mistake and error prone except for opening a report with a Filter. Why? It mostly has to do with the flow of the code. It's best to write code that funnels your user down a chute or slide to the bottom of the cliff instead of throwing them off a ledge. By putting a parameter in the Report's Query or writing code in Report Open Event, your just throwing the code execution at the Report and expecting it to handle things from there. So the report is responsible for gathering and validating the parameters and handling error when these parameters are wrong.

I think Both Nico and Phil have tried to guide you towards a more controlled approach by gathering and validating the users parameters before opening the Report. I haven't looked at Phil's approach, but I would recommend giving it a shot.

What I typically do is write the Report with as little code as possible and write it in a way that it runs wide open and returns all the results. In practice, you wouldn't want to run the Report like this, so then I often write a wrapper routine (or a launcher) that calls the Report. This is where the validation is accomplished on the parameters and only if the validation is passed is the Report executed. Sometimes the wrapper/launcher will prompt for information if the workflow calls for it, otherwise it can kick out an error. Here is simple example of a wrapper:

Private Sub cmdNewBlankQuote_Click()
    gNewQuoteNumber = ""
    DoCmd.OpenForm "QuoteNew", acNormal, , , acFormAdd, acDialog    
    If Len(gNewQuoteNumber) > 0 Then
        DoCmd.OpenReport "Quotes", , , "QuoteNumber='" & gNewQuoteNumber & "'"
    End If    
End Sub

When the button is clicked a global variable is reset to a blank value, then a Dialog box is displayed to get the information needed from the User. After the Dialog box is closed, the global variable is checked to make sure it has a value, and if it does the Report is run with a filter applied to show only the information needed.

Here is a variation on the above theme:

Public Sub printQuote(Byref sQuoteNumber As String)
    If Len(sQuoteNumber) = 0 Then
        gNewQuoteNumber = ""
        DoCmd.OpenForm "QuotePrompt", acNormal, , , , acDialog
        sQuoteNumber = gNewQuoteNumber 
    End If
    If Len(sQuoteNumber) > 0 Then
        DoCmd.OpenReport "Quotes", , , "QuoteNumber='" & sNewQuoteNumber & "'"
    End If    
End Sub

In this variation, if the QuoteNumber is supplied, the Report is printed, otherwise the QuoteNumber is prompted for by the Dialog box and the Report is only printed if the value is supplied.

Lastly, to hammer the point home, here is an example of more complex wrapper/launcher (I've cleaned it up and hidden some stuff to attempt to make it read easier):

Public Function genBOMPDF(ByRef sQuoteNumber As String) As String
On Error GoTo ErrorOut
 
    Dim sFileName As String
    Dim sWildCardFileName As String
    Dim iErrorLevel As Integer
    Dim sReportName As String
    Dim sError As String
    Dim sSQL As String
    Dim sDestination As String
    Dim sTempDirectory As String
 
    ' Validate and Variables
    genBOMPDF = ""
    iErrorLevel = 1
    If Len(sQuoteNumber) = 0 Then
        Call msgBoxError("A Quote Number is Required to generate a BOM PDF.")
        GoTo ExitOut
    End If
    setStatus ("Creating Preview...")
    sFileName = sQuoteNumber & "_BOM_" & getStringDateTime & ".PDF"
    sWildCardFileName = sQuoteNumber & "_BOM_?????????????.PDF"
    sReportName = "BOM"
    sTempDirectory = getApplicationDirectory & "\Temp\"
    Call establishFolder(sTempDirectory)
 
    ' Create Quote
    DoCmd.OpenReport sReportName, acViewPreview, , "QuoteNumber='" & sQuoteNumber & "'"
    setStatus ("Creating PDF...")
    iErrorLevel = 2
    DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sTempDirectory & sFileName, True
    iErrorLevel = 3
    DoCmd.Close acReport, sReportName
 
    iErrorLevel = 4
    setStatus ("Adding Attachment")
    If createAttachment(sTempDirectory & sFileName, sDestination, 3, sQuoteNumber, sFileName, "Quote BOM") = 0 Then
        GoTo ExitOut
    End If
 
    iErrorLevel = 5
    setStatus ("Archiving Previous BOMs")
 
    sSQL = "UPDATE Attachment SET Deleted=-1 WHERE Attachment LIKE '" & Replace(sDestination, sFileName, sWildCardFileName) & "' AND Attachment <> '" & sDestination & " '"
    Call executeSQL(sSQL)
 
    genBOMPDF = sFileName
 
ExitOut:
    setStatus ("")
    Exit Function
ErrorOut:
    Select Case iErrorLevel
        Case 2
            gErrorMessage = "Could not create the Report, please make sure the file '" & gQuoteDirectory & sFileName & "' is not open by Acrobat or another program." & vbCrLf & vbCrLf & "The actual error message is:  " & Err.Description
        Case Else
    End Select
    Call customErrorHandler(mProcedureName, True, Err, Erl, gErrorMessage)
    Resume ExitOut
End Function