Problems with combo box and data entry form

[Alex Wisnoski]

Access 97SR2-I am trying to create a Job Position data entry form
based on a table. The form has 15 fields on it. I want to use a
combo box to look in the table and see if the position number already
exists. If the position exists, then pull up that record and display
the information on the form. If the position doesn't exist, inform
the user and ask if they want to add it. If they elect to add it,
then have a new record to add it in to. My form has Save, Delete,
Clear, and Close buttons. I have tried having my combo box both bound
and unbound but have not been able to get everything to work yet.

Current errors/problems:

-I am getting a "Run-time error 2105: You can't go to the specified
record. You may be at the end of a recordset." on the code
"Me.Bookmark = rst.Bookmark 'RecordsetClone.Bookmark" when I try to
look up a record on an existing 'Position Number'.
-Sometimes my existing records (in the table) are getting overwritten
Here is my current code:
Private Sub cmdCloseForm_Click()
DoCmd.Close
End Sub

Private Sub cmdSaveRecord_Click()
blnSave = -1
DoCmd.RunCommand acCmdSaveRecord
blnSave = 0
End Sub

Private Sub cmdDelete_Click()
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End Sub

Private Sub cmdClear_Click()
If Me.Dirty Then 'Undo changes only if record is dirty
DoCmd.RunCommand acCmdUndo
End If
ClearCtrls Me 'function to clear all fields and avoid confusing
user

Me.cboPositionNumber.SetFocus
cboPositionNumber.Value = ""
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not blnSave Then
Cancel = True
DoCmd.RunCommand acCmdUndo
End If
End Sub

Private Sub Form_Current()
blnSave = False
End Sub
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2169 Then Response = acDataErrContinue
End Sub

Private Sub Form_Load()
ClearCtrls Me
End Sub

Sub cboPositionNumber_AfterUpdate()
' Find the record that matches the control.
Dim rst As Recordset, strTemp As String
Set rst = Me.RecordsetClone
rst.FindFirst "[PositionNumber] = '" & Me![cboPositionNumber] & "'"
If rst.NoMatch Then ' New value
strTemp = Me![cboPositionNumber]
If MsgBox(strTemp & " does not exist. Do you want to add it?",
vbYesNo) = vbYes Then
rst.AddNew
rst![PositionNumber] = strTemp
rst.Update
rst.Bookmark = rst.LastModified
Else
Me.Undo
DoCmd.GoToRecord , , acFirst
End If
Else ' Existing value
Me.Bookmark = rst.Bookmark 'RecordsetClone.Bookmark
End If
rst.Close
End Sub
Any assistance and/or suggestions on this would be greatly
appreciated.
Thanks,
Alex

[Pieter Linden]

Al***********@ncmail.net (Alex Wisnoski) wrote in message news:<5c**************************@posting.google. com>...

Access 97SR2-I am trying to create a Job Position data entry form
based on a table. The form has 15 fields on it. I want to use a
combo box to look in the table and see if the position number already
exists. If the position exists, then pull up that record and display
the information on the form. If the position doesn't exist, inform
the user and ask if they want to add it. If they elect to add it,
then have a new record to add it in to. My form has Save, Delete,
Clear, and Close buttons. I have tried having my combo box both bound
and unbound but have not been able to get everything to work yet.

Try this:

Private Sub Reason_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf
& vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the
current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to
re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblReasons", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Reason = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

rs.Close
Set rs = Nothing
End Sub

'************ Code Start **********
' This code was originally written by Dev Ashish. ' It is not to be
altered or distributed,' except as part of an application. ' You are
free to use it in any application, ' provided the copyright notice is
left unchanged.'' Code Courtesy of ' Dev Ashish'
Private Sub cbxAEName_NotInList(NewData As String, Response As
Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf
& vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the
current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to
re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************
from www.mvps.org/Access