By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,482 Members | 3,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,482 IT Pros & Developers. It's quick & easy.

Runtime Error 3075 on search button

P: 1
I am trying to create a search form in Access where in people can search records by date range. But each time I click search I get a run time error 3075... with DoCmd.ApplyFilter task highlighted. I am new to VBA.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command62_Click()
  2.     ' Search Button
  3.     Call Search
  4. End Sub
  5. Sub Search()
  7.     Dim strCriteria, task As String
  9.     Me.Refresh
  10.     If IsNull(Me.txtDateIntakeAssignedFrom) Or IsNull(Me.txtDateIntakeAssignedTo) Then
  11.         MsgBox "Please enter the date range", vbInformation, "Date Range Required"
  12.         Me.txtDateIntakeAssignedFrom.SetFocus
  13.     Else
  14.         strCriteria = "([Date_Staff_Assigned]) >= #" & Me.txtDateIntakeAssignedFrom & "# And [Date_Staff_Assigned] <= #" & Me.txtDateIntakeAssignedTo & "#)"
  15.         task = "select * from frmCustomizeSearchDataonDatasheet where (" & strCriteria & ") order by [Date_Staff_Assigned]"
  16.         DoCmd.ApplyFilter task
  17.     End If
  19. End Sub
Apr 10 '18 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 3,284

Welcome to Bytes!

As a newcomer, I remind you to enclose your code in code tags (use the [CODE/] button in your text editor), as this is a requirement of this forum. I have edited your post to reflect such changes.

There are a few things I find unorthodox about your code. You said you are new to VBA, so I will try to be as instructional as possible.

First, You may want to change the name of your Search Sub, as the word "Search" is one of those words in Access that can cause the DB engine to occasionally become confused. This can occur with field names like "Name" or Tables named "Table", etc.

Second, unless you are calling the Search Sub from other areas in your code, you can roll all your code under the Command Button's OnClick event. There is nothing wrong with how you have done it, especially if you call this search from multiple locations.

Third, a Filter is defined as "the WHERE clause of a SQL string, without the word WHERE." So, by assigning an actuall SQL string to the Filter, you are causing the error in the syntax of te Filter. Your line #14 should be the appropriate filter that you use.

Fourth, I am going to assume that you want this filter to filter the records that are currently being displayed onthe Form? I must assume this as it is not directly stated. Although you can use the ApplyFilter method, the more trasditional method to use is to simply directly apply the filter to the form itself. In fact, when you use the ApplyFilter method, you are setting the filter, but indirectly.

I would also highly suggest that you get into the habit of including Error Handling Code into all your procedures. Again, I have done a simple error handling code for you.

So, using the majority of your code, we would get this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command62_Click()
  2. On Error GoTo EH
  3.     Dim strCriteria As String
  5.     Me.Refresh
  6.     If IsNull(Me.txtDateIntakeAssignedFrom) Or _
  7.         IsNull(Me.txtDateIntakeAssignedTo) Then
  8.         MsgBox "Please enter the date range", _
  9.             vbInformation, "Date Range Required"
  10.         Me.txtDateIntakeAssignedFrom.SetFocus
  11.     Else
  12.         strCriteria = "([Date_Staff_Assigned]) >= #" & _
  13.             Me.txtDateIntakeAssignedFrom & _
  14.             "# And [Date_Staff_Assigned] <= #" & _
  15.             Me.txtDateIntakeAssignedTo & "#)"
  16.         Me.Filter = strCriteria
  17.         Me.FilterOn = True
  18.     End If
  19.     Exit Sub
  20. EH:
  21.     MsgBox "There was an error searching the form!  " & _
  22.         vbCrLf & vbCrLf & Err.Number & _
  23.         vbCrLf & vbCrLf & Err.Description & vbCrLf & vbCrLf & _
  24.         "Please contact your Database Administrator.", _
  25.         vbCritical, "WARNING!"
  26.     Exit Sub
  27. End Sub
Hope this hepps!
Apr 10 '18 #2

Post your reply

Sign in to post your reply or Sign up for a free account.