Displaying Search Results

hi everyone...

I have a big problem with my VB project that I'm doing now...

first of all I'm doing a very basic database entery for a call center...to record the clients information that we speak with, so I made a form with text entery (mostly like name, phone numbers...etc)
my programme saves perfectly to the database file.
my problem is in searching. I used this code for searching :


the problem is that it does'nt work and I get nothing, all I get is that my form clears up
now what I realy would love to do is that I need to know the best way of displaying a list of results after a search has been performed.
As my application matches records to my search criteria, I would like to list these in a List Box that I have already created and called "lstSearch" and the results should be listed by date "txtDate" so that I can choose from the date and when I press on that date it would fill in my form.
is this possible to do or am I dreaming?

thanks in advance...
peace to all...

Try using % instead of * for the wildcard character.

as I know % is for sql...and I'm not using sql here

Try to Change "=" to "like"...
"SELECT * FROM tblTransaction WHERE NomM like *" & txtMNom.Text & "*' or TelMaison like & mtxtTelMaison.Text & "'"

nop, not working still. plus I still don't know how to displaying a list of results in my List Box ?

What do you mean you're not using sql? it certainly looks like sql syntax to me... please clarify that statement.

as far as displaying records in a listbox - all you have to do is loop from 0 to .eof, and use the listbox.list.add(YourIndex) = rsrecordset("YourField")

as far as I know I'm not using sql I'm using a normal database access 2000 file and the data is stored in it.

about the list box, do I put this loop in the listbox sub or in the search sub...? and if you can write the exact code that would be great

I'm sorry if I ask stupid questions but I'm new in all of this...

OK... that's fine. Let me clear something up for you,though. You're partially right - you're not using SQL Server, but you are using Structured Query Language (SQL) in order to access (no pun intended) the Access database. By setting your recordset.source = a string and having it execute that string, VB Assumes that you are using SQL Syntax. I hope I'm not insulting your intelligence here... if I am then just ignore this, otherwise read on...

SQL is a language, not a true programming language, but a query language. There are many DBMS's (Database management systems) which utilize SQL syntax in order to retrieve information from the Database (in fact, to my knowledge, all of them do). Access even performs these actions behind the scenes. When you open a table, Access performs "Select * from tblYourTable" and displays the table in its GUI. When you build a query, you are graphically constructing a textual SQL Statement to select certain values from the database and display them. The general syntax of what is called the "Select" statement is...

Select [fieldnames, * wildcard for all] FROM [some table(s)] WHERE [Conditions are met]
HAVING [parameters]
GROUP BY[field to group records by]
ORDER BY[field to order records by]

Sorry, don't mean to drone on, but is important to realize that you are actually utilizing SQL statements in your program, and are querying an Access database with it.

I'm leaving work now, but I'll post a solution later.

when Querying Access 2000,sql statement can be used..U can use sqlvbcodegenerator to generate the vb code
http://www.newrad.com/software/sqlvbcodegenerator/

ok.. sorry so late - I've been working on personal projects.

Once you have your recordset in memory, you can cycle through it like so...

ok got it to work now...thanks alot...

one last question, if I want to make the more than one column to be displayed...how do I generate a space between each field ?

example :
now that's what I get:
10/9/20034:16:12PMRobertMcdonaldJohnMansfieldSonya

and here is what I what to do :
10/9/20034:16:12PM Robert Mcdonald John Mansfield Sonya

thanks again...
peace

that's a little more complicated... basically, you have to pad the fields in your listbox. You'll have to decide how long your fields are going to be... ie

Name 1-25
Address 27-55
CC Number 57-73

etc... then you have to determine the difference between your predetermined column width and the length of your string (field) on the fly, and add that number of spaces in by using the string$() function. After you've done all that, you can build your string on the fly and add it to the list box. Confused yet? It's actually not that hard, once you've wrapped your mind around the static column widths. I'll give you a word of advice - pick a monotype font, so that your characters don't throw off your column spacings.

I ask my self sometimes, why am I doing to myself all of this ? and I still can't find an answer !


look man, I know that I have asked alooooot till now, and I'm sure that you think by now that I'm the most stupid programmer on earth, but I did'nt undertand nothing of what you just said

I just want you to know one thing...
I never studied visual basic ! it's was just a hobbie for me, and now my manager is abusing this hobbie

Nah... I haven't met a stupid programmer yet. Most people wouldn't touch the internal workings of a program even if they knew that it could stop the apocalypse!

OK... here we go... Think about a printed report. In a report, you will have typespacing between column headings, and those typespaces will be statically(usually) set at design time. So, let's say that I have 4 column headings...

Full Name Spaces 1-25
Address Spaces 27-55
Date of Birth Spaces 57-70
Phone Spaces 72-85

so

Now, programatically, this says that I am only going to allow 25 spaces for the full name, so if the full name is shorter than that, then I am going to have to pad the listbox before the NEXT field. Now, I know that the next field starts on the 27th column, and that the current field ends on the 25th column, and I know the length of the current field (Full Name for example) by using the len() function. so I can do this


This allows for the proper spacing of columns, and should even things out across the width of your listbox, creating nice, clean column breaks. You can widen these by making changing your column spacings. This is very easy to mess up because you want to make sure to pick a column width that will fit the widest field you have. I usually write a function that loops through every field in a recordset and then enters an internal loop which looks for the longest string in my DB. Then I return those values. The only problem with that approach is it can take a considerable amount of processing time, depending on how many records you have.

I hope this helps.