Requery form ONLY if NEW record is created and not just updated

[Danick]

I have a subform displaying records in a datasheet. On the main form I have a button that will open a form for data entry. I can put some VBA so that when this data entry form closes, it will requery the subform.

Forms![MainForm]![SubformName].Form.Requery

However, I also have a doubleclick event on the subform that will open this same data entry form to make it easier to update or modify those records in the subform. The problem is, when you close the data entry form, it will requery the subform which will reset the position and you have to scroll to find the record you were updating.

As a workaround, I removed the code from the data entry form close event and just added a button with this requery code to the main form.

So my question is, is there a way to update a subform form ONLY when a new record is created and NOT if the record is simply updated?

 

[RuralGuy]

You could count the records in the recordset first.

 

[Danick]

You could count the records in the recordset first.

Well I couldn't figure out how to do that. So I tried to use a bookmark approach I found on another thread, but that wasn't working very well either.

So instead of trying to get the VBA to work, I kind of copped out and just created a duplicate form. So now if the "New Record" button is pressed, it opens the original form with the close event to requery and if the user just doubleclicks the line to edit it, then a new form opens that is an exact duplicate but without the closing requery event.

It isn't pretty and I'll have to maintain two forms, but at least it's doing what I want it to do.

 

[RuralGuy]

Me.Recordset.Clone.Movelast
YourVariable = Me.Recordset.Clone.Recordcount
...will put the RecordCount in "YourVariable" if you put it in the OnLoad event of your SubForm.
NewVariable = DCount("*","YourTable")
...will put the current recordcount in "NewVariable" for comparison.
You could also use Me.Recordset.Clone.Movelast
...and
NewVariable = Me.Recordset.Clone.Recordcount

 

[RuralGuy]

Anything except trying to maintain two forms is worth the effort. Please just ask for more details from the helpers. There are a lot of us that know code real well.

 

[RuralGuy]

Here's some code to stay on the same record after a Requery: http://www.baldyweb.com/Requery.htm

 

[RuralGuy]

When we reply to a post we have no idea of the skill level of the poster. Rather than assume a low skill level I tend to provide an answer in my usual fashion and see if it makes any sense to the poster. Just say "Can you say that in smaller words" and we'll adjust.

 

[Danick]

Thanks for all your efforts on this. Sorry haven't responded sooner - I do not tend to go back to my posts when I get it the issue working.

But the next time I have to change something on that form, I'll come back to your suggestions and see if I can make it work with just one form. Just hate trying to do that now. As the saying goes, "If it ain't broke..."

Thanks again.

 

[jdraw]

I do not tend to go back to my posts when I get it the issue working.

If you have posted a question, and someone(s) has responded with advice/suggestion/comment, your posting back:
- a solution if you found one, or
- a link to additional info that may be relevant
- additional questions if the issue is unresolved
- comments regarding the relevance of the info provided

all help other posters who may have similar or related issues. It also provides some feedback to those who have tried to assist you.

 

[Danick]

If you have posted a question, and someone(s) has responded with advice/suggestion/comment, your posting back:
- a solution if you found one, or
- a link to additional info that may be relevant
- additional questions if the issue is unresolved
- comments regarding the relevance of the info provided

all help other posters who may have similar or related issues. It also provides some feedback to those who have tried to assist you.

This is basically what I did on my third post when I posted back with my solution and considered the matter closed. But when I came back and saw more suggestions, I thought of at least acknowledging the extra effort that RuralGuy was putting into it.