M
Mike Gerbi
Ok I have a form that will allow you to edit two different tables. The problem is when I go to add an employee, I cannot scroll through or move to a different record. Access gives me an error message "Run-Time Error 2105" You may be at the end of the recordset. I am scrolling or moving to different records using a combo box. I just used the wizard coding and it is unbound.
Me.RecordsetClone.FindFirst "[CertificationNumber] = " & Me![cmbSearch]
Me.Bookmark = rst.Bookmark
I even tried this and it gave me the same error message:
Dim rstEmployeesUpdate As Recordset, rst As Recordset
Dim dbs As Database
Set dbs = CurrentDb
Set rstEmployeesUpdate = dbs.OpenRecordset("tblEmployees", dbOpenDynaset, 0)
Set rst = Me.RecordsetClone
rstEmployeesUpdate.MoveFirst
rstEmployeesUpdate.FindFirst "[CertificationNumber] = " & Me![cmbSearch]
Me.Bookmark = rst.Bookmark
Button01
Now the following is what I use to clear the fields, this is the first step in adding a new record.
With txtEmployeeLastName
.Enabled = True
.SetFocus
.Text = ""
End With
With txtCertNum
.Enabled = True
.SetFocus
.Text = ""
End With
With cmbAppraiserTypeGeneral
.Enabled = True
.SetFocus
.Value = 1
End With
With txtEmployeeFirstName
.Enabled = True
.SetFocus
.Text = ""
End With
Button02
Next is after the data has been entered into the fields you click another button and the following occurs:
Private Sub cmdAddNew_Click()
Dim dbs As Database
Dim rstEmployees As Recordset
Dim rstAppraiserType As Recordset
Dim rstEmployeesClone As Recordset
Dim rstAppraiserTypeClone As Recordset
Dim LName As String, FName As String, CertNum As String
If txtEmployeeFirstName.Enabled = False And txtCertNum.Enabled = False Then
MsgBox "Click the Clear Fields Button First Before Adding a New Employee", vbInformation
End
End If
'--------------------------------------------------------------
txtEmployeeFirstName.SetFocus
FName = txtEmployeeFirstName.Text
txtEmployeeLastName.SetFocus
LName = txtEmployeeLastName.Text
txtCertNum.SetFocus
CertNum = txtCertNum.Text
Set dbs = CurrentDb
Set rstEmployees = dbs.OpenRecordset("tblEmployees", dbOpenDynaset)
Set rstAppraiserType = dbs.OpenRecordset("tblAppraiserTypeDate", dbOpenDynaset)
Set rstEmployeesClone = rstEmployees.Clone
Set rstAppraiserTypeClone = rstAppraiserType.Clone
'--------------------------------------------------------------
txtEmployeeFirstName.SetFocus
If Len(Trim$(txtEmployeeFirstName.Text)) = 0 Then
Response = MsgBox("Please Enter a Name to add", vbOKCancel)
If Response = vbCancel Then
Me![txtEmployeeFirstName].Value = Me![txtEmployeeFirstName].OldValue
Me![txtEmployeeLastName].Value = Me![txtEmployeeLastName].OldValue
Me!txtCertNum.Value = Me!txtCertNum.OldValue
Me!cmbAppraiserTypeGeneral.Value = Me!cmbAppraiserTypeGeneral.OldValue
Me!txtDateChanged.SetFocus
Me!txtCertNum.Enabled = False
Me![txtEmployeeLastName].Enabled = False
Me![txtEmployeeFirstName].Enabled = False
Me!cmbAppraiserTypeGeneral.Enabled = False
Else
Me!txtEmployeeFirstName.SetFocus
End If
End
End If
'--------------------------------------------------------------
If IsNull(Me![txtDateChanged].Value) Then
Response = MsgBox("Please Enter a Date", vbOKCancel)
If Response = vbCancel Then
Me!txtDateChanged.SetFocus
Me![txtEmployeeFirstName].Enabled = False
Me![txtEmployeeLastName].Enabled = False
Me!txtCertNum.Enabled = False
Me!cmbAppraiserTypeGeneral.Enabled = False
Else
Me!txtDateChanged.SetFocus
End If
End
End If
'--------------------------------------------------------------
AppraiserType = Me![cmbAppraiserTypeGeneral]
FirstName = Me![txtEmployeeFirstName]
LastName = Me![txtEmployeeLastName]
Cert = Me![txtCertNum]
DateCh = Me![txtDateChanged]
'--------------------------------------------------------------
'rstEmployeesClone
Do Until rstEmployees.EOF
If rstEmployees!EmployeeLastName = LName And rstEmployees!EmployeeFirstName = FName Then
MsgBox "That Employee Already Exists in the Database. ", vbCritical, "No Duplicates Allowed"
Me![cmbAppraiserTypeGeneral].SetFocus
Me![txtEmployeeFirstName].Enabled = True
Me![txtEmployeeLastName].Enabled = True
Me![txtEmployeeFirstName].SetFocus
Me![txtEmployeeFirstName].Text = ""
Me![txtEmployeeLastName].SetFocus
Me![txtEmployeeLastName].Text = ""
Me![txtEmployeeFirstName].SetFocus
Exit Sub
End If
rstEmployees.MoveNext
Loop
rstEmployees.MoveFirst
'--------------------------------------------------------------
'rstEmployeesClone
Do Until rstEmployees.EOF
If Cert = rstEmployees!CertificationNumber Then
MsgBox "That Certification Number Already Exists in the Database. ", vbCritical, "No Duplicates Allowed"
Me![txtCertNum].Enabled = True
Me![txtCertNum].SetFocus
Me![txtCertNum].Text = ""
rstEmployees.Close
Exit Sub
End If
rstEmployees.MoveNext
Loop
'--------------------------------------------------------------
With rstEmployeesClone
.AddNew
!EmployeeFirstName = FirstName
!EmployeeLastName = LastName
!CertificationNumber = Cert
!AppraiserTypeGeneralID = AppraiserType
.Update
.Close
End With
'---------------------------------------------------------------
With rstAppraiserTypeClone
.AddNew
!CertificationNumber = Cert
!ChangeDate = DateCh
!AppraiserTypeID = AppraiserType
.Update
.Close
End With
'--------------------------------------------------------------
Set rstAppraiserTypeClone = Nothing
Set rstEmployeesClone = Nothing
Me!cmbSearch.SetFocus
Me![txtEmployeeFirstName].Enabled = False
Me![txtEmployeeLastName].Enabled = False
Me![txtCertNum].Enabled = False
Me![cmbAppraiserTypeGeneral].Enabled = False
'--------------------------------------------------------------
MsgBox FirstName & " " & LastName & " has been added to the database.", vbInformation
cmbSearch.Requery
MEForm.Refresh
rstEmployees.Close
Set rstEmployees = Nothing
rstAppraiserType.Close
Set rstAppraiserType = Nothing
dbs.Close
Set dbs = Nothing
'DoCmd.OpenForm "frmMain", acNormal, , , , acWindowNormal
'DoCmd.Close acForm, "frmMainEmployeeForm", acSaveYes
End Sub
Variables that are defined in Declarations:
Dim MEForm As Form
Dim AppraiserType As Integer
Dim DateCh As Date
Dim FirstName As String, LastName As String
Dim Cert As Integer
Dim Response
Dim AppraiserTypeNum As Integer
Dim FldChangeDate As Field, FldAppType As Field
Dim Desg As String
Me.RecordsetClone.FindFirst "[CertificationNumber] = " & Me![cmbSearch]
Me.Bookmark = rst.Bookmark
I even tried this and it gave me the same error message:
Dim rstEmployeesUpdate As Recordset, rst As Recordset
Dim dbs As Database
Set dbs = CurrentDb
Set rstEmployeesUpdate = dbs.OpenRecordset("tblEmployees", dbOpenDynaset, 0)
Set rst = Me.RecordsetClone
rstEmployeesUpdate.MoveFirst
rstEmployeesUpdate.FindFirst "[CertificationNumber] = " & Me![cmbSearch]
Me.Bookmark = rst.Bookmark
Button01
Now the following is what I use to clear the fields, this is the first step in adding a new record.
With txtEmployeeLastName
.Enabled = True
.SetFocus
.Text = ""
End With
With txtCertNum
.Enabled = True
.SetFocus
.Text = ""
End With
With cmbAppraiserTypeGeneral
.Enabled = True
.SetFocus
.Value = 1
End With
With txtEmployeeFirstName
.Enabled = True
.SetFocus
.Text = ""
End With
Button02
Next is after the data has been entered into the fields you click another button and the following occurs:
Private Sub cmdAddNew_Click()
Dim dbs As Database
Dim rstEmployees As Recordset
Dim rstAppraiserType As Recordset
Dim rstEmployeesClone As Recordset
Dim rstAppraiserTypeClone As Recordset
Dim LName As String, FName As String, CertNum As String
If txtEmployeeFirstName.Enabled = False And txtCertNum.Enabled = False Then
MsgBox "Click the Clear Fields Button First Before Adding a New Employee", vbInformation
End
End If
'--------------------------------------------------------------
txtEmployeeFirstName.SetFocus
FName = txtEmployeeFirstName.Text
txtEmployeeLastName.SetFocus
LName = txtEmployeeLastName.Text
txtCertNum.SetFocus
CertNum = txtCertNum.Text
Set dbs = CurrentDb
Set rstEmployees = dbs.OpenRecordset("tblEmployees", dbOpenDynaset)
Set rstAppraiserType = dbs.OpenRecordset("tblAppraiserTypeDate", dbOpenDynaset)
Set rstEmployeesClone = rstEmployees.Clone
Set rstAppraiserTypeClone = rstAppraiserType.Clone
'--------------------------------------------------------------
txtEmployeeFirstName.SetFocus
If Len(Trim$(txtEmployeeFirstName.Text)) = 0 Then
Response = MsgBox("Please Enter a Name to add", vbOKCancel)
If Response = vbCancel Then
Me![txtEmployeeFirstName].Value = Me![txtEmployeeFirstName].OldValue
Me![txtEmployeeLastName].Value = Me![txtEmployeeLastName].OldValue
Me!txtCertNum.Value = Me!txtCertNum.OldValue
Me!cmbAppraiserTypeGeneral.Value = Me!cmbAppraiserTypeGeneral.OldValue
Me!txtDateChanged.SetFocus
Me!txtCertNum.Enabled = False
Me![txtEmployeeLastName].Enabled = False
Me![txtEmployeeFirstName].Enabled = False
Me!cmbAppraiserTypeGeneral.Enabled = False
Else
Me!txtEmployeeFirstName.SetFocus
End If
End
End If
'--------------------------------------------------------------
If IsNull(Me![txtDateChanged].Value) Then
Response = MsgBox("Please Enter a Date", vbOKCancel)
If Response = vbCancel Then
Me!txtDateChanged.SetFocus
Me![txtEmployeeFirstName].Enabled = False
Me![txtEmployeeLastName].Enabled = False
Me!txtCertNum.Enabled = False
Me!cmbAppraiserTypeGeneral.Enabled = False
Else
Me!txtDateChanged.SetFocus
End If
End
End If
'--------------------------------------------------------------
AppraiserType = Me![cmbAppraiserTypeGeneral]
FirstName = Me![txtEmployeeFirstName]
LastName = Me![txtEmployeeLastName]
Cert = Me![txtCertNum]
DateCh = Me![txtDateChanged]
'--------------------------------------------------------------
'rstEmployeesClone
Do Until rstEmployees.EOF
If rstEmployees!EmployeeLastName = LName And rstEmployees!EmployeeFirstName = FName Then
MsgBox "That Employee Already Exists in the Database. ", vbCritical, "No Duplicates Allowed"
Me![cmbAppraiserTypeGeneral].SetFocus
Me![txtEmployeeFirstName].Enabled = True
Me![txtEmployeeLastName].Enabled = True
Me![txtEmployeeFirstName].SetFocus
Me![txtEmployeeFirstName].Text = ""
Me![txtEmployeeLastName].SetFocus
Me![txtEmployeeLastName].Text = ""
Me![txtEmployeeFirstName].SetFocus
Exit Sub
End If
rstEmployees.MoveNext
Loop
rstEmployees.MoveFirst
'--------------------------------------------------------------
'rstEmployeesClone
Do Until rstEmployees.EOF
If Cert = rstEmployees!CertificationNumber Then
MsgBox "That Certification Number Already Exists in the Database. ", vbCritical, "No Duplicates Allowed"
Me![txtCertNum].Enabled = True
Me![txtCertNum].SetFocus
Me![txtCertNum].Text = ""
rstEmployees.Close
Exit Sub
End If
rstEmployees.MoveNext
Loop
'--------------------------------------------------------------
With rstEmployeesClone
.AddNew
!EmployeeFirstName = FirstName
!EmployeeLastName = LastName
!CertificationNumber = Cert
!AppraiserTypeGeneralID = AppraiserType
.Update
.Close
End With
'---------------------------------------------------------------
With rstAppraiserTypeClone
.AddNew
!CertificationNumber = Cert
!ChangeDate = DateCh
!AppraiserTypeID = AppraiserType
.Update
.Close
End With
'--------------------------------------------------------------
Set rstAppraiserTypeClone = Nothing
Set rstEmployeesClone = Nothing
Me!cmbSearch.SetFocus
Me![txtEmployeeFirstName].Enabled = False
Me![txtEmployeeLastName].Enabled = False
Me![txtCertNum].Enabled = False
Me![cmbAppraiserTypeGeneral].Enabled = False
'--------------------------------------------------------------
MsgBox FirstName & " " & LastName & " has been added to the database.", vbInformation
cmbSearch.Requery
MEForm.Refresh
rstEmployees.Close
Set rstEmployees = Nothing
rstAppraiserType.Close
Set rstAppraiserType = Nothing
dbs.Close
Set dbs = Nothing
'DoCmd.OpenForm "frmMain", acNormal, , , , acWindowNormal
'DoCmd.Close acForm, "frmMainEmployeeForm", acSaveYes
End Sub
Variables that are defined in Declarations:
Dim MEForm As Form
Dim AppraiserType As Integer
Dim DateCh As Date
Dim FirstName As String, LastName As String
Dim Cert As Integer
Dim Response
Dim AppraiserTypeNum As Integer
Dim FldChangeDate As Field, FldAppType As Field
Dim Desg As String