Keyword search box with results displayed in subform (1 Viewer)

[goodhvnting]

Hello!

This is my 3rd time posting in this wonderful and supportive community. My first two questions have been solved and I am back hoping to build on what i've already learned.

Problem:
I have a main form with a subform that displays records. In the main form I have an unbound text box with 2 buttons called "search" and "show all". My problem is I cannot figure out the right code so that if a search term is entered into the text box the results would populate in the subform. In addition to, if a user clicks on the "show all" button, all records would be displayed in the subform.

Please let me know if it would help to post the DB. I know I am missing some important statements but my knowledge is minimal that I can't figure it out.

Here is the code I have been playing with:

Property Get WhereTextSearch() As String
If Not IsNull(Me.txtSearch2) Or Me.txtSearch2 = "" Then
WhereTextSearch = _
"(FileNumber like ""*" & Me.txtSearch2 & "*"") " & _
"Or (FileTitle like ""*" & Me.txtSearch2 & "*"") " & _
"Or (Notes like ""*" & Me.txtSearch2 & "*"") " & _
"Or (DateOpened like ""*" & Me.txtSearch2 & "*"")"
End If
End Property

Private Sub SetSubformRecordSource()
Me.tblRecords_subform.Form.RecordSource = "SELECT * FROM tblRecords " & Me.WhereTextSearch

End Sub

Private Sub Command11_Click()
SetSubformRecordSource
End Sub

Private Sub Command12_Click()
SetSubformRecordSource
End Sub

Private Sub txtSearch2_AfterUpdate()
Call Command11_Click
End Sub

 

[jdraw]

I suggest you make a zip of your database and post it with some sample for search term.
You can post a file without 10 posts if you use zip format.

You might also find this free youtube video by Steve Bishop useful. Create Keyword Search


Good luck.

 

[MarkK]

I would do the if block expression like...

If Nz(Me.txtSearch2, "") <> "" Then
[COLOR="Green"]'or even[/COLOR]
If Not Nz(Me.txtSearch, "") = "" Then

...because in all cases, if there is data in the txtSearch control, you want the block to run. And for the 'show all' button, I would simply null the search box, and run your process normally (since, given your If block, it knows what to do), so...

sub showall_click
   me.txtSearch2 = Null
   SetSubformRecordSource
end sub

Does that make sense?

 

[pbaldy]

You may find this useful:

http://www.baldyweb.com/ImmediateWindow.htm

In addition to the previous thoughts, I don't see the word "WHERE" in the SQL; it would need to be.

 

[goodhvnting]

I would do the if block expression like...

If Nz(Me.txtSearch2, "") <> "" Then
[COLOR="Green"]'or even[/COLOR]
If Not Nz(Me.txtSearch, "") = "" Then

...because in all cases, if there is data in the txtSearch control, you want the block to run. And for the 'show all' button, I would simply null the search box, and run your process normally (since, given your If block, it knows what to do), so...

sub showall_click
   me.txtSearch2 = Null
   SetSubformRecordSource
end sub

Does that make sense?

Hi MarkK,

I think I understand what you are doing here. I am still having a problem with this statement:

Private Sub SetSubformRecordSource()
' here we set the recordsource of the form in the subform control (the subform)
Me.tblRecords_subform.Form.RecordSource = "SELECT * FROM tblRecords " & Me.WhereTextSearch

End Sub

I know I've written something wrong or am missing another statement. Any thoughts? I've attached the DB for reference if that will help. Again, many thanks. You are always exceptionally helpful. Cheers.

PS. Thank you all for the numerous responses. You've all be very helpful.

 

[Gasman]

Goodhvnting,

If you set a breakpoint on the Me.tblRecords_subform.Form.RecordSource = line and then enter
? Me.WhereTextSearch you will see the sql string.

However in your code you are only using WhereTextSearch ? and as Paul has pointed out in post 4, there is no WHERE statement ?

That should point you to your error

HTH

 

[MarkK]

I am still having a problem with this statement:

Private Sub SetSubformRecordSource()
' here we set the recordsource of the form in the subform control (the subform)
Me.tblRecords_subform.Form.RecordSource = "SELECT * FROM tblRecords " & Me.WhereTextSearch
End Sub

It's not enough to say you are having a problem, you need to say what the problem is. Maybe you are getting an error? Maybe you are getting an unexpected result? Describe the symptoms of the problem.

Also, as Paul pointed out, have you added the word 'WHERE' to your SQL construction code?

 

[goodhvnting]

Hi again,

I was able to solve the problem by following everyone's instructions.

Thanks to you all who tried to help me out. I am so very grateful. Saved me a lot of time and energy.

Thanks again!

 

[MarkK]

Nice job!!!