Search While Typing Value Errors 1

[Mke85]

Good Morning All!

I have come up with some code here to perform a "search while typing":

Private Sub txtSearchDescription_Change()
Dim SCriteria As String
SCriteria = "(CFTO." & Me!cboSearch & " LIKE '*" & Me!txtSearchDescription.Text & "*')"
Me.Filter = SCriteria
Me.FilterOn = True
Me.txtSearchDescription.SelStart = Len(Me.txtSearchDescription.Text)
If Len(txtSearchDescription) = 0 Or IsNull(txtSearchDescription) = True Then
Me!txtSearchDescription.SetFocus
Me.FilterOn = False
End If
Me!txtSearchDescription.SetFocus
End Sub

cboSearch is a combo box populated by a field list from the "CFTO" table. This "search as you type" code works for about 90% of the fields in the CFTO table. However fields that are dates the code returns a "Run time error 2448" "You can't assign a value to this object."

I'm not to advanced into VBA so this one is giving me trouble and can't figure out what it means..

Thanks for the help,

 

[dhookom]

Which lines causes the error? I would search or filter on a date field using a text/string value.

Duane
Hook'D on Access
MS Access MVP

 

[Mke85]

Me.Filter = SCriteria

Produces the error.

Thanks,

 

[dhookom]

What is the name of the date field?

Again, I would use a separate control or controls for date filtering. A date is not text.

Duane
Hook'D on Access
MS Access MVP

 

[Mke85]

The date field is called "Version Effective Date".

Not to sure what you mean by control lol.. So setup another section of code that enables when "Version Effective Date" is choosen from the drop down?

Thanks,

 

[dhookom]

How many of your field names have spaces in them? Most of us old programmers don't allow spaces. I would change the code to:

SCriteria = "(CFTO.[" & Me!cboSearch & "] LIKE '*" & Me!txtSearchDescription.Text & "*')"

If a date field was selected, I would enable or unhide two text boxes formatted as short date. Name them txtStartDate and txtEndDate. Then use code like:

SCriteria = "CFTO.[" & Me!cboSearch & "] Between #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"

Duane
Hook'D on Access
MS Access MVP

 

[Mke85]

Haha about 1 minute after my last post I noticed I didn't put the any [] around my field(s) to compensate for the spaces..which has now fixed all my problems...lol

Thanks for the help though!