Finding the new Record

[laura]

I need my new records, once entered, to slot into their alphabetical
position in the form, not at the END of the form/table. If I enter a record
and then "Me.Requery", the record pointer goes to the first record, not the
record I just entered. I've been struggling with me.recrodsetclone.bookmark,
but results are inconsistent - it works if I click my save button, but NOT
if I click on the pencil icon on the top right of the form.

TABLE with 3 fields

RecNo (autonumber)
fname (text)
sname (text)

ENTRY FORM form sorted in order of Surname
RecNo (visible but not enabled)
Firt Name:
Surname:

BUTTON which I created which just saves the record:-
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

In the AfterUpdate() procedure:- (which happens whether I click on my button
or on the pencil icon):-

Dim rst As DAO.Recordset
Dim iRec As Integer
iRec = Me.RecID ' it picks up the right RecNo whether I click on the
buton or pencil icon
Set rst = Me.RecordsetClone
Me.Requery ' to put the records into the right alphabetical order
rst.FindFirst "RecID = " & iRec ' to find my most recently entered
record (nomatch is false, so it DOES find the record)
Me.Bookmark = rst.Bookmark ' I need the record to be slotted in
alphabetically and showing.
rst.Close

As I said, this works a treat when I click the button, but NOT when I click
the pencil icon... why??? arghhh.

LauraTD

 

[Allen Browne]

Try this in the After Insert event procedure of the *form* itself:

Private Sub Form_AfterInsert()
Dim rs As DAO.Recordset
Dim strWhere As String

strWhere = "RecNo = " & Me.RecNo
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End Sub

Post back if you are using Access 97 or earlier: there's a bug you may need
to avoid.

 

[laura]

It didn't work, same result - i.e., clicking the button works a treat, but
clicking the pencil puts the record pointer on the right record , but at the
end of the database, not in it's alphabetical position.

Small apology, my field is called RecID (not RecNo).. I mixed them up in my
exmaple.. so sorry, but nevertheless it still does not work, unfortunately.

I'm using Access 2000 (Windows XP)

What actually happens when you click on the pencil ID? I tried to pause and
see step by step, but it runs immediately to the AfterUpdate() event.
Strange how it works one way, but not the other.

LauraTD

 

[Allen Browne]

What button?

I suggested using an event of the *form*.

You cannot move to the new record until it is saved.
The code I posted does so as soon as it is saved.

If you want your button to force the save, use this code:
If Me.Dirty Then RunCommand acCmdSaveRecord

 

[laura]

correction:

clicking the pencil puts the record pointer on the right record , but at
the end of the database, not in it's alphabetical position.

sorry.. it puts the record pointer on the last record (alphabetically) and
the newly entered record is it's correct position, but I want to be on that
record when I finished entering it, not the last record.

LauraTD

It didn't work, same result - i.e., clicking the button works a treat, but
clicking the pencil puts the record pointer on the right record , but at
the end of the database, not in it's alphabetical position.

Small apology, my field is called RecID (not RecNo).. I mixed them up in
my exmaple.. so sorry, but nevertheless it still does not work,
unfortunately.

I'm using Access 2000 (Windows XP)

What actually happens when you click on the pencil ID? I tried to pause
and see step by step, but it runs immediately to the AfterUpdate() event.
Strange how it works one way, but not the other.

LauraTD

 

[laura]

Basically, I put the button there to experiment as clicking on the pencil
did not do what I need, which is to add a new record, have it slot into the
right place alphabetically and then be displayed. At the moment, if I click
on the pencil icon after entering a new record, the record is in the right
place alphabetically, but I'm presented with the last alphabetical record,
i.e., the bottom of the table. All my button does is run " DoCmd.DoMenuItem
acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70", then the program
runs through the AfterUpdate() event and the record is correctly
positioned - but when I simply click on the pencil, (which is what I need)
it isn't.

I tried adding "If Me.Dirty Then RunCommand acCmdSaveRecord" after Dim
strWhere As String and it still didn't work.

What actually happens when you click on the pencil?

Thanks for your patience.
LauraTD

 

[Allen Browne]

Clicking the pencil, saves the record, and selects it.

This messes up the code that goes to the new record.

You can demonstrate that by adding this as the first line in the form's
AfterInsert event procedure:
Stop

Now when you click on the pencil, Access shows the code window, with the
Stop highlighted in yellow. Size/position the code window so you can watch
what's happening in the form, as the code runs. Then press F8 repeatedly to
step through the code, one line at a time. You will see that Access does
requery the form, then goes to the correct record. Then when the code ends,
it loses the position and goes to the wrong place again.

This is a mistake in Access itself, after the code completes, so you can't
change the way it behaves.

 

[laura]

"This is a mistake in Access itself, after the code completes, so you can't
change the way it behaves."

I appreciate that, so I need to find a way to get to my record.

I've got a database with about 9000 records in it sorted primarily by
Airline name (it's an Airline Insurance database) and then by Inception
Date. Each Airline is grouped according to their Inception date, so you skip
from the latest through to the most recent record, then on to the next
Airline (alphabetically) and so on. When the user enters a new record for an
existing Airline with a new/current Inception Date, they don't want to be at
the bottom of the table looking at Zuliana Airlines, they want to be ON the
record they just entered and then if they scroll one record back, to be on
the previous year/Inception Date for that Airline.

There must be some way I can write a code that finds the correct record,
AFTER the pencil has been clicked on. I don't want to change the way Access
works, just to work around it. I've been stuck on this for a couple of days.
I guess I might be going about it in the wrong way.

Thanks
LauraTD

 

[Allen Browne]

An alternative might be to filter the form so that it contains only the one
record.

 

[laura]

Thanks Allen for your help. That won't do.. they need to see the whole
database and to have the record in the right place when they enter it. Hard
to believe this is so difficult to do. Maybe I can have a refresh button or
something, but I will have to capture the newly entered Record Number and
pass it on, then find the record. The odd thing is that it works when I save
the record with my "save" button, even though it passes through the same
event, "AfterUpdate(), but when I click on the pencil, which also takes me
through AfterUpdate() nothing happens. Boohoo...

Laura