Changing RecordSource in AfterInsert Event

[mistera]

I have a situation where I allow users to filter data on a single form. If they choose to add a new record, if the data entered in the new record does not match the filter criteria, I want to remove the filter and then go to the newly created record.

I added code to the AfterInsert event procedure to use the RecordsetClone and Bookmark combination, but when the procedure ends, it does not display the correct record. As I step through the code, it seems to work just fine – at least right up until the End Sub. Then the record displayed changes.

Here is an example: My recordset for the form includes 50 records. When a filter is applied, the underlying recordset changes and the record count is reduced to 5 records. If I add a record matching the filter criteria, everything is good and now the record count is 6 records.

However, if the record being added does not match the filter criteria, the code in the AfterInsert procedure saves the key of the record just added, updates the form’s RecordSource to remove the filter, clones the recordset, finds the record just added, and bookmarks it. The recordset now has 51 records and the newly created record happens to be the 45th record. However, when the routine ends, the 6th record is displayed (where the new record was before the RecordSource changed to remove the filter).

Here is a snippet of the code from the AfterInsert routine after a check against the filter criteria:

[SIZE=3][FONT=Calibri]    myProject = Me.Project_Name[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]    Me.RecordSource = "SELECT tbl_Project.* FROM tbl_Project ORDER BY tbl_Project.[Project Name];"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Set rs = Me.RecordsetClone[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    With rs[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]        .FindFirst "[Project Name]='" & myProject & "'"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]        If .NoMatch Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]            MsgBox "The new record could not be found.  Please contact the database administrator.", vbCritical, "Critical Error"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]            GoTo Exit_Form_AfterInsert[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]        Else[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]            Me.Bookmark = .Bookmark[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]        End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]        .Close[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    End With[/FONT][/SIZE]

How can I get this to work right?

 

[boblarson]

After Insert isn't what you want, I believe. I think you want the form's AFTER UPDATE event instead.

 

[mistera]

Thanks for the suggestion, but that yields the same result. The record I want to be displayed is actually displayed upon completion of the AfterUpdate as you suggested. However, I have other code in the AfterInsert procedure which fires after the AfterUpdate. Again, once the AfterInsert procedure completes, I have the same issue as first reported.

I can't figure out any way around this! Any other suggestions???

 

[jdraw]

I think Bob was suggesting you remove After Insert and use After Update, not both.

 

[mistera]

I thought that also, but I need to have some code run in the AfterInsert procedure.

However, just for kicks, I did remove the AfterInsert procedure just to see what would happen and the issue still remains. I think it is just a result of adding a new record and trying to go to a new "location" within the recordset. While it does go there, when the entire insert procedure completes, it wants to be on the "location" where the insert occurred.

I don't know what else I can do and I'm very frustrated. Any other ideas?

 

[jdraw]

Perhaps you can show us an example of what you are trying to do with some sample data.
And show us what is happening and what you would like to happen.

Or perhaps you could post a copy of your db without any confidential info.

I have Acc2003 so mdb format is required.
If you post accdb others may be able to help.

 

[mistera]

I'm using Access 2007. It would be easier to show you what is going on. Please see the attached PDF with screen prints and information describing what is happening.

HELP!!

 

[delikedi]

I would try adding these two lines to the code you have included in your first post. If I'm not mistaken, the line that synchronizes the bookmarks between the clone and the form passes the bookmark of the filtered recordset.

Me.RecordSource = "SELECT tbl_Project.* FROM tbl_Project ORDER BY tbl_Project.[Project Name];"
[COLOR=Red]Me.FilterOn=false
Me.requery    [/COLOR]
Set rs = Me.RecordsetClone
    With rs

 

[boblarson]

I'm using Access 2007. It would be easier to show you what is going on. Please see the attached PDF with screen prints and information describing what is happening.

HELP!!

your screenshot shows me that I believe you need to use the After Update event to first capture the PK of the record into a variable, then clear the filters and then move to the record which matches the PK of the record. Don't use the Project name and don't use the After Insert event. Nothing has yet been saved in the After Insert event.

So, like this:

Private Sub Form_AfterUpdate()
   Dim lngID As Long
   Dim rst As DAO.Recordset

   lngID = Me.IDFieldNameHere
   Me.Filter = vbNullString
   Me.FilterOn = False

   Set rst = Me.RecordsetClone
   With rst
     .FindFirst "[PKFieldNameHere] = " & lngID
   If .No Match Then  
                  MsgBox "The new record could not be found.  Please contact the database administrator.", vbCritical, "Critical Error"
   Else
       Me.Bookmark = .Bookmark
   End If

   rst.Close
   Set rst = Nothing

End Sub

 

[mistera]

I have tried all of your suggestions and still have the same result. No matter where I put the code to bookmark the record I want to display when finished or what filters are applied (or not applied), the final displayed record position is still the same.

When I step through the code, the AfterUpdate procedure fires before the AfterInsert procedure. However, that really doesn't matter because in all cases as soon as the final line of code completes before turning the system back to the user, it goes to the position it was in when the insert was completed -- in my example that is the 6th record -- even if that record is no longer the record I just added.

I'm wondering if there is a system variable in one of the system tables that stores the position of the record and uses that to get back to it after the routine is done. Does anyone know if this could be the problem and if so, what is that variable and can it be changed through code?

 

[boblarson]

Post a copy of your database with bogus data if you can so we can have a stab at it.

 

[mistera]

My database is way too big and complex to attach. However, I was able to create a very simple database with a single table and a single form where I recreated the issue. I've attached this simplified database in accdb format. Let me know if you need it in mdb format.

You will see that I only have 5 records in Table1. One of the fields in the table is "Base Year". Some of the records have Base Year = 2011 and others have Base Year = 2012.

Form1 is based on Table1 and includes a filter for Base Year = 2012 when it opens. I've also included two buttons to either filter by 2011 or 2012.

While filtered for 2012, try adding a record for 2011. You will see that when the message saying that filters are being removed, the record just added is shown. However, when you click OK on the message, the routine finishes and a different record is displayed.

Let me know what you think about this.

 

[bob fitz]

Let me know if you need it in mdb format.

I would like that.

 

[boblarson]

My database is way too big and complex to attach. However, I was able to create a very simple database with a single table and a single form where I recreated the issue. I've attached this simplified database in accdb format. Let me know if you need it in mdb format.

You will see that I only have 5 records in Table1. One of the fields in the table is "Base Year". Some of the records have Base Year = 2011 and others have Base Year = 2012.

Form1 is based on Table1 and includes a filter for Base Year = 2012 when it opens. I've also included two buttons to either filter by 2011 or 2012.

While filtered for 2012, try adding a record for 2011. You will see that when the message saying that filters are being removed, the record just added is shown. However, when you click OK on the message, the routine finishes and a different record is displayed.

Let me know what you think about this.

What I think about it is that I'm a bit PERTURBED. You just don't like to take advice do you? I gave you the answer and you ignored it. I rewrote your code for you and you didn't use it. Because I just used this code below and it worked like a charm, exactly like you have been describing how you wanted. it. Can you tell I'm a bit mad?

Private Sub Form_AfterUpdate()
  Dim lngID As Long
 
 
    If Me.Base_Year <> Mid(Me.Filter, 13, 4) Then
[B][COLOR=red]   lngID = Me.ID[/COLOR][/B]
[B][COLOR=red]   Me.Filter = vbNullString[/COLOR][/B]
[B][COLOR=red]    Me.FilterOn = False[/COLOR][/B]
        Set rs = Me.RecordsetClone
        With rs
            .FindFirst [B][COLOR=red]"[ID]=" & lngID[/COLOR][/B]
            If .NoMatch Then
                MsgBox "The new record could not be found.  Please contact the database administrator.", vbCritical, "Critical Error"
                GoTo Exit_Form_AfterUpdate
            Else
                Me.Bookmark = .Bookmark
            End If
            .Close
        End With
 
        MsgBox "Filters have been cleared since new project did not meet filter criteria.", vbInformation, "Filter(s) Removed"
 
    End If
Exit_Form_AfterUpdate:
    Exit Sub
 
Error_Form_AfterUpdate:
    MsgBox "Error occurred."
    GoTo Exit_Form_AfterUpdate
End Sub

Notice, I saved the ID FIRST into a variable. THEN I removed the filters and THEN I used the ID field to search for the item using my saved variable.

So basically the same code I gave you in post #10 but with the specific ID field which I didn't know the name of it at the time.

 

[mistera]

OOPS!! I am sooo sorry! I inadvertantly posted the wrong code. I don't blame you for being perturbed. I would have been too!

Please know that I did use your suggestion in a different version of my database and it didn't work for me. In fact, the version you attached in your last post didn't work for me either.


That leads me to believe that there is something different between your Access settings and mine. Could this have something to do with different references on our machines? I have the following:

• Visual Basic for Applications
• Microsoft Access 12.0 Object Library
• OLE Automation
• Microsoft Office 12.0 Access database engine Object library

Could this be the issue? Please accept my apolgies again and know that your support has really helped me to learn some things I didn't already know.

 

[boblarson]

Same settings. And not sure why it would work for me (I attached the working database) and it wouldn't work for you.

 

[mistera]

I'm posting a copy of Bob's database in mdb format for those of you with older versions of Access. Does this work for you? As mentioned in my last post, it doesn't work for me but it does for Bob. Does anyone else know why it would work for one person and not another?

Using this database, I am filtered on 2012 projects. When I add a new project called "Cat" with 2011 in the base year, after I get the message that filters will be cleared, which runs after the bookmark, I can see that I'm on the newly added record. However, when I click OK, I'm now on the project called "Dog" with ID=2 showing record 4 of 8 at the bottom.

This problem is just nagging at me and I'm stumped!

 

[bob fitz]

It does not seem to work for me in A2003 but it does with A2007 Runtime.

 

[bob fitz]

If you remove the setting of the Order By property, it seems to work in A2003 also.

 

[mistera]

I still can't get it to work! I tried using the mdb file and also the accdb file. I also converted the accdb file to accdr to run it as a runtime version. I removed the Order By property setting in all versions and it still won't work. What's going on here???