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.Bookmar k = 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_Cl ick()
DoCmd.Close
End Sub
Private Sub cmdSaveRecord_C lick()
blnSave = -1
DoCmd.RunComman d acCmdSaveRecord
blnSave = 0
End Sub
Private Sub cmdDelete_Click ()
DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 6, , acMenuVer70
End Sub
Private Sub cmdClear_Click( )
If Me.Dirty Then 'Undo changes only if record is dirty
DoCmd.RunComman d acCmdUndo
End If
ClearCtrls Me 'function to clear all fields and avoid confusing
user
Me.cboPositionN umber.SetFocus
cboPositionNumb er.Value = ""
End Sub
Private Sub Form_BeforeUpda te(Cancel As Integer)
If Not blnSave Then
Cancel = True
DoCmd.RunComman d acCmdUndo
End If
End Sub
Private Sub Form_Current()
blnSave = False
End Sub
Private Sub Form_Error(Data Err As Integer, Response As Integer)
If DataErr = 2169 Then Response = acDataErrContin ue
End Sub
Private Sub Form_Load()
ClearCtrls Me
End Sub
Sub cboPositionNumb er_AfterUpdate( )
' Find the record that matches the control.
Dim rst As Recordset, strTemp As String
Set rst = Me.RecordsetClo ne
rst.FindFirst "[PositionNumber] = '" & Me![cboPositionNumb er] & "'"
If rst.NoMatch Then ' New value
strTemp = Me![cboPositionNumb er]
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.LastModifie d
Else
Me.Undo
DoCmd.GoToRecor d , , 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