display query results

[infoscion]

Hi All:
I am in the process of developing a very simple search application. However, I have run into a few road blocks. Firstly, I was wondering as to how to include wild card characters to search for records. I know I can achieve that by using the LIKE criteria of SQL.
Secondly, more importantly , how do I report the results of the search ? Since I plan to use a recordset, I was wondering as to how do I transfer the results of the query onto another form or to a sub-form or even to excel/word application.
Advice, suggestions and tactics are greatly appreciated.
Thanks a million for your help.
Regards,
Info

 

[Blorf]

Say you have a form with an unbound text box called mysearch, form called my form. Make a query that has the like based on the mysearch text box, and make a new form based on the query.

Then, make the new form a sub form of myform, and include an instruction to requery the subform when mysearch is updated.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[infoscion]

Thanks for your inputs. But can I get some specifics about the display as to how do I transfer the contents to the sub form, especially when the number of records in the recordset is variable.
Info

 

[Blorf]

I don't fully understand your question.

You have a query based on your form, so you can base a report on that query, or export it, or open it as a recordset with vba.

What specificaly do you need help with?

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[infoscion]

Hi:
TABLE directory includes lastnames, first names and the addresses of the customers.
Here is part of the code. What I am trying to accomplish is that once the user enters a text message the SQL query is run. So depending upon the selection a number of records will be pulled. Please bear in mind the number of records pulled will vary depending on the search criteria.(e.g. 'Ma' or will pull records with last names mahan, mahalik, Mahony and so on..., 'Ba' will pull records with last names Bart, Bassinger , Bart and son on.. )
I was trying to count the number of records that are pulled by this query and create a dynamic list of text boxes and then populate them.
Please let me know if I am clear on this.



I was wondering as to how do I display the search result?
Your inputs are kindly requested.
Thanks a million.
Info


Private Sub Command2_Click()
'On Error GoTo Err_Command2_Click
Dim cn As ADODB.Connection
Dim strtext As String

Dim cmdCommand As New ADODB.Command
Dim r As CurrentData
Dim db As Object
Dim rs As ADODB.Recordset
Set cn = Nothing
Dim strSQL As String
Dim strcon As String
Set cn = New ADODB.Connection
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Desktop\lib.mdb"

Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
Set cmdCommand = New ADODB.Command
'display the message box that says that the serach criteria is being looked for
If IsNull(Me.Text0) Then
Msg = " You have not entered any search"
ans = MsgBox(Msg, vbYesNo)
If ans = vbNo Then
Exit Sub
Else
MsgBox "Please enter the search criteria for the directory search"



' Mylen = Len(Customer)
'If Mylen >= 2 Then
'strsql2 = Mid(strtext, 2, 3)

'strtext = Mid(Me.Text0(1, 1))
'If strtext = "*" Then
strSQL = "select * FROM directory WHERE last_name LIKE " & Forms!Form1!Text0 & ";"
'display the strSQL that goes with the QL
MsgBox (strSQL)
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
' if the recorset is empty
If rs.BOF Or rs.EOF Then
MsgBox "There are no records in the database"
Exit Sub
Else
'if the recorset is not empty
Set c = Recordset.Count
MsgBox (c)
End If

End If


End If
End sub