How to select last record in a subform?

[Guest]

Hello,

I have a subform that displays records in a table (ParentBookID), in
Datasheet view.
User can edit/add records directly in the subform.
I want to make sure user doesn't enter a duplicate record.
So in the subform's AfterUpdate event, I have the below code:
-------------------------------------------------------
Private Sub Form_AfterUpdate()
Dim db As Database
Dim aSQL As String
Dim aRST As Recordset

Set db = CurrentDb
aSQL = "SELECT First(ParentBookID.ParentBookID) AS [ParentBookID Field] " _
& "FROM ParentBookID " _
& "GROUP BY ParentBookID.ParentBookID " _
& "HAVING (((Count(ParentBookID.ParentBookID))>1));"
Set aRST = db.OpenRecordset(aSQL, dbOpenDynaset)
If aRST.RecordCount > 0 Then
MsgBox "DUPLICATE ENTRY!"
End If
End Sub
-------------------------------------------------------

The code is running fine, able to give the message. But after the message,
I want the duplicated entry the user just added in the subform be selected.
How can I do that?

THANKS!

 

[Scott McDaniel]

Hello,

I have a subform that displays records in a table (ParentBookID), in
Datasheet view.
User can edit/add records directly in the subform.
I want to make sure user doesn't enter a duplicate record.

The code is running fine, able to give the message. But after the message,
I want the duplicated entry the user just added in the subform be selected.
How can I do that?

That depends on how your records are Sorted. You can more to the last record like this:

DoCmd.RunCommand accmdRecordsGoToLast

however there is no guarantee that the last record will always be your "new" record.

Better, in my opinion, to catch this before the record is actually inserted into the database. You can use several
methods; you might try using the subform's BeforeInsert event to test for the duplicate and, if found, Cancel the
insert:

Sub YourForm_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SELECT * FROM YourTable WHERE SomeField='" & Me.SomeTextbox & "' AND
SomeOtherField='" & SomeOtherTextbox & "")"

If Not(rst.EOF and rst.BOF) Then
'/a record was found
Msgbox "The data you are trying to enter is a duplicate of an existing record.", vbOkOnly + vbExclamation)
Cancel = True
End If

End Sub

You'd need to alter the SELECT statement to look for the data in the proper table columns, of course, and change the
table/column/control names to match those in your application.

THANKS!

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Guest]

Thanks Scott for the hint. I also think it makes more sense to confirm the
dup before the record is inserted, but I didn't know how to do it.
I tried the RecordsGoToLast method, and it gave me a message that this
method is not available. I also tried to use your code in BeforeInsert, and
because BeforeInsert is triggered the moment you type the first character, it
pop up the msg already even I enter anything.
So I used your idea in BeforeUpdate event instead and it works. Below is my
final code:
---------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As Recordset

'to confirm with user adding/editing a record is he/she wants to do
If MsgBox("You have added/updated a record, do you want to save the
change?", vbYesNo, "Change Confirmation") = vbNo Then
Cancel = True
Me.Undo
Exit Sub
Else
'check for duplicated record before inserting to table
Set rst = CurrentDb.OpenRecordset("SELECT ParentBookID.ParentBookID FROM
ParentBookID;")
If Not (rst.EOF And rst.BOF) Then
'/a record was found
MsgBox "The data you are trying to enter is a duplicate of an
existing record.", vbOKOnly + vbExclamation
Cancel = True
End If
End If
End Sub
-----------------------------------------------
It is doing exactly what I want it to do now, and the new record is still
in-focus after the "duplicate" message.

Thank you!!!

SW

 

[Scott McDaniel]

Thanks Scott for the hint. I also think it makes more sense to confirm the
dup before the record is inserted, but I didn't know how to do it.
I tried the RecordsGoToLast method, and it gave me a message that this
method is not available. I also tried to use your code in BeforeInsert, and
because BeforeInsert is triggered the moment you type the first character, it
pop up the msg already even I enter anything.

I forgot about that re: Before Insert. Glad you got this working!

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com