search form

[Gazza2]

Can someone point me in the right direction please.

i have been searching for ages and cant seem to find what i want.

I have a search form for customers with a text box and a list box. What i am trying to do is as i type into the text box the list box displays the customers names etc.

I know what i want is here somewhere but i cant seem to find it.

Thanks

 

[DCrake]

Simple Software Solutions

Hi

Please find attached a demo of how to do this, along with a few other items in an earlier demo mdb.

Code Master:

 

[Gazza2]

demo missing

thanks for the reply but could you attach the demo please

thanks

 

[Gazza2]

ok i have found some code that works in the demo but cant seem to get it to work in my db

Heres what i have

table1(customers)
field 1 = accountcode
field 2 = customername
field 3 = address

form1
txtbox1 = txtsearch
txtbox2 = txtfilter
lstbox = lstdata

i have the following sql statement in the lstdata rowsourse

SELECT customers.accountcode, customers.customername FROM customers WHERE (((customers.accountcode) like forms!form1!txtfilter));

and the following code in the on change event of txtsearch

Me.txtfilter = Me.txtsearch.Text
Me.lstdata.Requery

This works fine in the demo but when i apply it to my db it tells me to enter parameter value then puts whatever i type into the parameter value box into the account code column in the listbox with the different customername value
in the next column.

Can anyone spot where it has all gone pear shaped.

Thanks in advance.

 

[DCrake]

Simple Software Solutions

Hi

Here is the raw code from my demo

Private Sub TxtCriteria_Change()
'Purpose: to refresh the contents of the list box based on the text entered into the TxtCriteria control

On Error Resume Next
Dim StrSql As String 'SQL statement for the record source
Dim sText As String 'Contents of the criteria control

sText = Trim(Me.TxtCriteria.Text)

'Is there any text to test?
If Not sText = "" And IsDelOrBack = False Then

StrSql = "SELECT * FROM TblmstStaffMembers " & _
"WHERE fldName Like '*" & sText & "*' ORDER BY fldname;"

'Refresh the rowsource with the new SQL
Me.LstResults.RowSource = StrSql

End If
'Requery the list box to show results
Me.LstResults.Requery

End Sub


The fields you bring through in your recordset must match the layout columns in your listbox. So if you want CustId and CustName in your listbox change your select statement to read:-

Select CustID,CustName From ....

Then in your list box set the column count to 2
set the column widths to 0cm,5cm

Set the bound column to 1

Test the results.


Code Master:

For some reason I attempt to upload my demo but it goes through the procedure but nothing is attached to the thread. Sorry.

 

[Gazza2]

thanks so far dcrake

The problem im still having is it is asking me to enter all the details in the enter parameter value box that pops up every time i touch a key.

heres your code :

Private Sub txtsearch_Change()
'Purpose: to refresh the contents of the list box based on the text entered into the TxtCriteria control

On Error Resume Next
Dim StrSql As String 'SQL statement for the record source
Dim sText As String 'Contents of the criteria control

sText = Trim(Me.txtsearch.Text)

'Is there any text to test?
If Not sText = "" And IsDelOrBack = False Then

StrSql = "SELECT accountcode, customername FROM customers " & _
"WHERE accountcode Like '*" & sText & "*' ORDER BY accountcode;"

'Refresh the rowsource with the new SQL
Me.lstdata.RowSource = StrSql

End If
'Requery the list box to show results
Me.lstdata.Requery

End Sub

Ive highlighted the parts that ive changed to fit my db.

All i need now is for the parameter value box to stop popping up but i dont know where to look in the code for the problem.

Thanks
Gazza

 

[DCrake]

Simple Software Solutions

Hi

Have you tried to step through the code to see which line is generating the dialog box?

If you are not familiar with this then go to your code and click on the left hand of your code until a brown highlight appears. Do this on the

Sub TxtSearch_Change()

Then try again. You code will stop at this line. Press F8 to step through the code one line at a time you will then be able to find out at what point you are being asked for the parameter.

Code Master:

 

[Gazza2]

thanks for the info on problem solving code, couldnt quite get it to work but it is something i will look into when i get time.

the problem code is this line

"WHERE accountcode Like '*" & sText & "*' ORDER BY accountcode;"

the parameter value is asking me for accountcode and i know that this field exists in my table so i dont really understand why it is asking me for this.

thanks

 

[DCrake]

Simple Software Solutions

Hi

The next step is when you get to the line that is generating the error...

Highlight the line and Ctrl+C

Create a new query but dont pick a table
Click on the SQL view and Ctrl+V

Next switch view to Design mode

If there is an error it will be easier to detect. It may be spacing or something like that.

Let me know how you get on.

code Master:

 

[Gazza2]

ok I tried what you said and i get an error when trying to save it.

Ive attached a picture of the error message.

Hope you can spot the error cause my eyes and brain have lost the will to do anything.

Thanks for the help and patience.

 

[Oldsoftboss]

Try downloading the sample example I posted here...

http://www.access-programmers.co.uk/forums/showthread.php?t=138165

 

[Gazza2]

It works (yippee)

That worked a treat thanks Oldsoftboss

And also thanks to Dcrake for all your help and patience.

Also without seeming rude (im still learning access) could one of you explain the difference in the code you both supplied.

Thanks

 

[Oldsoftboss]

Dcrake's uses code written in VBA and supplies an SQL line (SQL is the same as a query, except a query is saved) and reloads the listbox each time, where as the sample I have uses very little code, just uses the "*" (wildcard) in a query and refreshes the list box after each key press.

Dave

 

[Gazza2]

Search form extension

Now i have the search form working and i have added a sequence to the results.
I have a text box at the bottom of the form and i want to enter the number of the customer and have it open in another form.

The code i have on the afterupdate of the text box is :

If Text23.Text = Me.lstdata.Column(1) Then
Me.txtsearch2.SetFocus
stLinkCriteria = "[txtsearch2]=" & "'" & Me![AccountCode] & "'"
DoCmd.OpenForm FrmWorkfile1, , , stLinkCriteria
End If

But this is not working. any suggestions on where im going wrong.

Thanks
Gareth

 

[Oldsoftboss]

Where do you get the data for Me![AccountCode] ?

The search code refers to Text23.Text and Me.lstdata.Column(1)


If Text23.Text = Me.lstdata.Column(1) Then
Me.txtsearch2.SetFocus

stLinkCriteria = "[txtsearch2]=" & "'" & Me![AccountCode] & "'"
DoCmd.OpenForm FrmWorkfile1, , , stLinkCriteria

Does the code open the second second form?

 

[Gazza2]

me![accountcode] comes from tblcustomers.

I have added a copy of my db so you can see exactly whats going on.

Thanks