Creating a form to enter parameters into a report

[Dave0291]

Hi There,

I need to create a form with 3 fields (registratonNum , startDate and endDate).

I want to make a report that will show data based on the registrationNum passed into the form during the time frame (startDate to endDate) specified in the form. I'm new to access and I can't seem to get this to work.

Any help would be greatly appreciated.

Thank you.

[anoble1]

Dave,

Maybe I can help out a little bit for now. Just create a regular form. Get a Text Box for the registrationNum . The 2 more text boxes for your date fields. There are several ways to do the limiting with your 3 options.

of course you will have to change some stuff to make your custom report work.. :)

I had to make a table to handle my reports. An ID field, Report name field, then the rptWhateverregi stration

I made something similiar, On the report Drop down I used this:

Private Sub cmbReportName_AfterUpdate()
    
    ResetOptions
    If Nz(cmbReportName.Value, "") <> "" Then
        'Grab the report name and return the Filters below
        If GetObjectName(cmbReportName.Value) <> "" Then cmdView.Enabled = True
        
        Select Case cmbReportName.Value
            Case 1
                'Customer Account Type 
                Me.RegistrationNum.Enabled = True
                Me.startDate .Enabled = True
                Me.endDate.Enabled = True
                cmdView.Enabled = True    
            Case 2
                'Report 2
                Me.RegistrationNum.Enabled = True
                Me.startDate .Enabled = True
                Me.endDate.Enabled = True
                cmdView.Enabled = True    
          
        End Select
    Else
    
                
    End If
    
End Sub

Then when you click the VIEW button with the criteria:

Private Sub cmdView_Click()

If Nz(cmbReportName.Value, "") <> "" Then
DoCmd.OpenReport GetObjectName(cmbReportName.Value), acViewPreview, , GetWhereCond

Then I created a new module

Public Function GetObjectName(id As Integer) As String

Dim records As Recordset
Dim sqlStatement As String
Dim returnValue As String
    
    'Grab the number in tblReportName and grab the Report Name
    sqlStatement = "SELECT * FROM tblReportName WHERE ReportID = " & id
    Set records = CurrentDb.OpenRecordset(sqlStatement)
    If records.EOF Then
        returnValue = ""
    Else
        returnValue = Nz(records("ObjectName"), "")
    End If
    records.Close
    
    'Records the string and changes the name
    GetObjectName = returnValue

End Function

Public Function GetWhereCond() As String

Dim whereCond As String
    'Use the Report Main Form
    With Forms!frmMain
    
                If Nz(!registratonNum.Value, "") <> "" Then whereCond = whereCond & " AND (registratonNum = " & """" & !registratonNum.Value & """" & ")"
        If Nz(!startDate.Value, "") <> "" Then whereCond = whereCond & " AND (startDate = " & """" & !startDate.Value & """" & ")"
        If Nz(!endDate.Value, "") <> "" Then whereCond = whereCond & " AND (endDate = " & """" & !endDate.Value & """" & ")"
        
        
    End With
    
    If whereCond = "" Then
        GetWhereCond = ""
    Else
        GetWhereCond = "(" & Right(whereCond, Len(whereCond) - 5) & ")"
    End If

End Function

[Dave0291]

Wow thank you for the detailed response. I'll give it a shot.

[NeoPa]

The concepts within Example Filtering on a Form should help with this. Instead of filtering the current form you open a report with the same filter string prepared.

[zmbd]

You might also take a look at:

http://office.microsoft.com/en-us/access-help/using-parameters-with-queries-and-reports-HA001117077.aspx

I often use parameter based queries using a form for simple reports...

-z