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 rptWhateverregistration

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/ac...001117077.aspx

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

-z