Full Version: Rolodex Style Form
UtterAccess Forums > Microsoft® Access > Access Forms
Ramdryve
Heyas all. frown.gif the project I've been working on for the last few weeks is finally in operation! I got the old data transferred, and the db split into fe and be, but..... there are some 5000 records that display when we open the master listing of customers. What I would like to do, is have nothing come up, then, as the user clicks buttons like (0-9) (a) (b) etc. . . . have the form pull only the records that start with those characters, and still be able to search the results.
urrently, I have the form based on a query, with Like ( "*" & Forms.CustomerList.LName & "*"), with a me.requery in the search button's onclick().
anyone have any ideas? I would be most grateful as it's taking *forever* to load the customer list, and this is a heavily used form.... everyone likes the system, but it's *slow*.
TIA!
cheekybuddha
What is the SQL of the query you use?
Why not just set the form's RecordSource when a button is clicked?
Create a sub in your form's module:
CODE
Private Sub sSetRecordSource(strStartsWith As String)
    Dim strSQL As String
    strSQL = "Select * From CustomerList Where LName Like " & strStartsWith & "* Order By LName;"
    Me.RecordSource = strSQL
End Sub

Then for button 'a' you put in it's OnClick event:
CODE
    sSetRecordSource "a"

(You will have to repeat for each button!)
Then, clear the RecordSource of your form in design view.
When you open the form it will contain no records until one of the buttons is clicked.
hth,
d
jmcwk
OR, See Attached
Ramdryve
You guys are GODS!
Thank you sooooo much. just having a small issue, not quite sure what I'm doing with select statements, so rather than risk the database, I thought I would ask.
in the form's module I have
CODE
Dim strSQL As String
strSQL = "Select * From CompanyInfo Where [Company Name] Like " & strStartsWith & "* Order By LName;"
Me.RecordSource = strSQL

However, when I run the code behind my test button, (a), I get:
Syntax Error (missing operator) in query expression '[Company Name] Like a*'.
Ideas? I havent used select statements before.....
TIA!
jmcwk
You and me both Tia I am not good with it either however try:
CODE
Dim strSQL As String
strSQL = "Select * From CompanyInfo Where [Company Name] Like " & strStartsWith & "* Order By Company Name;"
Me.RecordSource = strSQL

also as a sidenote it is not good practice to have field names with spaces creates problems in the longrun
Ramdryve
OK, I found one issue, stupid me.....
im strSQL As String
strSQL = "Select * From CompanyInfo Where [Company Name] Like " & strStartsWith & "* Order By [Company Name];"
Me.RecordSource = strSQL
but it still gives the same error. I love this Me.Recordsource, thank you sooooo much!
TIA Again
Ramdryve
LOL! we must have crossed our replies in the ether. frown.gif
Trying yours now....
TIA
jmcwk
How bout
CODE
  Dim strSQL As String
strSQL = "Select * From CompanyInfo Where me.Company Name Like " & strStartsWith & "* Order By me.Company Name;"
Me.RecordSource = strSQL
Ramdryve
hrm. No Go.
ame error as above....
Has far as the space in the name, I know. It's a little mistake I made when setting up the tables that didnt get caught when I was doing my testing. Now, what [censored] is that fact that it's too late to change it...
Thank the great architect that it's the only one, LOL
TIA, yet again.
Ramdryve
why did it censor B U G s me?
OL
ejstefl
Or this:
im strSQL As String
strSQL = "Select * From CompanyInfo Where [Company Name] Like '" & strStartsWith & "*' Order By [Company Name];"
Me.RecordSource = strSQL
You need to add quotes around text fields.
Ramdryve
THANK YOU!!!!!!!!!!!!!!!!!
ever so much.
cheekybuddha
Oops! Sorry, forgot the quotes blush.gif , glad you got there!
Ramdryve
For what it's worth, I now have it working beautifully. The only problem, was that I was getting #Name? in the fields displayed on the form, but I solved that: I simply placed a sSetRecordSource "a" in the forms OnOpen() event and Voila! the perfect form. Now, after lunch, I am going to make the system be able to search based on that same- and thereby totally eliminate the cruddy old list that I have!
Thank you guys, ever so much!
cheekybuddha
Charles, enjoy! :thumup:
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.