RowSource by Code

[Swagener]

HI there,

I have a form which consists of txt fields and a list box, currently
the rowsource of the list box contains SQL in which the criteria points
back to the forms txtbox's with wildcards on both sides
( like "*" & form!search!surname & "*") but when I used to open the
form it took a long time to open so I decided to leave the
listbox.rowsource blank and made a button whose onclick event procedure
looks like as below but nothing happens when click the button.
But the same sql was used earlier in the rowsource of the listbox, i
tried to test the sql by removing the where clause and it works fine
and populates all the records in the list box from the table but as
soon as i add the where clause it stops working.

`````````````````start of code`````````````````
Private Sub cmdsearchcust_Click()
Dim stroesource As String

stroesource = "SELECT [SO Cust Master].cInitials, [SO Cust
Master].cSurname, [SO Cust Master].houseNumber, [SO Cust
Master].addrLine1, [SO Cust Master].postCode, [SO Cust
Master].DMBarcode FROM [SO Cust Master]
WHERE
((([SO Cust Master].cInitials) Like " * " &
[Forms]![FRMCUSTDetailSearch]![Txtsearchinitials] & " * ")
AND
(([SO Cust Master].cSurname) Like " * " &
[Forms]![FRMCUSTDetailSearch]![TXTSearch] & " * ") ;"

Forms!frmcustdetailsearch!List3.RowSource = stroesource
End Sub
````````````````````````````end of code``````````````````````

any help will be much apprieciated.

thanks in advance !

 

[chris]

Hi Swagener,

At the very least, you need another ) (close parenthesis) on the end of
your Where clause. I count it as having 3 nested levels at most, and
you don't close the last one off.

Can you try that first?

Chris.

 

[Swagener]

Hello Chris,

I have managed to get it working after 3 hours painful try and test.

Now at the end of sql there is a order by clause for a unindexed field
..
Is there any way of speeding it up

 

[Chris]

Hi,

First of all, is it possible to index that field? That might be the
easiest way to get a speed increase.

You can use recordsets with listboxes, so you could get all the data
into a recordset and then perform the sort. You would then set your
sorted recordset to be the listbox's recordset. Are you familiar with
recordsets? Do you use them in your program (and if so, DAO or ADO)?

Would the form be used once, or many times? I.e. would your user type
in one item, get some matching records and that's it, or would they
then type in different items again and again?

Are you dealing with thousands of records? And have you compact and
repaired the database recently?

Chris.

 

[Swagener]

Thanks for the quick responses first of all.

Now the database has about 300 thousand records and it is a form that
will be used frequently and the may type in again and again to narrow
down the search after that they can select a record and open a form
with relevant data filtered.
I did index the field but it is still slow, as far as recordset is
concerend i,m not very good at it.

Is there any chance you could help me with the code?

Current code i,m using..

``````````````````````````start of code````````````````````````````````
Private Sub cmdsearchcust_Click()
Dim stroesource As String

stroesource = "SELECT [SO Cust Master].cInitials, [SO Cust
Master].cSurname, [SO Cust Master].houseNumber, [SO Cust
Master].addrLine1, [SO Cust Master].postCode, [SO Cust
Master].DMBarcode FROM [SO Cust Master] WHERE ((([SO Cust
Master].cInitials) Like ""*"" &
[Forms]![FRMCUSTDetailSearch]![Txtsearchinitials] & ""*"") AND (([SO
Cust Master].cSurname) Like ""*"" &
[Forms]![FRMCUSTDetailSearch]![TXTSearch] & ""*"") AND (([SO Cust
Master].addrLine1) Like ""*"" &
[Forms]![FRMCUSTDetailSearch]![txtsearchRoadnameadd1] & ""*"") AND
(([SO Cust Master].postCode) Like ""*""&
[Forms]![FRMCUSTDetailSearch]![TxtsearchPostcode] & ""*"") AND (([SO
Cust Master].DMBarcode) Like ""*"" &
[Forms]![FRMCUSTDetailSearch]![txtsearchDMBarcode] & ""*""));"


Forms!frmcustdetailsearch!List3.RowSource = stroesource

End Sub
``````````````````````````end of code````````````````````````

 

[Chris]

Hi,

If you're not familiar with recordsets then I'd read up about it and
see if you can figure how they'd fit into your application. I don't
know if this would be quicker though.

As for speeding up the SQL, if you remove some/most of the fields from
the where statement does it speed up the query? If so, you could
dynamically build your string dependant on text being entered into the
boxes.

e.g. For each text box, see if it's got something in and if so add it
into a string variable.
if txtSearchInitials <> vbNullString then sWhereString = sWhereString &
" AND ([Forms]![FRMCUSTDetailSearch]![Txtsearchinitials] & ""*"")"

When you're done with the textboxes add the where onto the end of your
SQL and run it. f you do this method make sure each route through the
IF statements will produce a valid string.

However, cut down the Where clause first to see if this might help you.
Of course, if your user enters data in each textbox then it still
won't help speed it up.

Chris.

 

[Swagener]

thanks chris ,

I will try further to speed up things.