How to open a form with a particular record showing?

[Don Seckler]

I have a form with a button. When the fields are filled in and the
button is clicked, the code below checks to see if a record with the
same data already exists. If it does the error kicks in and the pop
up comes up saying that the record already exists, would you like to
edit the existing record.

Next I have "editForm" opening, so the user can edit the record. How
do I get the record that triggered the error to be the active one in
"editForm" when it opens?



Private Sub ButtonAddDraw_Click()
On Error GoTo Err_ButtonAddDraw_Click


DoCmd.GoToRecord , , acNewRec

Exit_ButtonAddDraw_Click:
Exit Sub

Err_ButtonAddDraw_Click:
Select Case Err.Number
Case 2105
Err.Clear
'* Display my custom message box
If MsgBox("The draw has already been entered for this issue.
Would you like to edit it?", vbYesNo) = vbYes Then
DoCmd.OpenForm "EditForm"
Else
End If
GoTo Exit_ButtonAddDraw_Click
Case Else
MsgBox Err.Description
End Select
Resume Exit_ButtonAddDraw_Click

 

[Fletcher Arnold]

I have a form with a button. When the fields are filled in and the
button is clicked, the code below checks to see if a record with the
same data already exists. If it does the error kicks in and the pop
up comes up saying that the record already exists, would you like to
edit the existing record.

Next I have "editForm" opening, so the user can edit the record. How
do I get the record that triggered the error to be the active one in
"editForm" when it opens?



Private Sub ButtonAddDraw_Click()
On Error GoTo Err_ButtonAddDraw_Click


DoCmd.GoToRecord , , acNewRec

Exit_ButtonAddDraw_Click:
Exit Sub

Err_ButtonAddDraw_Click:
Select Case Err.Number
Case 2105
Err.Clear
'* Display my custom message box
If MsgBox("The draw has already been entered for this issue.
Would you like to edit it?", vbYesNo) = vbYes Then
DoCmd.OpenForm "EditForm"
Else
End If
GoTo Exit_ButtonAddDraw_Click
Case Else
MsgBox Err.Description
End Select
Resume Exit_ButtonAddDraw_Click

The key to this lies in getting the primary of the existing record, but
since we don't know much about the tables or what might define a duplicate,
it's hard to say.

If what defined a 'duplicate draw' was a DrawName and this was all that was
required to create a new record, then my AddNew button let the user enter
the name, then try to find it using perhaps the form's recordsetclone. You
may need to add more information than just one field, but you need to make
sure you don't let the user fill in a whole new record and then tell him -
sorry this record already exists. (that is how I imagine your scheme
currently works)

Fletcher

 

[Paul]

Hi Don,

You can probabley adapt my solution to your situation.

I needed to open a form at a known ID but I didnt know the record
number. So I set a clone of my recordset and loop through it until I
find my ID (while counting each loop) then this is my record number to
use as the offset when opening the form using the DoCmd.GoToRecord

Code as follows -

Private Sub CmdNew_Click()
On Error GoTo CmdNew_ClickError

Dim db As Database
Dim rec As Recordset
Dim RecClone As Recordset
Dim strSQL As String
Dim strCo As String
Dim strResponce As String
Dim lngCustomerID As Long
Dim intI As Integer

'lngIntID is a public variable
lngIntID = Me.ID

'this bit returns the Co name
Set db = CurrentDb()
Set rec = db.OpenRecordset("SELECT tblCustomerContacts.ID,
tblCustomerContacts.FKtblCustomersID, TblCustomers.Name FROM
TblCustomers INNER JOIN tblCustomerContacts ON TblCustomers.ID =
tblCustomerContacts.FKtblCustomersID where (tblCustomerContacts.id) =
" & lngIntID)
strCo = rec("Name")
lngCustomerID = rec("FKtblCustomersID")
Set rec = Nothing
Set db = Nothing

strResponce = MsgBox("Is this a New contact for " & strCo & " ?",
vbYesNo)

If strResponce = vbNo Then
DoCmd.GoToRecord , , acNewRec
Else

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblCustomerContacts")
rec.AddNew
rec("FKtblCustomersID") = lngCustomerID
lngIntID = rec("ID")
rec("FKStatusCode") = 4
rec("FKActionCode") = 6
rec.Update
Set rec = Nothing
Set db = Nothing

End If

Me.Requery

Set RecClone = Me.RecordsetClone()

RecClone.MoveFirst

intI = 1

Do Until RecClone("ID") = lngIntID
RecClone.MoveNext
intI = intI + 1
Loop

DoCmd.GoToRecord acDataForm, "frmProspects", acGoTo, intI

Set RecClone = Nothing

errExit:
Exit Sub

CmdNew_ClickError:
'the error is logged B4 it is handled.
'Error logging
Call plcErrorLogger("Form frmProspectDetails Sub CmdNew_Click",
Err.Description, Err.Number)

'error handling
MsgBox "There has been an error. Inform technical support.",
vbInformation
Resume errExit

End Sub

Hope it helps,

Paul.

 

[Fletcher Arnold]

Set RecClone = Me.RecordsetClone()

RecClone.MoveFirst

intI = 1

Do Until RecClone("ID") = lngIntID
RecClone.MoveNext
intI = intI + 1
Loop

DoCmd.GoToRecord acDataForm, "frmProspects", acGoTo, intI

Set RecClone = Nothing

Any reason you don't use the "textbook standard" of .FindFirst followed by
checking .NoMatch as shown below?

With Me.RecordsetClone
.FindFirst "ID=" & lngIntID
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With


Fletcher