The extract has a missing or invalid field name 1004

N

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,256
Office Version
  1. 2016
Hi, I have code (below)which keeps returning the error "THE EXTRACT HAS A MISSING OR INVALID FIELD NAME, error 1004"

I cannot for the life of me understand why, hopefully someone can see it. Fingers crossed. Thanks.


Code: 
Option Explicit'Private variables
Dim cNum As Integer
Dim X As Integer


Private Sub cmdLookup_Click()
'call lookup macro
    Lookup
End Sub


Sub Lookup()
'declare the variables
    Dim Due As Variant
'error statement
    On Error GoTo errHandler:
'clear the listbox
    lstLookup.RowSource = ""
'set the variable
    Due = Me.cboStart.Value
'if "New" or "Once" is selected run a different filter
    If Me.cboStart.Value = "Once" Or Me.cboStart.Value = "New" Then
        Sheet2.Range("N7").Value = Me.cboStart.Value
        AdvFilter_Once
'if the results are nil then clear the rowsource to avoid an error
        If Sheet2.Range("T7").Value = "" Then
            lstLookup.RowSource = ""
        Else
'add range to rowsource if range has values
            lstLookup.RowSource = "Filter_Staff"
        End If
        Exit Sub
    End If
'if no date selected for criteria
    With Sheet2
        If Me.cboStart = "" Then
            .Range("O7").Value = ""
            .Range("P7").Value = ""
            .Range("Q7").Value = Me.txtLookup
            .Range("R7").Value = Me.cboDepartment
'if date is selected
        Else
            .Range("P7").Value = "=""<""&TODAY()" & "+" & Due
            .Range("O7").Value = "="">""&TODAY()"
            .Range("Q7").Value = Me.txtLookup
            .Range("R7").Value = Me.cboDepartment
        End If
    End With
'run the filter
    AdvFilter
'if the results are nil then clear the rowsource to avoid an error
    If Sheet2.Range("T7").Value = "" Then
        lstLookup.RowSource = ""
    Else
'add range to rowsource if range has values
        lstLookup.RowSource = "Filter_Staff"
    End If
'error block
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub
Private Sub cmdOverdue_Click()
'error statement
    On Error GoTo errHandler:
'clear the listbox
    lstLookup.RowSource = ""
'clear controls
    Me.txtLookup.Value = ""
    Me.cboStart.Value = ""
'add department and date range to criteria
    With Sheet2
        .Range("P7").Value = ""
        .Range("Q7").Value = ""
        .Range("R7").Value = Me.cboDepartment.Value
        .Range("O7").Value = "=""<=""&TODAY()"
    End With
'run the filter
    AdvFilter
'check for value and adjust rowsource to avoid an error
    If Sheet2.Range("T7").Value = "" Then
        lstLookup.RowSource = ""
    Else
        lstLookup.RowSource = "Filter_Staff"
    End If
'error block
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub










Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'declare the variables
    Dim ID As String
    Dim I As Integer
    Dim findvalue
    'error block
    On Error GoTo errHandler:
'get the select value from the listbox
    For I = 0 To lstLookup.ListCount - 1
        If lstLookup.Selected(I) = True Then
'set the listbox column
            ID = lstLookup.List(I, 9)
        End If
    Next I
'find the value in the range
    Set findvalue = Sheet2.Range("L:L").Find(What:=ID, LookIn:=xlValues).Offset(0, -9)
'add the values to the userform controls
    cNum = 10
    For X = 1 To cNum
        Me.Controls("Reg" & X).Value = findvalue
        Set findvalue = findvalue.Offset(0, 1)
    Next
'disable the controls to make the user select an option
'error block
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub
Private Sub cmdAdd_Click()
'declare the valiable
    Dim nextrow As Range
'error handler
    On Error GoTo errHandler:
    Application.ScreenUpdating = False
'force user to click the option button
Me.Reg10.Value = Sheet2.Range("J2").Value + 1
    If Me.Reg4.Enabled = False Then
        MsgBox "You need to click the Add Option Button"
        Exit Sub
    End If
'set the next row in the database
    Set nextrow = Sheet2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
'check for values in all controls
    If Me.Reg8.Value = "New" Or Me.Reg8.Value = "Once" Then
    For X = 1 To 8
        If Me.Controls("Reg" & X).Value = "" Then
            MsgBox "You need to add the skill and first and last names"
            Exit Sub
        End If
    Next
    Else
    For X = 1 To 10
        If Me.Controls("Reg" & X).Value = "" Then
            MsgBox "You need to add the skill and first and last names"
            Exit Sub
        End If
    Next
    End If
