Avoid using DoCmd.RunCommand acCmdRefresh when adding a new record

[WCStarks]

I have a ComboBox in the Main Members form, which is sensitive to the status of a variable, Is_Active, in the main Members form, which is always open. The ComboBox allows selection of active or all members, depending on the status of the Is_Active check box. To get the ComboBox to refresh when the Is_Active status is changed, I created a Got Focus event with the following code:

DoCmd.RunCommand acCmdRefresh

This works fine for existing Members records, but when I attempt to add a new record in the Members Form, it generates errors, because other processes are executed when the Refresh is executed. After I "OK" on the two error messages, the new record opens. Apparently, this event procedure is executed when adding a new record. How can I get this On Focus event to not execute when adding a new record, which does not yet have a record ID? The status of Is_Active is irrelevant in this case, anyway.

[WCStarks]

After posting this query, I did some more research and came up with this solution:

Private Sub Combo106_GotFocus()
On Error GoTo Exit_Error
Exit_Error
  Exit Sub
End Sub
DoCmd.RunCommand acCmdRefresh
End Sub

I thought this worked, but I was wrong. When I click on the ComboBox, I get a Compile error Sub or Function not defined.

[Minty]

Try checking for the status of the record.

If Me.Newrecord = True Then 
   Do Something here
Else 
   Do the other things here
End IF

[WCStarks]

Thanks. I tried:

If Me.NewRecord = False Then    
    DoCmd.RunCommand acCmdRefresh
End If

But I still get the same errors. Maybe it is no longer a new record by this time.

[davegri]

Don't understand why the code is in the gotfocus event of the combobox. Why not the AfterUpdate event?

[WCStarks]

Apparently, AfterUpdate, BeforeUpdate and even OnClick are all too late. The list of Active or All members, depending on the status of the Is_Active checkbox, needs to be populated before the Combo box drop-down list is opened. I found that OnGotFocus worked. The problem is that when "DoCmd.RunCommand acCmdRefresh" is in any of the above mentioned event procedures, when a new member record is add, it causes other procedures which normally run on existing records to cause errors when adding a new blank record. I need to find a way for it to not execute when adding a new member record. I tried the "Me.NewRecord ..." statement above, but it did not fix the issue.

[Minty]

Think we will need to see a stripped down DB to fully grasp this problem.
I suspect there may be a more elegant way for your combo to interact with the target form that would overcome the issue.

[Missinglinq]

I think you're not clear on what Refresh actually does:

From Access Help: '...the Refresh method shows only changes made to records in the current set. Since the Refresh method doesn't actually Requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried...To Requery the database, use the Requery method. When the record source for a form is requeried, the current set of records will accurately reflect all data in the record source...'

Linq ;0)>

[WCStarks]

Here is a copy of the sanitized database with personal information replaced. The Combobox is at the top of the SCATeam Tracking form. The Is_Active checkbox is in the header of the Manage SCATeam form. When the Is_Active control is toggled, I need the Combobox to show the correct list. When I added this Statement, "DoCmd.RunCommand acCmdRefresh" to the Combobox's Got Focus, it did what i want it to do. However, now, the db creates errors with other processes when I add a new member record. The active members list is shorter than the "all" list.

SCATeam.zip

I believe the two errors, when opening a new record, are related to the ELookup() functions in the Joined and Discharged fields of the member record. They work fine on existing records. I just need the Refresh to not happen when adding a new member record. I suspect the reason for the error is that the new record doesn't not yet have an ID.

[WCStarks]

The only purpose of using the refresh was simply to refresh the list of Active or All members in the ComboBox, when the Is_Active is toggled. It was not intended to reflect changes to the records. I am not sure this would be an issue when adding or editing individuals, as Access manages that automatically, when leaving an updated record. Right? Without this "DoCmd.RunCommand acCmdRefresh" in the ComboBox's On Got Focus property, after toggling Is_Active, I would need to leave the Member form and return to it to get the ComboBox to refresh.

I am have situations in other aspects of the db where changes may not be properly reflected in some forms. I will look into seeing how I might use the Requery in some of those instances.

[davegri]

To get rid of new record errors,
Make the Joined and Resigned textboxes unbound and add following code to form SCATeam:

Private Sub Form_Current()
    If Me.NewRecord Then Exit Sub
    Me.Joined = ELookup("[Status_Date]", "Membership", "[Status] = ""Joined"" and [Member_ID] =" & [ID], "[Status_Date] Desc")
    Me.Resigned = ELookup("[Status_Date]", "Membership", "([Status] = ""Resigned"" or [Status] = ""Terminated"" or [Status] = ""Emeritus"") and [Member_ID] =" & [ID], "[Status_Date] Desc")
