Complicated Data search problem

Hello Guys!

I have faced a problem which I cannot overcome in any way.

I have access database and sql string as follows:

sql = "SELECT ID, description, item, amount, product_time, customer, customer_po_no, mo_no, conf_date, time, comments " _
& " FROM orders " _
& " WHERE ID = " & CLng(myId) & "" _
& " ORDER BY mo_no"

How could I add this information to listboxes, every value in different
listbox.

And on top of every listbox should be textboxes where I could search the
records. For example if I have product "Chair123" and I enter "Cha"
it would show all the records starting with "Cha"


I know this is complicated but I still hope someone can help
me out.

Thank you so much in advance,
Jessica:)

in the change event of the textbox filter the recordset and repopulate the list control


myRs.filter = "description like '" & text1.text & "*'"

if not mrRs.eof then
' Clear then Populate the list box
else
' Clear the list box and or warn the user
end if

Thank you so much, jezzerdilla, for your answer, but I still have problem.
I got it to work in most part, but how can I polulate the list if the entry is found??

Public Sub Text1_Change()
Dim myrs As New ADODB.Recordset, sql As String
sql = "SELECT ID, description, item, amount, product_time, customer, customer_po_no, mo_no, conf_date, time, comments " _
& " FROM orders "

myrs.Open sql, sConn, adOpenKeyset, adLockOptimistic, adCmdText
myrs.Filter = "description like '" & Text1.Text & "*'"

If Not myrs.EOF Then
MsgBox "user" ' Clear then Populate the list box
Else
lstPerson.Clear ' Clear the list box and or warn the user
End If
End Sub


Jessica:)

And there is also another proble. When I clear the textbox totally there is an error "...arguments are out of acceptable range"

How could this be prevented?
Jessica

And there is also another proble. When I clear the textbox totally there is an error "...arguments are out of acceptable range"

How could this be prevented?
Jessica

I think it would be better if you would show us the code.

Hello Duane!

The code is two posts up, and I have a text box where I type
the description and if I type for example RPS111 then all the
product which starting with RPS111 should appear in listbox
below.

Jessica in trouble


And there is also another proble. When I clear the textbox totally there is an error "...arguments are out of acceptable range"

How could this be prevented?
Jessica

I think it would be better if you would show us the code.

Hello Duane!

The code is two posts up, and I have a text box where I type
the description and if I type for example RPS111 then all the
product which starting with RPS111 should appear in listbox
below.

Jessica in trouble


And there is also another proble. When I clear the textbox totally there is an error "...arguments are out of acceptable range"

How could this be prevented?
Jessica

I think it would be better if you would show us the code.

where's the code?

Hi Jess,

Try something like this, its probably best to open the recordset once on startup and then close on unload so you don't get loads of orphaned recordset objects knocking around...



Option Explicit

Private myRs As ADODB.Recordset
private sConn as ADODB.Connection

Private Sub Form_Load()
Dim sql As String

Set myRs = New ADODB.Recordset

sql = "SELECT ID, description, item, amount, product_time, customer, customer_po_no, mo_no, conf_date, time, comments " _
& " FROM orders "

'Setup sConn
sConn.open

myRs.Open sql, sConn, adOpenKeyset, adLockOptimistic, adCmdText

End Sub

Public Sub Text1_Change()
myRs.Filter = "description like '" & Text1.Text & "*'"

List1.Clear

Do While Not myRs.EOF
List1.AddItem myRs!field
myRs.movenext
Loop

End Sub

Private Sub Form_Unload(Cancel As Integer)
sConn.close
myRs.Close

Set myRs = Nothing
Set sConn = Nothing
End Sub


Smile :)