'check for duplicate staff
    If WorksheetFunction.CountIf(Sheet2.Range("F:F"), Me.Reg4.Value) > 0 Then
        MsgBox "This staff member already exists"
        Exit Sub
    End If
'add value to the next row in the database
    nextrow = Reg1.Value
    nextrow.Offset(0, 1) = Reg2.Value
    nextrow.Offset(0, 2) = Reg3.Value
    nextrow.Offset(0, 3) = Reg4.Value
    nextrow.Offset(0, 4) = Reg5.Value
    nextrow.Offset(0, 5) = Reg6.Value
    nextrow.Offset(0, 10) = Reg11.Value
    'format the date controlls
    With nextrow
        .Offset(0, 6).Value = Format(Reg7.Value, "dd/mm/yy")
    End With
    nextrow.Offset(0, 7) = Reg8.Value
    With nextrow
        .Offset(0, 8).Value = Format(Reg9.Value, "dd/mm/yy")
    End With
    nextrow.Offset(0, 9) = Reg10.Value
'sort the database
    Sortit
'set the criteria for the filter to show the department
    With Sheet2
        .Range("P7").Value = ""
        .Range("Q7").Value = ""
        .Range("R7").Value = Me.Reg5.Value
        .Range("O7").Value = ""
    End With
'run the filter
    AdvFilter
'add the rowsource to the listbox
    lstLookup.RowSource = "Filter_Staff"
'clear the controls
    For X = 1 To 10
        Me.Controls("Reg" & X).Value = ""
    Next
   
'error block
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub
Private Sub cmdTraining_Click()
'declare the variables
Dim cNum As Integer
Dim nextrow As Range
Dim MyCell As Range
Dim rng As Long
'error handling
On Error GoTo errHandler:
'check for duplicates
rng = Sheet2.Cells(Rows.Count, "F").End(xlUp).Row
For Each MyCell In Sheet2.Range("F7:F" & rng)
If MyCell = Me.Reg4.Value And MyCell.Offset(0, 2).Value = Me.Reg6.Value Then
MsgBox "This training already exists for this staff member"
Exit Sub
End If
Next MyCell
'check for values
Me.Reg10.Value = Sheet2.Range("J2").Value + 1
If Reg1.Value = "" Or Reg4.Value = "" Then
MsgBox "There is not data to edit"
Exit Sub
End If
'check that the date is a date
If Not IsDate(Me.Reg7) Then
MsgBox "Completed date must be a date format"
Exit Sub
End If
'find the next row to add data to
Set nextrow = Sheet2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
'check for values in the controls
If Me.Reg6.Value = "" Or Me.Reg7.Value = "" Or Me.Reg8.Value = "" Then
MsgBox "You need to add all data"
Exit Sub
End If
'clear the listbox
lstLookup.RowSource = ""
'add the values to the database
nextrow = Reg1.Value
nextrow.Offset(0, 1) = Reg2.Value
nextrow.Offset(0, 2) = Reg3.Value
nextrow.Offset(0, 3) = Reg4.Value
nextrow.Offset(0, 4) = Reg5.Value
nextrow.Offset(0, 5) = Reg6.Value
nextrow.Offset(0, 10) = Reg11.Value
'format the date values on the worksheet
With nextrow
.Offset(0, 6).Value = Format(Reg7.Value, "dd/mm/yy")
End With
nextrow.Offset(0, 7) = Reg8.Value
With nextrow
.Offset(0, 8).Value = Format(Reg9.Value, "dd/mm/yy")
End With
nextrow.Offset(0, 9) = Reg10.Value
'sort the database
Sortit
'run the filter
AdvFilter
'refresh the rowsource in the listbox
lstLookup.RowSource = ""
lstLookup.RowSource = "Filter_Staff"
'error block
On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"
End Sub
Private Sub cmdEdit_Click()
'declare the variables
Dim findvalue As Range
Dim cNum As Integer
'error handling
On Error GoTo errHandler:
'check for values
If Reg1.Value = "" Or Reg4.Value = "" Then
MsgBox "There is not data to edit"
Exit Sub
End If
'check to see if the date is entered
If Not IsDate(Me.Reg7) Then
MsgBox "Completed date must be a date format"
Exit Sub
End If
'clear the listbox
lstLookup.RowSource = ""
'find the row to edit
Set findvalue = Sheet2.Range("L:L").Find(What:=Reg10, LookIn:=xlValues).Offset(0, -9)
'update the values
findvalue = Reg1.Value
findvalue.Offset(0, 1) = Reg2.Value
findvalue.Offset(0, 2) = Reg3.Value
findvalue.Offset(0, 3) = Reg4.Value
findvalue.Offset(0, 4) = Reg5.Value
findvalue.Offset(0, 5) = Reg6.Value
nextrow.Offset(0, 10) = Reg11.Value
'format date values
With findvalue
.Offset(0, 6).Value = Format(Reg7.Value, "dd/mm/yy")
End With
findvalue.Offset(0, 7) = Reg8.Value
With findvalue
.Offset(0, 8).Value = Format(Reg9.Value, "dd/mm/yy")
End With
findvalue.Offset(0, 9) = Reg10.Value
'run the filter
AdvFilter
'add the new values to the listbox
lstLookup.RowSource = "Filter_Staff"
'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "An Error has Occurred  " & vbCrLf & _
"The error number is:  " & Err.Number & vbCrLf & _
Err.Description & vbCrLf & "Please notify the administrator"
End Sub
Private Sub cmdDelete_Click()
'declare the variables
    Dim findvalue As Range
    Dim cDelete As VbMsgBoxResult
    Dim cNum As Integer