End Sub

I didn't change the existing ELookUp code that was in the textbox data source when I moved it, so I have no idea where those weird dates are coming from for new members, plus I don't know that UDF.

To get the Active checkbox to select the correct members add this code to form ManageSCATeam

Option Compare Database
Option Explicit


Private Sub Is_Active_AfterUpdate()
    Select Case Is_Active
        Case True
            Me.RecordSource = "select * from members where Active = true"
        Case False
            Me.RecordSource = "members"
    End Select
 End Sub

[WCStarks]

Thank you for your help. I will look into implementing the Sub_Form_Current () code you suggest. However, those values need to end up in the member record for access by reports for example. I am updating them automatically this way, to avoid sync issues.

I am sorry. I should have mentioned that the Is_Active checkbox is referenced in many other contexts. Will this still work in these other contexts? I have used the above mentioned Refresh statement for ComboBoxes in all the other instances where Active/All Members need to be selected. They are found in Organization, Committees, Events, Assets, and Agencies. As currently implemented, all the other instances work fine. It is only with the Members form that I have an issue, when adding new member records.

I put the Is_Active checkbox in the Manage SCATeam form, which is always open, so the checkbox would be global to all the other queries, forms and reports that need to refer to it.

[WCStarks]

That UDF is from Allen Brown, http://allenbrowne.com/tips.html I learned about this replacement for DLookUp() from another in the forum. It has an extra parameter which allows one to select, for example, the most current, in my case.

As I look at your AfterUpdate code, I am thinking that it may actually work for all my other uses, as it may be creating a virtual data set to work with. If so, that would greatly simplify these other contexts. I had tried to figure out how to make a virtual data set, such as in a Query which would be either active or all. But I could not figure out how to do that so it would work in all the other contexts. If this is what it does, then it is truly an elegant solution.

[davegri]

As far as having a global Is_Active variable you could do just that. Define a global variable that the checkbox after_update would set to true or false.
Then all the forms with members as recordsource would need to have something like
Select * from Members where Status = Is_ActiveGlobal. Instead of a global you could use a tempvar.
Also, all the comboboxes that select a member would need to have the rowsources modified to check the global.
In some cases, you don't want a combobox to show an inactive member regardless of the global so criteria would be Where Members.Status = True

Change the ManageSCATeam code to this so that at BOJ it honors the Active checkbox.

Option Compare Database
Option Explicit


Private Sub Form_Load()
    Call Is_Active_AfterUpdate
End Sub


Private Sub Is_Active_AfterUpdate()
    Select Case Is_Active
        Case True
            Me.RecordSource = "select * from members where Active = true"
        Case False
            Me.RecordSource = "members"
    End Select
 End Sub

Then the SCATeam form_current needs to change to this, as the textboxes are unbound and will keep any previous value going from record to record unless explicitly changed. So for new records the previous value was shown. Code fixes that.

Private Sub Form_Current()    
    If Me.NewRecord Then
        Me.Joined = vbNullString
        Me.Resigned = vbNullString
        Exit Sub
    End If
    Me.Joined = ELookup("[Status_Date]", "Membership", "[Status] = ""Joined"" and [Member_ID] =" & [ID], "[Status_Date] Desc")
    Me.Resigned = ELookup("[Status_Date]", "Membership", "([Status] = ""Resigned"" or [Status] = ""Terminated"" or [Status] = ""Emeritus"") and [Member_ID] =" & [ID], "[Status_Date] Desc")
End Sub

[WCStarks]

Please excuse my ignorance. As I try to wrap my mind around what you have provided, I have a few questions:
1) What does BOJ mean?
2) Is that RecordSource as referred to in the ManageSCATeam form available to the subforms, Members, Events, Assets, etc.? I am not sure how I would reference it elsewhere. Do I repeat that code in each ComboBox which selects members?
3) You say using the phrase "Select * from Members where Status = Is_ActiveGlobal" elsewhere when referencing the Members RecordSource. But doesn't that just select either Active members, or InActive members, rather than Active members or All members, as the Case statement does?
4) How do my reports that need to be sensitive to Is_Active relate to this?
5) I don't understand why my Joined and Resigned controls need to be unbound. I would like them to store into the members' profiles, so I don't have to do ELookUp()s everywhere I need to show their values. Is there something fundamentally wrong with doing that?
I appreciate your patience.