Search code flawed????

[thetambarineman]

Hello all,

OK-I should probably start off by explaining the situation- basically i have several forms. - One search form, and an employer form are the ones that im concerned with at the minute- Previously i had code that searched a the database - ive lost it now!! it wasnt working properly though and a guy from tek-tips helped me out - thanx Ruairi!! the following is the code which he supplied me with-

Sub systemsearch(sysdata As Data, sysfield As String, syssearchdata As String, syssearchlist As ListBox)

recsfound = 0
sysdata.Recordset.MoveFirst

Do Until sysdata.Recordset.EOF

If InStr(UCase(sysdata.Recordset(sysfield)), UCase(syssearchdata)) Then

recsfound = recsfound + 1

syssearchlist.AddItem sysdata.Recordset(sysfield)

End If

sysdata.Recordset.MoveNext
Loop

If recsfound = 0 Then MsgBox "No records found, please refine your search.."

End Sub

The search is fine- however - when i go to say the employer form- the record is not pointed at the correct record!! - in fact theres nothing in the txt boxes..

Can anyone suggest a way (other than sql) of remedying this situation...

Excuse the spelling..!!

Thanks in advance...

Paul...

 

[thetambarineman]

looking at the problem - ive realised/am assuming one thing- apart from my own stupidity- that is that the problem lies somewhere in the parameters of the sub routine.. this is module code - and the other forms that i talked of are separate - when i tried to debug it i got a variable or with block not set.. Has this helped any??

and yes i do realise that im thick!!!!

Any help is greatly appreciated!!

Paul...

 

[edderic]

Public Sub ! (and not Sub )

Or

Dim SQL as String
Dim Result as string

SQL = "Select * From tbale Where = ' " & txtText & "'"
Set RS = DB.Openrecordset(SQL,DBopendynaset)

Do while not Rs.Eof
Result = Rs!field
listbox1.Additem Result
Rs.MoveNext
Loop





Eric De Decker
vbg.be@vbgroup.nl

Licence And Copy Protection AxtiveX.

Download Demo version on my Site:
Promotions before 02/28/2001 (free source codebook),visite my site

http://www.createsoftware2000.com

 

[thetambarineman]

Thanx for your reply - i had wanted to avoid sql - is there any other way around this problem using the existing code- i dont want to hack it up to much!!! like i said i think that its something to do with the sub routines parameters..

Thanks in advance...

Paul..

 

[edderic]

The Error :variable or with block not set

Is that you declare the recordset not public with shared with other Forms

That's you problem...
Eric De Decker
vbg.be@vbgroup.nl

Licence And Copy Protection AxtiveX.

Download Demo version on my Site:
Promotions before 02/28/2001 (free source codebook),visite my site

http://www.createsoftware2000.com

 

[Snaggs]

If you're working between forms and using different recordsets, be sure you pass the Employee recordset when you're on the employee form. You probably copied and pasted the code from one form to another and it's trying to pass a recordset that's not instantiated or doesn't exist. Check the code on the calling form. That seems to be the most likely place for the error. The code in the routine looks fine and should not be a problem. It's what you're passing to the routine is where the problem appears to be. Snaggs
tribesaddict@swbell.net
Enter any 11-digit prime number to continue...

 

[thetambarineman]

im not sure that i understand what either of you mean by passing the recordset...

Any other suggestions???

Thanks in advance...

Paul...

 

[Snaggs]

sysData in the function header is where the data control is passsed in. The data control has an SQL statement associated with it, which is what the code is using to base the search on.

Be sure you pass the correct data control to the routine when you call it. Snaggs
tribesaddict@swbell.net
2 wire mesh butchering gloves:
1 5-finger, 1 3-finger, pair: $15

 

[thetambarineman]

this is what im using to call the code...

systemsearch "employer_name", txtsearchemp, Listemp

ive now revised the module code to this:

Public Sub systemsearch(sysfield As String, syssearchdata As String, syssearchlist As ListBox)

recsfound = 0
frmemployer.dbemployer.Recordset.MoveFirst

Do Until frmemployer.dbemployer.Recordset.EOF
If InStr(UCase(frmemployer.dbemployer.Recordset(sysfield)), UCase(syssearchdata)) Then
recsfound = recsfound + 1
syssearchlist.AddItem frmemployer.dbemployer.Recordset(sysfield)

End If
frmemployer.dbemployer.Recordset.MoveNext
Loop

If recsfound = 0 Then MsgBox "No Records Found, please refine your search.."

End Sub

Am i digging a bigger hole for myself or am i just plain thick??

Thanx

Paul

 

[Snaggs]

Paul,

I admire you're thought process on this! But let me offer a bit of suggestion. First, the code that Ruairi wrote for you orignally looked like it was good code. Let me state why and then it might shed some light on the subject.

When he used the "sysData As Data" parameter in the function header, he did this for several reasons... and good ones at that.

1. By coding the function this way it allows you to pass a different data control to the function without having to have special code in the routine for each data control you might pass to it.

2. He loosly coupled the code. This means that the code is more reusable than the code that you're presenting here. The reason it's loosly coupled is that it doesn't make any external references to controls on a specific form. It uses the parameter that was passed in locally as the reference. When you added the line: frmemployer.dbemployer.Recordset(sysfield)) to the routine you tightly coupled the code the a data control called dbEmployer on a form called frmEmployer. Ruairi's code didn't do this.

I will base my response on the routine that he wrote and try to explain why his method is a better approach.

First, it's more flexable, here's why. You can pass ANY data control to the routine and the routine doesn't care. So looking at his function header of:

Sub systemsearch(sysdata As Data, sysfield As String, syssearchdata As String, syssearchlist As ListBox)

You would call the function from the frmEmployer form like this:

SystemSearch dbEmployer, "Employer_Name", txtSearchEmp.Text, lstEmployerMatch

The reason this works out so nice is that you can change all three parameters that are passed to the function and you get the same search results, just on a different data control!

Currently the way the code is written, the routine is tied (tightly coupled) to the frmEmployer form and can't be used in any other fashion. This is a big problem with tightly coupled code.

If you look at the code that Ruairi wrote, you'll notice that there are no external references to controls or forms within his code. This is what makes it so portable and reusable. If you wanted to reuse the code you currently have, you'd have to copy and paste it. Rename the routine and change the form name and data control name in the routine. Now you'd have two very similar routines that did exactly the same thing, but you'd have twice the maintenence! If you made a bug fix to one of them, you'd have to remember to make the fix to the other one too. Even worse, if you had 4 or 5 copies of the routine, it would be a real headache to maintain.

If you had an Employee form you could do this:

SystemSearch dbEmployee, "Employee_Name", txtSearchEmployee.Text, lstEmployeeMatch

If you had a Customer form you could do this:

SystemSearch dbCustomer, "Customer_Name", txtSearchCustomer.Text, lstCustomerMatch

If you had a Book form you could do this:

SystemSearch dbBooks, "Book_Name", txtSearchBook.Text, lstBookMatch

Do you see how portable this approach is? What he did was what I call "The fine art of factoring". He found all the things that were common to the search routine and put them INSIDE the routine. Then he determined the things that could change when the routine was called and made them PARAMETERS.

The logic for searching is going to be pretty much the same, no matter what data set you're searching in this application. The thing that will change is WHAT data you're searching on and WHERE you want the results to go. That's why the things that chage are setup as parameters in the routine header.

Hope this makes sense. Snaggs
tribesaddict@swbell.net
2 wire mesh butchering gloves:
1 5-finger, 1 3-finger, pair: $15