Filter a form for uncontacted records

[mindbender]

I have a form that is filtered on open to show only records aligned to the rep that logged in. That filter works fine.

What I am trying to do is have a button to show them the records they have not touched yet. I was trying to use ApplyFilter like this

Private Sub Command395_Click()
    
DoCmd.ApplyFilter , "SELECT tblMain.* FROM tblMain LEFT JOIN tblActivities ON tblMain.Account = tblActivities.Account WHERE (tblActivities.[Activity Create Date]IS NULL) AND tblMain.Employee_Number = Forms.Logon_Form.cboEmployee"
                
End Sub

But I constantly get runtime error 3075 saying there is a syntax error.

I also have an unfilter button that removes other filter options the rep can apply and I would like the above uncontacted filter to be removed by this unfilter button as well.

The unfilter presently looks like this

[Private Sub cmdReset_Click()
    'Purpose: Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    Dim strWhere As String
       
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next

    'Remove the form's filter.
        Me.Filter = strWhere & "[Account Name] Like '*" & Me.AccountName & "*' AND [Employee_Number] = '" & Forms!Logon_Form!cboEmployee & "'"
           ' Me.FilterOn = False
            'Forms!frmMain.RecordSource = "QueryTblMain"
            'DoCmd.Requery
    End Sub

Any help would be appreciated.

[ranman256]

build a query. It doesnt get the syntax wrong.
then you can either keep the query or copy out the SQL,
you want an outer join, and show all REPs, and under accounts , set the criteria: acctname is null.
(this says show all accounts that are null, not having contacted.)

[aytee111]

Have the record source of the form have a criteria of only showing the rep's data. That will remove one issue of filter/unfilter.

Then set the filter using:
Me.Filter="IsNull([Activity Create Date])"
Me.FilterOn=True
Me.Requery

Unset the filter with:
Me.FilterOn=False

[mindbender]

Have the record source of the form have a criteria of only showing the rep's data. That will remove one issue of filter/unfilter.

Then set the filter using:
Me.Filter="IsNull([Activity Create Date])"
Me.FilterOn=True
Me.Requery

Unset the filter with:
Me.FilterOn=False

I get prompted for an Activity Date parameter when I do this. I should have mentioned (and I apologize) that frmMain has the account data and the activity data is in a subform on the main form called tblActivitiesSubform.

That is why I was trying to go the sql route.(Not saying this is the correct way either) I have the form limiting to the current users records now and working fine.

Thanks

[aytee111]

So you have two record sources. Have them both have criteria set to rep's data only.

The filter for activity date is being applied to the subform? If so, put the filter on the subform:
Me!subformname.Form.Filter="IsNull([Activity Create Date])"
me!subformname.Form.FilterOn=True
Me!subformanme.Requery

[mindbender]

I am still stuck...sorry for being so thick.

Maybe I am explaining it wrong. I have a logon form that the user enters their id and password then the main form opens constrained to the records assigned to them. I then have an account search section where they can enter in part of an account name and hit a button and it will return the accounts that meet that search criteria again constrained to their records. There is also a remove filters button which removes all previous filters and again returns only the records aligned to them. All of this data is in the main table that populates the main form

The activity data is in a subform. What I am trying to do is return only the accounts aligned to the user where there is no activities on them yet when they click a Uncontacted Button on the main form.

If it helps I have attached a screenshot of the main form.

There is also a Last Modified Date field on the form which looks at a union query of all the forms and subforms and captures the DMAX of those dates and displays it in that field. I thought of using that field because if it is not empty then it is contacted but with not populating that date in a field in the main table, that field does not seem to work properly for the filter. If it was possible to get that max date to write to a field in the main table I could most likely use that for the filter as well.

Sorry for the wordiness - just trying to add as much detail as possible

[aytee111]

(Side note: having users enter passwords is a very dangerous method, users forget passwords. Plus all the additional coding and database maintenance associated with it. Have a Users table with their login id - Environ("username") - and use that to determine who is using the database. You can expand it to add security levels and all sorts of things.)

No apologies needed.

Create a query with the data needed for frmMain and save it. Have the criteria built in to the query of rep id = Forms!LoginForm!UserID. Use this as the record source for frmMain. When the user enters an account to search for, filter frmMain based on that value as in post # 3. You do not need to worry about showing records for this user only as the record source/query has already taken care of that.

This query will also be the record source for the subform. Link the subform to the main form using account number. When the user selects Uncontacted Accounts, do the filter as in post # 5.

Create another query which contains the max last modified date per account. Join this query to the first query as the record source for the subform. Join by account number with a left join, so that if there is no last modified date then a record will still show up.

Get these queries working properly prior to going in to form design.

[Micron]

Not sure where this is going, but as far as your filter query is concerned, you'd probably have that solved if you followed the advice in post 2 (ranman256). If that were true, you might be half way or all done by now. Incorrect syntax is almost always immediately flagged by Access in sql view of a query. In your case, I suspect it would highlight Forms.Logon
because I'm pretty sure it should be
Forms!Logon

If you did follow that advice, I missed it and apologize.
Also, not sure I like the idea of filtering a subform independently of the main form. My preference would be to include the filter as a value from a checkbox or combo for example, and requery the form in its AfterUpdate event. Clicking a button to filter a form provides no visual clue that the filter has been applied.
This reference doesn't look right to me Me!subformname.Form.Filter because I'm used to using
[Forms]![Main form name]![subform control name].[Form] and then either ! or . followed by the rest of the code, depending on what I'm doing. Even if using Me, wouldn't it be
Me![subform control name].[Form]
because [subform control name] refers to the subform control, not the subform.

[mindbender]

(Side note: having users enter passwords is a very dangerous method, users forget passwords. Plus all the additional coding and database maintenance associated with it. Have a Users table with their login id - Environ("username") - and use that to determine who is using the database. You can expand it to add security levels and all sorts of things.)

No apologies needed.

Create a query with the data needed for frmMain and save it. Have the criteria built in to the query of rep id = Forms!LoginForm!UserID. Use this as the record source for frmMain. When the user enters an account to search for, filter frmMain based on that value as in post # 3. You do not need to worry about showing records for this user only as the record source/query has already taken care of that.

This query will also be the record source for the subform. Link the subform to the main form using account number. When the user selects Uncontacted Accounts, do the filter as in post # 5.

Create another query which contains the max last modified date per account. Join this query to the first query as the record source for the subform. Join by account number with a left join, so that if there is no last modified date then a record will still show up.

Get these queries working properly prior to going in to form design.

I am so close. I am just confused by one part. I have everything done except the record source for the Activity subform. I am not sure what I am supposed to be doing here. You mentioned using the same query as the main form (if I understood properly). That query looks at the account data. The activities subform is linked to its own table to store activity data. I have the parent -child set on account from subform to main form. and have a query showing the max of last modified date. Should my record source for the subform be tbl main joined to activities table joined to max of last modified date all on account and the join from activities to max of last modified date be the left join? As when I do that it filters the subform from that current account record only and not all the account records with activities that criteria

thanks again for taking the time to walk me through this. It is very helpful

[aytee111]

No, you're right. The subform will have its own query/record source. The link between it and the main form will take care of filtering it by account number. If there is a one to one relationship between account and rep (only one rep per account) then you will not need to worry about the user name. Add the second query to the activities subform's record source to show the max of date. So now you will have all the activities data for the account shown on the main form which is only for the rep who logged in. All you need worry about now is the button for Uncontacted Accounts which will use the filter method as shown above.

[mindbender]

I have included some images of what I have going on. The first is the query that is the record source for the activities subform. I joined it to the query that shows the max of last modified date. Up to this point everything is working fine in terms of linkages etc.

The second image is what is run when the uncontacted accounts button is clicked. When I click this button it just filters the current accounts subform.

example - rep starts with 314 accounts. I click button and it stays at 314 but only shows activities under the account with a null create date. What I was hoping to see was it filter even further to say 250 records and those be the accounts that have no activities on them at all

[aytee111]

Now I am confused! What is the "current accounts subform"? It looks like your main form and subform should not be linked in any way, is this what you have done?

but only shows activities under the account with a null create date

Are you saying that 314 records are still being displayed but that some of those lines are blank?

[mindbender]

Now I am confused! What is the "current accounts subform"? It looks like your main form and subform should not be linked in any way, is this what you have done?



Are you saying that 314 records are still being displayed but that some of those lines are blank?

No that would be me confusing you. Sorry

Should say

The second image is what is run when the uncontacted accounts button is clicked. When I click this button it just filters the current accounts activities subform. - so instead of showing all accounts with no activities in the filter it is only filtering the activities subform for the visible account.

This image is the way the main form is filter on open - works perfectly. It shows 314 (of the 1552 total) that belong to the rep that logged in.


The next is after I have clicked the Uncontacted accounts button. You will see the record count is still 314 but the Activities subform is filtered to show a blank activity. What I was expecting was the number of records to go from 314 to say 250 accounts and those be the ones with no activities in the activities subform

[aytee111]

so instead of showing all accounts with no activities in the filter it is only filtering the activities subform for the visible account.

That is because your subform is linked to the main form using the account number. You will need to remove that and add a filter for when the account search is done. So, on open the activities subform will have the filter set to account number of the main form. This same filter will also be applied when the search button is clicked. Ending up with one record source for the subform showing all records for the rep, then a different filter applied depending on which button is clicked.

Your second question, you are referring to filtering the main form, that is what the 314 applies to, nothing to do with activities. Apply a filter to the main form as well when the button is clicked.
Me.Filter=...
Me.FilterOn=True
Me.Requery

[mindbender]

That is because your subform is linked to the main form using the account number. You will need to remove that and add a filter for when the account search is done. So, on open the activities subform will have the filter set to account number of the main form. This same filter will also be applied when the search button is clicked. Ending up with one record source for the subform showing all records for the rep, then a different filter applied depending on which button is clicked.

Your second question, you are referring to filtering the main form, that is what the 314 applies to, nothing to do with activities. Apply a filter to the main form as well when the button is clicked.
Me.Filter=...
Me.FilterOn=True
Me.Requery

I have been messing with this and am now getting a result but it is not quite the result I am looking for.

I have a field called Last Modified Date on the main form. This field is calculated as follows =DMax("[Last Modified Date]","UnionTables","Account='" & [Account] & "'"). I basically have a last modified date on each form/subform and I capture the date and time it was last updated. The I use the union to get the most recent date/time and then the field last modified date pulls in that date/time.

When I use Me.Filter = "IsNull([Last Modified Date])" it runs but does not seem to filter out the records with a date/time in the field. The only thing I can think of is because it is generated with the form so in essence they are all null when the filter is applied......is there a way around this? Maybe copying the existing date/time to another field?

Thanks again for everthing