Search as I type

[Cindy]

hey vbaInet, I was referring to your point about giving total control to the user and easing the 'pressure' on the database.

if one were to take the route of giving control to the user, what would this entail? would it still need the search box or just the search button in addition to the two ID fields?

 

[vbaInet]

hey vbaInet, I was referring to your point about giving total control to the user and easing the 'pressure' on the database.

if one were to take the route of giving control to the user, what would this entail? would it still need the search box or just the search button in addition to the two ID fields?

Definitely the search box and a command button for firing up the search. The two ways of firing off a search will be:

1. Clicking the command button
2. Hitting the Enter key

There's a property of a command button called Default. If you set this to True, whenever the Enter key is hit it will run the Click event of the command button. So what you can do is on the Got Focus event of the textbox you'll set the button's Default property to True, and on the Lost Focus event of the textbox set the button's Default property to False.

 

[Cindy]

hi vbaInet. I really appreciate your patience and help. I put this code which CJ London provided in the change event of the search box but the ELSE line is highlighted in red. what could be wrong with it?:banghead:
my form is called ObstetricsForm and the
searchbox is called cmdSRCHBOX

Private Sub cmdSRCHBOX_Change()
If cmdSRCHBOX.Text = "" Then
ObstetricsFormResult.Form.Filter = ""
Else
ObstetricsFormResult.form.filter = "[Trip_ID] Like '*" & cmdSRCHBOX.Text & "*' OR " & [Patient_ID] Like '*" & cmdSRCHBOX.Text"
End If
ObstetricsFormResult.Form.FilterOn = True
End Sub

 

[vbaInet]

You can use either one of these:

    If Len(cmdSRCHBOX.Text & vbNullString) = 0 Then
        ObstetricsFormResult.Filter = vbNullString
        ObstetricsFormResult.FilterOn = False
    Else
        ObstetricsFormResult.Filter = "[Trip_ID] Like '*" & cmdSRCHBOX.Text & "*' OR [Patient_ID] Like '*" & cmdSRCHBOX.Text
        ObstetricsFormResult.FilterOn = True
    End If
    


    With ObstetricsFormResult
        If Len(cmdSRCHBOX.Text & vbNullString) = 0 Then
            .Filter = vbNullString
            .FilterOn = False
        Else
            .Filter = "[Trip_ID] Like '*" & cmdSRCHBOX.Text & "*' OR [Patient_ID] Like '*" & cmdSRCHBOX.Text
            .FilterOn = True
        End If
    End With

 

[Cindy]

Thank you!! I'll work with this and post back an update

 

[Cindy]

hey vbaInet, I put the first part of the 2 codes below in the change event of cmdSRCHBOX but got this error:

Run-Time error '424': object required

 

[Cindy]

forgot to add:

when I used the WITH code, I got
Run-time error '438': object doesn't support this property or method

 

[vbaInet]

It's always good to include which row it highlights when you hit Debug.

Just include ".Form" as you had it before. I forgot about that.

With ObstetricsFormResult[COLOR="Red"].Form[/COLOR]

 

[Cindy]

please see the attached :banghead:

 

[vbaInet]

What is ObstetricsFormResult? A subform, a form?

 

[Cindy]

in this form, ObstetricsFormResult would be a tab page. I could not add a subform to the tab control because for some reason, when I tried, it covered the other controls there. I could not "bring them to the front" if there is such a thing in VB.

 

[vbaInet]

Ok. Remember a tab is just for layout and you can't Filter the tab, you filter records bound to the form. Meaning, the Form property is a property of the form and not the tab. If you remove all references to Obstetrics your code should then be:

With Me

End With

 

[Cindy]

thanks vbaInet. Really appreciate your time and patience. I removed all references to ObstetricsForm but it gave me
run-time error'3075': syntax error in string

.Filter = "[TripID] Like '*" & cmdSRCHBOX.Text & "*' OR [PatientID] Like '*"
& cmdSRCHBOX.Text

This is the code I have there:
With Me
If Len(cmdSRCHBOX.Text & vbNullString) = 0 Then
.Filter = vbNullString
.FilterOn = False
Else
.Filter = "[TripID] Like '*" & cmdSRCHBOX.Text & "*' OR [PatientID] Like '*" & cmdSRCHBOX.Text
.FilterOn = True
End If
End With

 

[vbaInet]

What did you enter as the search text?

 

[vbaInet]

Oh sorry, I didn't look propertly. There's a missing single quote and asterisk at the end.

 

[Cindy]

don't if it helps but [TripID] and [PatientID] have number data type. would they need to be converted to string?

 

[Cindy]

like this?:

.Filter = "[TripID] Like '*" & cmdSRCHBOX.Text & "*' OR [PatientID] Like '*" & cmdSRCHBOX.Text & "*'

 

[Cindy]

very much appreciated vbaInet!!

The pesky bug is gone but...
I can only type in one digit in the searchbox. Each digit overwrites the previous one

 

[vbaInet]

Do you have any other code that's overwriting it? Is the search box bound or unbound?

 

[Cindy]

the control is unbound and I've looked at the properties again. I don't see another code. unless...

as soon as I type the 1st digit in the searchbox, the first record having [TripID] with a similar 1st digit populates the form (tab page and all).

could using the actual program names of the text boxes have anything to do with it i.e. [TripID] and [PatientID]? clutching at straws here..