create text boxes on a fly 1

[infoscion]

Hi All:
I am attempting to implement a search application. Depending upon the number of records pulled up by the SQL query I would like to create corresponding number of text boxes on a new form. Please advise me as to how do I achieve this?
Thanks
Info

 

[PHV]

Why not simply use a ListBox or a continuous subform ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[infoscion]

PHV:
Thank you for your inputs.
If I have a list box can I transfer the results of a query to it? This is the code that I have so far:How do I tie in the results of the recordset with those of the entries for the list box that is there on the form that I am opening?
Regards,
Info

Private Sub Search_Click()
'On Error GoTo Err_Command2_Click
Dim cn As ADODB.Connection
' Dim cmdCommand As New ADODB.Command

Dim c As String
Dim rs As ADODB.Recordset
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\p22841.NTGROUP\Desktop\lib.mdb"
'open the connection
'strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Desktop\lib.mdb"
'open the connection
cn.Open strcon
'create a new instance of the record set
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
'display the message box that says that the search criteria is being looked for
If IsNull(Me.Text0) Then
Msg = " You have not entered any search criteria "
ans = MsgBox(Msg, vbYesNo)
If ans = vbNo Then
Exit Sub
Else
MsgBox "Please enter the search criteria for the directory search"
Exit Sub
End If
End If

If Not IsNull(Me.Text0) Then
strSQL = "SELECT * FROM directory WHERE last_name LIKE '" & Forms!Form1!Text0 & "%';"
'display the strSQL that goes with the SQL
MsgBox (strSQL)
'open the recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
If rs.BOF Or rs.EOF Then
MsgBox "There are no records in the database"
'disconnect the recordset
rs.Close
cn.Close
Set cn = Nothing
Exit Sub

End If

If Not rs.EOF And Not rs.BOF Then
c = rs.RecordCount
MsgBox (c)

DoCmd.OpenForm "search names"

End If

End If

 

[PHV]

Create a listbox based on the directory table with the wizard.
Then in your event procedure:
[name of new listbox].RowSource = "SELECT * FROM directory WHERE last_name LIKE '" & Forms!Form1!Text0 & "*'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[infoscion]

PHV:
Thank you for your inputs.

I understand that I could do something like that. But the issue at least I think is that it wont be in a position to handle the wild card character(like a * or some thing). To include the wild card character I think I need to write the VBA code for it.
Also, I went ahead and did some thing like this.

If you look at the attached code I was wondering as to how do I concantenate the records from the recordset so that they can be displayed in the list box?
Your suggestions are greatly appreciated.
Regards,
Info

DoCmd.OpenForm "search names"
Set f = Screen.ActiveForm
With f
If Not rs.EOF And Not rs.EOF Then
Do While Not rs.EOF
'''''''''' IS THERE A WAY TO CONCANTENATE THE RECORDSET VALUES AND INCLUDE THEM IN A LIST BOX?


strstring = rs.Fields("Last_name").Value & rs.Fields("First_name").Value & rs.Fields("Address").Value
!SelectCode.Value = strstring
rs.MoveNext
Loop
End If
End With
rs.Close
cn.Close
Set cn = Nothing



End If

 

[PHV]

Reread my previous post.
You don't need any recordset by setting dynamically the RowSource property of the listbox to the SQL code.

 

[infoscion]

PHV:
Thanks for your inputs
Regards,
Info
I was curious as to how do you assign a null value to a string?

 

[PHV]

how do you assign a null value to a string?
You can't as only Variant may hold Null.
However you may use ZeroLengthString:
strVar = "" (or = vbNullString)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[infoscion]

PHV:
I do really appreciate your help. I do have a follow up question. The wild card character could precede or follow the search criteria. Please correct me as to how do I handle the varibaility introduced through the location of the wild card character. I want the system to be flexible such that it can parse the typed in text and then generate the query results.
I am not sure ( confused because of my ignorance) as to how the RecordSource proprty of the list box can handle such conditional SQL statements.
Please steer me in the right direction.
Regards,
Info

 

[PHV]

strBefore = IIf(some condition here, "*", "")
strAfter = IIf(other condition here, "*", "")
[name of new listbox].RowSource = "SELECT * FROM directory WHERE last_name LIKE '" _
& strBefore & Forms!Form1!Text0 & strAfter & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[infoscion]

Hi PHV:
I have another follow up question for you.

Do you want me to include the query in the row source property of the list box or in the VBA code that drives the form.

When I include the query

[name of new listbox].RowSource = "SELECT * FROM directory WHERE last_name LIKE '" _
& strBefore & Forms!Form1!Text0 & strAfter & "'"

in the row source it gives me an error and when I inlcude it in the VBA code module it does nothing.
Please advise me.
I do appreciate your inputs.
Regards,
Info