Access Form Filtered Result

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Heather112

Thread Starter
Joined
Jun 21, 2012
Messages
16
Hi Everyone,

I have a Access Form set up so users can enter data that links back to an Access Table. The Table will be split so users with access to the Access Form will not have access to the Table. The users entering the data into the form aren't what I would call technically advanced which is why I don't want them to have access to the Table... I'd like to protect the integrity of the data in that way.

However, I do want the users to be able to access certain stats about what they have entered into the Form in a controlled way. I would like to build a macro or separate Form that will:
1) Prompt the user to enter their name or, ideally, allow them to choose from a list of names. This would link back to a field in the Form that contains a value set list of names including a "" option for the user to enter an alternative not included on the list.
2) Next I would like to filter by a date range. I have been successful in doing this part. I created a new Form not bound by any query or table and generates a Report. The Form has two text boxes named txtStartDate and txtEndDate with a button named cmdPreview that On Click's property has this code (thank you http://allenbrowne.com/casu-08.html):
Code:
Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler     
   
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  
    
    
    strReport = "rptSales"      
    strDateField = "[SaleDate]" 
    lngView = acViewPreview     
    
   
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If
    

    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    Debug.Print strWhere      
    DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub
I don't have to have the results open in a Report if there is a better way.

3) I have the Report set to show me the line items from each invoice entered by each person. If it is possible, I would like instead to have only a sum of those line items (Table field is called [ITEMS ENTERED]) for that period selected above as well as doing a SUMIF type formula to give me a sum unique Invoice Numbers (labelled [INVOICE NUMBER] on the Table) for that person for that date range.

Note: It is not strictly necessary that the users be able to filter by name if the Report can be set in a way that only shows the totals as per bullet 3 above. It does have to break it down by each user however. Also, the 'SUMIF' type calculation in bullet 3 is not strictly necessary either, more of a wish item than a must have.

Any help anyone could provide would be much appreciated. I am not able to provide the worksheet for reference but I can certainly provide the names of the columns for reference if I have not done so accurately already.

Thank you so very much!

Heather
 

OBP

Joined
Mar 8, 2005
Messages
19,895
What you want to do with summarising by Invoice is what Queries do best, it can then be displayed in a Report or on a Form.
You need to select Totals, set Invoice Number to "Group by" and the value to be summed to "Sum".
Do you have a table with the User Names(you should), if so you can just create a Combo to select the User.
 

Heather112

Thread Starter
Joined
Jun 21, 2012
Messages
16
Thank you for replying.

I created a query and used the Invoice Number as the Group By, and set the Item line to Sum. However this doesn't allow me to sort by the individual user or filter by dates. These features I really need.

Thank you again for your help.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
You can do the "Filtering" in a previous query that is used by your new query to get the values that you want.
 

Heather112

Thread Starter
Joined
Jun 21, 2012
Messages
16
Thank you.

I like the query for the sum but I would like a more flexible approach for the user filter. The users are often in flux but there are a set group. This set group is included on my table as a Combo Box, Value List but includes a "" option to allow ad hoc users to enter their info. If I set a query it won't be flexible enough to account for these ad hoc users.

I like the coding for the date filter and was hoping for something similar for the user filter.

Thanks again!
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Allen Browne has used a Report Filter I tend to use Query Filters as they are more flexible.
You can Filter the Query using the values from the Form.
 

Heather112

Thread Starter
Joined
Jun 21, 2012
Messages
16
Is there a way to allow the users to set the filters without accessing the query though? I just want them to press a button, enter their name & date range and get a result. I do not want them to have to run a query (it's a stretch getting them to fill out a pre-made Access Form). I'm not sure how to set a query that doesn't require the user to set their parameters as part of building the query. How would I do this?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
When a Report is based on a Query the query is automatically run when you open the report, so the button only has to open the report.
You use form fields and use that as the Criteria using the Format
forms![formname]![fieldname]
in the Criteria Row, where formname is the name of the form and fieldname is the name of a field containing the required data.
If it is between 2 dates you use
Between forms![formname]![fieldname1] And forms![formname]![fieldname2]
which contain the dates.
 

Heather112

Thread Starter
Joined
Jun 21, 2012
Messages
16
Thank you again for helping but this.

I understand the report will link to the query and I can set my fields on the report. However, my trouble is the Criteria will constantly be changing so I can't set the between criteria upfront. A user may need to pull entry info between January-May 2012 or all of January 2013 or just a few days in February. This is my trouble, I can't preset the search criteria since it will always be changing.

If that's possible with what you suggest perhaps I'm just missing it. Can you give me a sample report perhaps? I need the results separated first by User, then by date range where the range can be entered by the user at the time they run the report, then proved the results of the query showing the Sum of Line Items.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Do you need to have the User and the dates as criteria in the same query or just one of them?
The dates would be entered by the user in the form, so they can be a few days, a few months or a few years.
 

Heather112

Thread Starter
Joined
Jun 21, 2012
Messages
16
Ok dates perfect. Thank you. And I do needs the results displayed by user first, not sure if that means they'll have to on the query or not. But the users do link back to a combo box, value list on my table. The users can enter names not preset onthe list though.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
If the user one is a different query to the Dates one that will be fine, otherwise it needs a field on the form to say which Criteria to use.
 

Heather112

Thread Starter
Joined
Jun 21, 2012
Messages
16
I have a Table that has the dates and the User Names. There is only one Table. I'm not sure what this means in terms of how to set up the Query as I still don't quite understand how a query will work for my purposes in terms of filtering by User and Date Range.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Can you provide me with a copy of the database with some dummy data in the Table for me show you how it works?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top