Search form to populate datasheet instead of listbox (1 Viewer)

[megatronixs]

Hi all,

I have the below code to do searches for example the creator of some minutes. The results are shown in a listbox. Is it possible to show this in a datasheet instead?

Private Const BASE_SQL As String = _
"SELECT record_id, subject, date_of_meeting, invitation_status, time_of_meeting, who_minutes_tracker, meeting_room, participant_name " & _
"FROM tbl_actions " & _
"<whereclause>" & _
"ORDER BY record_id;"

Private Sub search_creator_text_Change()
' This event fires for every keystroke in the textbox
Dim where As String
If Me.search_creator_text.Text <> "" Then
where = "WHERE who_minutes_tracker LIKE '*" & Me.search_creator_text.Text & "*' "
End If
Me.search_results_list.RowSource = Replace(BASE_SQL, "<whereclause>", where)
End Sub

greetings.

 

[Uncle Gizmo]

I must say that's an interesting way of building an SQL statement. I'd never thought of doing it like that! I will have to give that a try sometime.

Is your datasheet going to be within a subform window on a main form?

 

[megatronixs]

hi,
it will be in a subform. The main form has the search text, and below there is the datasheet subform.

Greetings.

 

[Uncle Gizmo]

You only need to change your code slightly, it would look something like the following:-

Me.subFrmWinForYourForm.Form.RecordSource = Replace(BASE_SQL, "<whereclause>", where)

Where "subFrmWinForYourForm" is the name of the subform/subreport control which houses your subform.

".Form." refers to the Form contained in the aforementioned Control.

".RecordSource =" refers to the Record Source of the aforementioned Form.

And then you know about the rest of the code!

I recently created a playlist on my YouTube channel called "Subform" - Basically - I went through all my videos and just tagged ones with "subform" if they contained any mention of subforms at all! Hopefully there will be a bit more information about setting this up on subforms if you need it..

 

[megatronixs]

It works

Thanks a lot

Greetings.