Form doesn't change records if record is newly added.

[Seth Schrock]

I have a problem that if I have a form open and a new record gets added by another user, I have to close my form before I can go to that record. My current setup is that I have a Home form that has a list of the active records and I have my Loan form that has all the details. When I select a record on my Home form, I use the following code to go to the selected record on my Loan form:

DoCmd.OpenForm "frmLoans"
DoCmd.SearchForRecord , , acFirst, "LoanID = " & LoanID

This works even if I leave my Loan form open and then go to my Home form and select an already existing loan. However, if I select a loan that was added after the Loan form was opened, the record doesn't move. My thinking is that the new record wasn't part of the query when the form was opened and therefore it doesn't see that record. My idea is to just add Forms!frmLoans.Requery between the above two lines so that the new record is available. However, I think that this will trigger the OnCurrent event to run twice; once for the requery and then again for the .SearchForRecord. Is there a better way to do this? I'm thinking something to do with the recordsource, but not sure what.

I hope I'm clear. It is a bit complicated to explain.

[TheSmileyCoder]

You are correct in your assumptions. While Access will periodically refresh the data collected, it will not collect new records, unless required.

Is there any reason why you don't open the frmLoans as a modal popup with a filter to the specified record? That way you retrieve less data, and the user has to close the form to select a new loan.

You could also use instanced forms if you require the ability to have multiple loans open at a time.

[zmbd]

The requery will more than likely trigger the on current as Me.Requery essentially reloads the form by re-running the query; thus, your current position is lost will change from where you are to the top of the sort order.
Order of events for database objects (v2010)
And then as you suspect the on current will fire again on the record movement... there's no way around that.

[Seth Schrock]

@Smiley I don't like having forms filtered on their PK value as it makes it complicated to navigate to another record in the same form. I currently have the filter set to get only the current user's records. This is fine since I can still navigate fairly easily through the records within the filter. However, I might play with instanced forms. I would need to come up with a way to count the number of forms open so that they don't get so many it start to degrade performance though.

@Z I figured that was the case with the Requery. Is there a way to SearchForRecord in Recordsource or something similar?

[TheSmileyCoder]

@Seth
I thought you were using the main form as a navigation form, and then frm_Loans as a edit/viewing form. Part of the approach of the instancing of forms involve a collection to place the forms in. So its somewhat easy getting a count of the forms open, since you can just count on the collection. There is a method in my crash reporter as well, to count the number of linked "connections" available (applicable if you are using linked tables (Access or SQL))

An easier approach might be to use the recordset of the form, and use the RecordSet.FindNext method, and check to see if RecordSet.NoMatch is true. If so, you can try a requery, and moving to record again.

[Seth Schrock]

I have used your easier approach for now because I can implement it quickly. When I get more time, I might look into the instanced forms.

You are correct that I was using the main form as a navigation form, but I also have navigation built into the header of the Loan form as well so that they can go to another loan without having to go back to the home form.