By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 913 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Creating a form to enter parameters into a report

P: 33
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.
May 18 '12 #1
Share this Question
Share on Google+
4 Replies


anoble1
100+
P: 223
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbReportName_AfterUpdate()
  2.  
  3.     ResetOptions
  4.     If Nz(cmbReportName.Value, "") <> "" Then
  5.         'Grab the report name and return the Filters below
  6.         If GetObjectName(cmbReportName.Value) <> "" Then cmdView.Enabled = True
  7.  
  8.         Select Case cmbReportName.Value
  9.             Case 1
  10.                 'Customer Account Type 
  11.                 Me.RegistrationNum.Enabled = True
  12.                 Me.startDate .Enabled = True
  13.                 Me.endDate.Enabled = True
  14.                 cmdView.Enabled = True    
  15.             Case 2
  16.                 'Report 2
  17.                 Me.RegistrationNum.Enabled = True
  18.                 Me.startDate .Enabled = True
  19.                 Me.endDate.Enabled = True
  20.                 cmdView.Enabled = True    
  21.  
  22.         End Select
  23.     Else
  24.  
  25.  
  26.     End If
  27.  
  28. End Sub
Then when you click the VIEW button with the criteria:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdView_Click()
  2.  
  3. If Nz(cmbReportName.Value, "") <> "" Then
  4. DoCmd.OpenReport GetObjectName(cmbReportName.Value), acViewPreview, , GetWhereCond
Then I created a new module
Expand|Select|Wrap|Line Numbers
  1. Public Function GetObjectName(id As Integer) As String
  2.  
  3. Dim records As Recordset
  4. Dim sqlStatement As String
  5. Dim returnValue As String
  6.  
  7.     'Grab the number in tblReportName and grab the Report Name
  8.     sqlStatement = "SELECT * FROM tblReportName WHERE ReportID = " & id
  9.     Set records = CurrentDb.OpenRecordset(sqlStatement)
  10.     If records.EOF Then
  11.         returnValue = ""
  12.     Else
  13.         returnValue = Nz(records("ObjectName"), "")
  14.     End If
  15.     records.Close
  16.  
  17.     'Records the string and changes the name
  18.     GetObjectName = returnValue
  19.  
  20. End Function
Expand|Select|Wrap|Line Numbers
  1. Public Function GetWhereCond() As String
  2.  
  3. Dim whereCond As String
  4.     'Use the Report Main Form
  5.     With Forms!frmMain
  6.  
  7.                 If Nz(!registratonNum.Value, "") <> "" Then whereCond = whereCond & " AND (registratonNum = " & """" & !registratonNum.Value & """" & ")"
  8.         If Nz(!startDate.Value, "") <> "" Then whereCond = whereCond & " AND (startDate = " & """" & !startDate.Value & """" & ")"
  9.         If Nz(!endDate.Value, "") <> "" Then whereCond = whereCond & " AND (endDate = " & """" & !endDate.Value & """" & ")"
  10.  
  11.  
  12.     End With
  13.  
  14.     If whereCond = "" Then
  15.         GetWhereCond = ""
  16.     Else
  17.         GetWhereCond = "(" & Right(whereCond, Len(whereCond) - 5) & ")"
  18.     End If
  19.  
  20. End Function
May 18 '12 #2

P: 33
Wow thank you for the detailed response. I'll give it a shot.
May 18 '12 #3

NeoPa
Expert Mod 15k+
P: 31,409
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.
May 18 '12 #4

zmbd
Expert Mod 5K+
P: 5,397
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
May 20 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.