AddNewRecord - cancel ?

[ooops]

Hi,
When users add new record and the record already exist in the table, how do I cancel the AddNewRecord action and make the form navigate to the existed record ? Thanks.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM myTable WHERE ID = " & Chr(34) & Me![txtID] & Chr(34) & _
" AND Year = " & Chr(34) & Me![txtYear] & Chr(34)

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

With rs
If .BOF And .EOF Then
Me.AllowEdits = True
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Record has been saved
Else

MsgBox "Record already exists in the table."
??????????????????????????????????

Exit Sub
End if

 

[hneal98]

One way would be to store the ID of the current record, so when the opperation is canceled, you can do a search on that ID and return to that record.

 

[MakeItSo]

Another would be to make your own Add-button:
Set the Form's AllowAddition property to False,
and alter your code such that new records get inserted:
[blue]
Dim db As DAO.Database
Dim rs As DAO.Recordset,rs2 As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM myTable WHERE ID = " & Chr(34) & Me![txtID] & Chr(34) & _
" AND Year = " & Chr(34) & Me![txtYear] & Chr(34)

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL,dbOpenDynaset)

With rs
If .BOF And .EOF Then
rs.Edit
rs![ID]=Me!ID
rs!field2=Me!field2
...
rs.Update
MsgBox "Record has been saved
Else

MsgBox "Record already exists in the table."
rs.Close
Set rs=Me.RecordsetClone
rs.MoveFirst
rs.FindFirst "ID=" & Me!ID
Me.bookmark=rs2.bookmark
rs.Close
Exit Sub
End if
...
End Sub
[/blue]

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[ooops]

Thanks for your help. Could you please give me the code to cancell the operation ? I've had a hard time finding it. Thank you so much.

 

[MakeItSo]

Haven't tested it but
RunCommand acCmdUndo
should provide that.

Cheers,
MakeItSo

 

[MakeItSo]

P.S:
Dim str as String
str=Me!ID
....
and then in the Else part:

rs.Close
Set rs=Me.RecordsetClone
rs.MoveFirst
rs.FindFirst "ID=" & str
Me.bookmark=rs.bookmark
rs.Close

Should navigate you to the existing record. ;-)

 

[ooops]

Thank you all. I got it!