Run a query from a form ?

A

al9315

I have a form with a text box and a list box. Can I get user to enter text in
textbox, then search a table for all instances of text entered and add each
instance to the list box ? Not quite sure how to go about this ?
Thank you
Al
 
G

Graham R Seach

Al,

Yes, you can, but in order to give you something you can use, you'll need to
enlighten us in several ways:

1. What is the name and structure of each table.
2. What the TextBox's name?
3. What's the ListBox's name, and where does it get its data from?
4. How do you intend to trigger this action (clicking a button, exiting
the TextBox?)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
A

al9315

Wow - great - thanks for helping !!!
The database is for my record / CD collection
1) The table to be searched is called "Tracks", so there may be 2,3....
instances
of the same track on different discs
2) The text box on the form is called "FindTrack"
3) The list box is called "TracksFound"
4) I would like it to work by clicking a cmd button
I suppose I could use the Input msgBox, but I would much prefer to enter:-
Track to look for e.g. " Merry Xmas" and that text would stay in
"Findtrack" then all instances e.g. "We wish you a Merry Xmas" appear in the
list box below
Hope that is enough info, the people on here have been 'fantastic', I
started clueless, and have a working database now, with pictures etc
cheers to all the pople who help !
Al
 
G

Graham R Seach

Al,

1. Change the ListBox's RowSourceType property to "Value List".

2. Then add the following code to the button's Click() event:
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sValue As String

Set db = CurrentDb
sSQL = "SELECT TrackName FROM Tracks " & _
"WHERE TrackName LIKE ""*" & Me!FindTrack & "*"""
Set rs =db.OpenRecordset(sSQL, dbOpenSnapshot)
Do While Not rs.EOF
sValue = sValue & Me!TracksFound,RowSource & ";"
rs.MoveNext
Loop

Me!TracksFound.RowSource = Left(sValue, Len(sValue) - 1)

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
A

al9315

Hi Graham,
Thanks for the code - I have spent 4.5 hours trying to get it all to work !
I cannot profess to understand it all, I did mess about with Visual Basic 3
!! for a year or so....
When I enter my text in the textbox and 'click', scroll bars appear in the
list box - but no text
I put in a few Debug.? ..... :-
1) A counter x = x+1, the result was, it is finding the correct number of
instances of the text entered
2) sValue just returns ;;;; - the number of times the text appears in the
Tracks Table
3) If there is no instance of the text in the table an error - invalid
procedure call or argument appears, I take it that is because 0 -1 = error ?
in line Me!TracksFound.Rowsource = Left.................. ?

I did try AddItem.TrackName but got nowhere, just errors ?! :-(
Shame to get so close !!!

Al
 
A

al9315

Sad admission Graham !!? - 9 hours to get it to work ! -learnt a lot in
process though.
There were two code errors:-
1) sValue=sValue & Me!TracksFound,Rowsource - should have been Found.Row.....
2) sValue = sValue & Me!FindTrack & ";"

Took me ages to find 2nd error, but read lots about Rowsource etc.
Thank you very much for writing code, it is extremely clever, however, I do
find it quite awkward to get to grips with at the moment, after VB
I won't give up though, and you have possibly sold another book
Regards,
Al
 
G

Graham R Seach

Geez, I don't know what happened there; must be fat fingers! I wrote it off
the top of my head, and testing would have highlighted the errors. But
you'll notice how I've modified the code even further in the code segment
below.

Do While Not rs.EOF
sValue = sValue & rs!TrackName & ";"
rs.MoveNext
Loop

Me!TracksFound.RowSource = Left(sValue, Len(sValue) - 1)

This essentially does the same thing as you would in VB, using
TracksFound.AddItem, but since Access ListBoxes don't have an AddItem
method, you have to add data to its RowSource property. The last line above,
is what actually assigns the value to the ListBox, but it also removes the
trailing semi-colon, which if left in place, would add a blank line at the
end of the list.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
A

al9315

Graham
"YES !!!! - it works
I should think I have spent 20 hours or more !? :-( trying to fathom it out,
but now it works it has made my day !!!!
I do find the coding very complex, not easy to know how to best do things,
but when it works - Wow!!!
Thanks again
Al
 
Top