'error statement
    On Error GoTo errHandler:
'check for values
    If Reg1.Value = "" Or Reg4.Value = "" Then
        MsgBox "There is not data to delete"
        Exit Sub
    End If
'give the user a chance to change their mind
    cDelete = MsgBox("Are you sure that you want to delete this training", vbYesNo + vbDefaultButton2, "Are you sure????")
    If cDelete = vbYes Then
'find the row
        Set findvalue = Sheet2.Range("L:L").Find(What:=Reg10, LookIn:=xlValues)
        findvalue.EntireRow.Delete
    End If
'clear the controls
    cNum = 10
    For X = 1 To cNum
        Me.Controls("Reg" & X).Value = ""
    Next
'run the filter
    AdvFilter
'add the values to the listbox
    lstLookup.RowSource = ""
    lstLookup.RowSource = "Filter_Staff"
'error block
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub
Sub Setit()
'disable,clear values and change the back color of all controls
    cNum = 10
    For X = 1 To cNum
        Me.Controls("Reg" & X).Value = ""
    Next
'clear the criteria range
    With Sheet2
        .Range("P7").Value = ""
        .Range("Q7").Value = ""
        .Range("R7").Value = ""
        .Range("O7").Value = ""
    End With
'clear the listbox
    lstLookup.RowSource = ""
'clear the controls
With Me
    .txtLookup.Value = ""
    .cboDepartment.Value = ""
    .cboStart.Value = ""
    End With
End Sub


Private Sub Reg7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'check for date value
Me.Reg7 = Format(Me.Reg7, "dd/mm/yy")
    If Not IsDate(Me.Reg7) Then
        MsgBox "Completed date must be a date format"
        Me.Reg7.Value = ""
        Exit Sub
    End If
End Sub
Private Sub Reg8_Change()
'add values to criteria
Me.Reg9.Value = Format(Me.Reg9.Value, "dd/mm/yy")
    With Sheet3
        .Range("O7").Value = Format(Me.Reg7.Value, "dd/mm/yy")
        .Range("P7").Value = Me.Reg8.Value
    End With
    Me.Reg9.Value = Format(Sheet3.Range("Q7").Value, "dd/mm/yy")
End Sub
Private Sub Reg9_Change()
Me.Reg9 = Format(Me.Reg9, "dd/mm/yy")
End Sub




'THIS SECTION CREATES AN ERROR
Private Sub UserForm_Initialize()
'format the control
    Me.Reg7 = Format(Me.Reg7, "dd/mm/yy")
    Me.Reg9 = Format(Me.Reg9, "dd/mm/yy")
'clear the listbox
    Me.lstLookup.RowSource = ""


    AdvFilter
End Sub
Private Sub cmdClear_Click()
'call macro Setit to clear values
    Setit
End Sub
Private Sub cmdClear2_Click()
'call macro Setit to clear values
    Setit
End Sub
Private Sub cmdClose_Click()
'close userform
    Unload Me
End Sub
 
Sort by date Sort by votes
Step through your code using F8, which will execute each line one-by-one. Then tell me which line causes the error.
 
Upvote 0
You must log in or register to reply here.

Similar threads

KlausW
  • Solved
Fil named after cell K9 and send by mail
Replies
2
Views
74
KlausW
KlausW
KlausW
  • Solved
Take fil name from cell
Replies
4
Views
291
KlausW
KlausW
K
  • Question Question
Two different worksheets with similar VBA's to send out emails getting Ambiguous name detected: RangetoHTML
Replies
1
Views
70
kac1125
K
A
  • Solved
Extract and Merge Files
Replies
1
Views
223
rowebca
rowebca
I
  • Question Question
Vba has stopped adding hyperlink to my customers name
Replies
3
Views
144
ipbr21054
I

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top