|
Subject:
|
DoCmd.GoToRecord , , acNewRec doesnt create new re
|
|
Posted By:
|
david_bridges
|
Post Date:
|
7/22/2006 8:07:18 AM
|
Hi,
I'm having a problem when using DoCmd.GoToRecord , , acNewRec in the following code.
It doesn't create a new record. Instead, it changes the current record in the recordset.
What i really want to do is create a new record.
Private Sub Engine2_Click()
On Error GoTo ErrorHandler
Dim strSearch As String
Dim intAC_ID As Integer
MsgBox "In engine 2 click"
'For text IDs
'strSearch = "[______ID] = " & Chr$(34) & Me![cboSelect] & Chr$(34)
'For numeric IDs
'strSearch = "[______ID] = " & Me![cboSelect]
strSearch = "[AE_AC_ID] = " & Me![cmbAE_AC_ID] & _
" AND [AE_E_NO] = 2 "
intAC_ID = Me![cmbAE_AC_ID]
'Find the record that matches the control
Me.RecordsetClone.FindFirst strSearch
If (Me.RecordsetClone.NoMatch = True) Then
MsgBox "couldn't find a match creating new record"
DoCmd.GoToRecord , , acNewRec
txtAE_E_NO = 2
cmbAE_AC_ID = intAC_ID
Me![cmbEngine2] = Null
MsgBox "creating New record cmbAE_AC_ID = " & Me![cmbAE_AC_ID]
Else
MsgBox " Found: Bookmarking???"
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
|
|
Reply By:
|
wscheiman
|
Reply Date:
|
8/21/2006 6:03:10 AM
|
Hi David,
When you need to do something like adding a new record while you're working in a bound form, what you want to do is create the new record in the "background". If you need to position to the new record, use bookmarking once the record has been added.
Here's the excerpt I noticed:
Me.RecordsetClone.FindFirst strSearch
If (Me.RecordsetClone.NoMatch = True) Then
MsgBox "couldn't find a match creating new record"
DoCmd.GoToRecord , , acNewRec
txtAE_E_NO = 2
cmbAE_AC_ID = intAC_ID
Me![cmbEngine2] = Null
MsgBox "creating New record cmbAE_AC_ID = " & Me![cmbAE_AC_ID]
Else
MsgBox " Found: Bookmarking???"
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
Assuming a table name of "tblEngine" (for example purposes only, this is what I'd do:
#1 Declare a string for a SQL statement
Dim strSQL As String
#2 Modify the section for the .FindFirst logic - something like this:
Me.RecordsetClone.FindFirst strSearch
If (Me.RecordsetClone.NoMatch = True) Then
MsgBox "couldn't find a match creating new record"
Me![cmbEngine2] = Null
MsgBox "creating New record cmbAE_AC_ID = " & Me![cmbAE_AC_ID]
strSQL = ""
strSQL = strSQL & "INSERT INTO tblEngine "
strSQL = strSQL & "("
strSQL = strSQL & "AE_E_NO, "
strSQL = strSQL & "AE_CD_ID "
strSQL = strSQL & ")"
strSQL = strSQL & " VALUES "
strSQL = strSQL & "("
strSQL = strSQL & "2, "
strSQL = strSQL & intAC_ID
strSQL = strSQL & ")"
CurrentProject.Connection.Execute (strSQL)
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox " Found: Bookmarking???"
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
I'm kind of guessing with the column names, but this should hopefully give a better idea.
Hope it helps.
|
|
