Hello,
In my spreadsheet, after adding data into the form fields, I am supposed to click on 'Add New Record' and the data will be written to Sheet 2. At the same time, the data in Sheet 2 will be sorted into Last_Name alphabetical order and the fields on the form will be cleared ready for the next set of data to be entered.
This was working correctly in the previous version of the same spread sheet.
In the current version of the spread sheet, I have added 12 fields to the data form, and reorganised the tab order. I have methodically gone over the code and updated the cell references to reflect the changes. Now I've done this I can still:
- Write a new record using the form, and have the new data show in the correct columns on Sheet 2
- Search for and edit a record correctly
- Search for and delete a record correctly
For a reason I can't fathom however I cannot now:
- Make the data in Sheet 2 sort Last_Name in alphabetical order
- Clear the fields on the data form after adding or editing a new record
When adding a new record the following error message appears: "error 1004 application defined or object defined error"
Can anyone help me identify and fix the problem? The spread sheet is attached. The code relating to adding a new record is below.
Thank you very much for your help.
P.s. The password for the VBA code is 'Travel'
Private Sub cmdAdd_Click()
'dimention the variable
Dim DataSH As Worksheet
Dim Addme As Range
'set the variable
Set DataSH = Sheet2
'error handler
On Error GoTo errHandler:
'set variable for the destination
Set Addme = DataSH.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
If Me.Last_Name = "" Or Me.First_Name = "" Or Me.Email_Address = "" Then
MsgBox "Information missing, please add missing data"
Exit Sub
End If
'send the values to the database
With DataSH
'add the unique reference ID then all other values
Addme.Offset(0, -1) = DataSH.Range("BB5").Value + 1
Addme.Value = Me.Last_Name
Addme.Offset(0, 1).Value = Me.First_Name
Addme.Offset(0, 2).Value = Me.Work_Department
Addme.Offset(0, 3).Value = Me.Job_Title
Addme.Offset(0, 4).Value = Me.Home_Postcode
Addme.Offset(0, 5).Value = Me.Base_Location
Addme.Offset(0, 6).Value = CDbl(Me.Distance_Miles)
Addme.Offset(0, 7).Value = Me.Email_Address
Addme.Offset(0, 8).Value = Me.Work_Tel_No
Addme.Offset(0, 9).Value = Me.Mob_Tel_No
Addme.Offset(0, 10).Value = Me.Line_Manager_First_Name
Addme.Offset(0, 11).Value = Me.Line_Manager_Last_Name
Addme.Offset(0, 12).Value = Me.Line_Manager_Email
Addme.Offset(0, 13).Value = Me.Line_Manager_Tel_No
Addme.Offset(0, 14).Value = Me.Mode_of_Commute
Addme.Offset(0, 15).Value = Me.Car_Use_Freq
Addme.Offset(0, 16).Value = Me.Type_of_Permit
Addme.Offset(0, 17).Value = Me.Pay_Band
Addme.Offset(0, 18).Value = Me.Hours_Worked
Addme.Offset(0, 19).Value = Me.Vehicle1_Make
Addme.Offset(0, 20).Value = Me.Vehicle1_Model
Addme.Offset(0, 21).Value = Me.Vehicle1_Colour
Addme.Offset(0, 22).Value = Me.Reg1_No
Addme.Offset(0, 23).Value = Me.Engine1_Type
Addme.Offset(0, 24).Value = CDbl(Me.Car1_CO2)
Addme.Offset(0, 25).Value = Me.Vehicle2_Make
Addme.Offset(0, 26).Value = Me.Vehicle2_Model
Addme.Offset(0, 27).Value = Me.Vehicle2_Colour
Addme.Offset(0, 28).Value = Me.Reg2_No
Addme.Offset(0, 29).Value = Me.Engine2_Type
Addme.Offset(0, 30).Value = CDbl(Me.Car2_CO2)
Addme.Offset(0, 31).Value = Me.Permit_Criteria_Met
Addme.Offset(0, 32).Value = Me.Temp_Permit_Expiry
Addme.Offset(0, 33).Value = Me.Criteria_Notes
Addme.Offset(0, 34).Value = Me.Date_Form_Recd
Addme.Offset(0, 35).Value = Me.Permit_Approved
Addme.Offset(0, 36).Value = Me.Date_Approved
Addme.Offset(0, 37).Value = Me.Date_Permit_Collected
Addme.Offset(0, 38).Value = Me.Deposit_Paid
Addme.Offset(0, 39).Value = Me.Method_of_Payment1
Addme.Offset(0, 40).Value = Me.Receipt_Given1
Addme.Offset(0, 41).Value = Me.Date_Replacement_Issued
Addme.Offset(0, 42).Value = Me.Fee_Paid
Addme.Offset(0, 43).Value = Me.Method_of_Payment2
Addme.Offset(0, 44).Value = Me.Receipt_Given2
Addme.Offset(0, 45).Value = Me.Date_Permit_Handed_In
Addme.Offset(0, 46).Value = Me.Refund_Given
Addme.Offset(0, 47).Value = Me.Receipt_Collected
End With
'sort the data by "Last_Name"
DataSH.Select
With DataSH
.Range("B6:AX12500").Sort Key1:=Range("C6"), Order1:=xlAscending, Header:=xlGuess
End With
'clear the values after entry
Clear
'communicate with the user
MsgBox "The staff record was successfully added"
'return to interface sheet sheet
Sheet2.Select
'reset the form
On Error GoTo 0
Exit Sub
errHandler:
'if error occurs then show me exactly where the error occurs
MsgBox "Error " & Err.Number & _
" (" & Err.Description & ")in procedure cmdClear_Click of Form EmployeeDB"
End Sub
Sub Clear()
Dim ctl As Control
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "ListBox"
ctl.RowSource = ""
Case "ComboBox"
ctl.Value = ""
End Select
Next ctl
End Sub
Private Sub cmdClearme_Click()
'clear all controls
Clear
End Sub