Runtime Error 3075 - Access 2010

[cwturner2]

We use an access database for our Human Resources department. Recently we updated to Access 2010 on a couple of workstations. In Access 2010 we keep getting Runtime Error 3075 missing operator. I click debug and this is where the error is happening.



Private Function setFormRecordSource(Optional activeEmployeesOnly As Boolean)
'//set the form's RecordSource

Form.Filter = "employeeNumber<>9000"

Form.RecordSource = "Employees"
Form.OrderBy = "Lname, Fname"

If chkActive.Value = -1 Or activeEmployeesOnly Then
cmbFirst.RowSource = "SELECT Fname, Lname FROM Employees WHERE Active = 'Y' ORDER BY Fname;"
cmbLast.RowSource = "SELECT Lname, Fname FROM Employees WHERE Active = 'Y' ORDER BY Lname;"
Form.Filter = Form.Filter & " And Active = 'Y'"
Else
cmbFirst.RowSource = "SELECT Fname, Lname FROM Employees ORDER BY Fname;"
cmbLast.RowSource = "SELECT Lname, Fname FROM Employees ORDER BY Fname;"
End If

If cmbFirst.Value <> "" Then
Form.Filter = Form.Filter & " And Fname='" & cmbFirst.Value & "'"
End If

If cmbLast.Value <> "" Then
Form.Filter = Form.Filter & " And Lname='" & cmbLast.Value & "'"
End If

cmbFirst.Requery
cmbLast.Requery
Form.FilterOn = True
Form.OrderByOn = True

End Function

-- While playing with the code i noticed if I take out the "AND" in the bold line above. The error is removed within Access 2010; however, it creates the same error in Access 2007, 2003. We still have a couple users that utilize Access 2007 and need it to function properly in both instances. This is a program that I have inherited and my programming skills are minimal at best. I have worked on this for several hours already, and i can figure out ways to to get the code work in either 2010, or 2007; however, not in both.

Any suggestions would be greatly appreciated.

Thank you!

[help_me_with_access]

why are you using "FORM." for a qualifier? is this behind a form? if it is, use "ME."

doesn't look like there's anything wrong with the syntax. see if there are any comparisons between the v07 help file page for "form.filter" vs. the v.10 help file page.

another option, which you should use anyway simply because changing an object property time and time again in a single routine is a bad programming practice.

use a string variable to get to your end result then assign the string to the filter at the end of the routine.

[cwturner2]

Yes this is behind a form. Again I would like to state I have basic programming skills. I maintain the network / security. Our previous programmer left the company and at this time we have not hired a new one, so I am attempting to figure this out. When you state to use ME. should I change the form.filter to ME.filter?

[help_me_with_access]

Yep!!! =)

[cwturner2]

I changed the code to this. As it is, it works within Access 2010; however, in Access 2007 I am still getting the Run-time Error missing operator

Private Function setFormRecordSource(Optional activeEmployeesOnly As Boolean)
'//set the form's RecordSource

Me.Filter = "[employeeNumber] <> 9000"

Form.RecordSource = "Employees"
Form.OrderBy = "Lname, Fname"

If chkActive.Value = -1 Or activeEmployeesOnly Then
cmbFirst.RowSource = "SELECT Fname, Lname FROM Employees WHERE Active = 'Y' ORDER BY Fname;"
cmbLast.RowSource = "SELECT Lname, Fname FROM Employees WHERE Active = 'Y' ORDER BY Lname;"
Me.Filter = Me.Filter & " [Active] = 'Y'"
Else
cmbFirst.RowSource = "SELECT Fname, Lname FROM Employees ORDER BY Fname;"
cmbLast.RowSource = "SELECT Lname, Fname FROM Employees ORDER BY Fname;"
End If

If cmbFirst.Value <> "" Then
Me.Filter = Me.Filter & " And Fname='" & cmbFirst.Value & "'"
End If

If cmbLast.Value <> "" Then
Me.Filter = Me.Filter & " And Lname='" & cmbLast.Value & "'"
End If

cmbFirst.Requery
cmbLast.Requery
Me.FilterOn = True
Me.OrderByOn = True

End Function

[help_me_with_access]

change the code on a UNIVERSAL basis, my friend. always follow standards on a global scale. you'll love yourself later on when you're forced to debug!

try this instead (copy this verbatim):

Private Function setFormRecordSource(Optional activeEmployeesOnly As Boolean)
'//set the form's RecordSource
   Dim strFilter As String
   Dim srcFirst As String
   Dim srcLast As String   Me.FilterOn = False   'ADDED
   Me.OrderByOn = False   'ADDED
   strFilter = "[employeeNumber] <> 9000"
   Me.RecordSource = "Employees"
   Me.OrderBy = "[Lname], [Fname]"
   If Me.chkActive.Value = -1 Or activeEmployeesOnly Then
      Me.cmbFirst.RowSource = "SELECT Fname, Lname FROM Employees WHERE Active = 'Y' ORDER BY Fname;"
      Me.cmbLast.RowSource = "SELECT Lname, Fname FROM Employees WHERE Active = 'Y' ORDER BY Lname;"
      strFilter = strFilter & " AND [Active] = 'Y'"
   Else
      Me.cmbFirst.RowSource = "SELECT Fname, Lname FROM Employees ORDER BY Fname;"
      Me.cmbLast.RowSource = "SELECT Lname, Fname FROM Employees ORDER BY Fname;"
   End If
   If Me.cmbFirst.Value <> "" Then
      strFilter = strFilter & " And [Fname] = '" & Me.cmbFirst.Value & "'"
   End If
   If cmbLast.Value <> "" Then
      strFilter = strFilter & " And [Lname] = '" & Me.cmbLast.Value & "'"
   End If
   cmbFirst.Requery
   cmbLast.Requery
   Me.FilterOn = True
   Me.OrderByOn = True
   Me.Requery   'ADDED
End Function

also...please please PLEASE compact the database, and then run a test compile to see if this is an error that appears during the compilation process. if the compilation doesn't break because of this, your form object or file itself might be corrupted.