How do I Display records in datasheet view

[Steve78]

Hi, this problem is probably very simple to solve but I can't seem to find an answer to it.

I have created a search form that returns all records meeting the criteria back into the search form. What I want to be able to do is to display the records in a datasheet view. This is what I currently have and it works ok but how do I convert?

Private Sub cmdSearch_Click()

Dim LSQL As String
Dim LSearchString As String
Dim LTownString As String
Dim LActive As Integer


Set LSQL = Me.RecordsetClone
If (Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True) And (Len(txtsearchTown) = 0 Or IsNull(txtsearchTown) = True) Then
'MsgBox "You must enter a search string."

Else
LSearchString = txtSearchString
LTownString = txtsearchTown
Select Case Me.Frame99.Value

Case Is = 1
stActive = " AND Active = -1"
Case Is = 2
stActive = " AND Active = 0"
Case Is = 3
stActive = ""
End Select

'Filter results based on search string
LSQL = "select * from tblContacts"

'LSQL = LSQL & " where LastName LIKE '*" & LSearchString & "*' AND Town LIKE '*" & LTownString & "*'" & stActive

If Form_frmMain2.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found"
Else
Form_frmMain2.RecordSource = LSQL
End If

'Clear search string
txtSearchString = ""
txtsearchTown = ""

End If
End Sub

 

[Ziggy1]

I wouldn't do it that way myself, all I do is create a query (of the same data source as form) and then place the control names into the criteria for the relevant fields. Then place a command button on the on your form to open the query. It will filter the query based on the Forms values

This is called Query by form.

the basic syntax in the Queries Criteria is

[Forms]![FormName]![ControlName]

as a wildcard Place "*" in the default value of the control.....

Like([Forms]![FormName]![ControlName]) & "*"

That will not filter the field but return all its values.


And if by chance I am describing what you are already doing for your search form then just add a command button to open the forms table/Query.

 

[Steve78]

Thanks

Hi Ziggy1,

Thanks for the reply. Yes I could do it as you suggest but I would like to do it this way as there are more criteria I want to add in and I think (though I must admit I'm not really sure) that this method will be more flexible. I think it has something to do with recordsets but I'm not really sure.