Clear Box after

[mtagliaferri]

I have a command to search a record, after I run the command how can I clear the search box from its value ready for another imput?

Private Sub CmdSeach_Click()
Dim strsearch As String
Dim strTxt As String
strTxt = Me.TxtSearchBox.Value
strsearch = "SELECT * from qryCrewMemberList where ((StaffNumber like ""*" & strTxt & "*"") or (Surname like ""*" & strTxt & "*"") or (name like ""*" & strTxt & "*""))"
Me.RecordSource = strsearch
End Sub

Thanks

 

[MarkK]

I would do it like this . . .

Private Const SQL_SEARCH As String = _
    "SELECT * " & _
    "FROM qryCrewMemberList " & _
    "WHERE StaffNumber like ""*{0}*"" " & _
        "OR Surname like ""*{0}*"" " & _
        "OR Name like ""*{0}*"";"

Private Sub CmdSeach_Click()
[COLOR="Green"]    'set the record source[/COLOR]
    Me.RecordSource = Replace(SQL_SEARCH, "{0}", Me.TxtSearchBox)
[COLOR="Green"]    'clear the textbox[/COLOR]
    Me.TxtSearchBox = ""
End Sub

 

[mtagliaferri]

Thanks, can you highlight the difference with the first part of the code?

 

[arnelgp]

no difference.

 

[mtagliaferri]

I am now struggling again....I have a cmd button to filter the list in the form to show only those records that have a check box marked. I have this code

DoCmd.OpenForm "frmCrewMemberList", acNormal, "", "[Resined]=True", , acNormal

and it works fine if I open just the form, but because this form is bound into a tab of the navigational form when I run the form this function no longer works.
What other VBA command can I use?

 

[MarkK]

DoCmd.OpenForm might seem to not work if the form is already open. You can either close the form first and re-open it, or explicitly change the Filter. This code, for instance . . .

DoCmd.OpenForm "frmCrewMemberList", , , "[Resined]=True"

. . . opens the form with a Filter of "[Resined]=True". To open with a different filter, close it first and re-open . . .

   const FN as string = "frmCrewMemberList"
   docmd.close acform, FN
   docmd.openform FN, , , "Resigned = False"

. . . or, just change the filter . . .

   const FN as string = "frmCrewMemberList"
   forms(FN).Filter = "Resigned = False"

If that's what's failing.

 

[mtagliaferri]

Still not working :-(

Private Sub CmdViewResined_Click()
On Error GoTo CmdViewResined_Click_Err

Const FN As String = "frmCrewMemberList"
Forms(FN).Filter = "Resigned = False"


CmdViewResined_Click_Exit:
Exit Sub

CmdViewResined_Click_Err:
MsgBox Error$
Resume CmdViewResined_Click_Exit

End Sub

 

[MarkK]

"Not working" is not enough information. Do you get an error? What error? Do you get a wrong or unexpected result? What result is expected, what do you get?

One guess I have is that this code will generate error 2450 . . .

Forms(FN).Filter = "Resigned = False"

. . . if the form FN is not open. Is that what you mean by "not working?"

 

[Cronk]

Maybe
Forms(FN).Filter = "Resined = False" '--Not Resigned
Forms(FN).FilterOn = true

Although if the form is opened with a where condition of "Resined = True", the filter won't work.

 

[mtagliaferri]

apart from the spelling mistake I have changed the command to "openForm" which if I open the actual frmCrewMemberList the filter does work however when the subform is open in the navigational form frmMain this does not work hence the reason I thought that a filter option was going to work