 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / May 2008finding records faster with list box--how do i do it?
I have an Access database that is growing too large/too fast. I currently move thru the form by scrolling the records one at a time, but it's too slow. The table/form is a simple identity database: last name, first name, birth date, etc. I need a list box/combo box (or whatever would be best) on the form to show a list of the names that are in the database/table, then let the user choose that name and go directly to the record. For example, I currently start at the first letter of the last name of the person, "Adams", and have to do a lot of clicking one-by-one to get to the records that start with "Smith". There is no data entry connected to this, just to be able to see the total record. I am VERY new at this, so any directions should be simple, clear and specific. I've read thru posts looking for something similar, but can find final resolution. >The table/form is a simple identity database: last name, first name, birth >date, etc. I need a list box/combo box (or whatever would be best) on the >form to show a list of the names that are in the database/table, then let the >user choose that name and go directly to the record. A Combo Box can do this. Open your form in design view, be sure the magic wand icon on the toolbox is selected, and add a Combo Box control; choose the wizard option to "Use this combo box to find a record". If the wizard is uncooperative post back, it's pretty easy to do it manually.  SignatureJohn W. Vinson [MVP] I give some thoughts on searching here: http://www.members.shaw.ca/AlbertKallal/Search/index.html There is a sample search form in my super easy word merge that is much like the ones in the above article: http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html SignatureAlbert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com I have a ready-made Access application with a very powerful QBF (Query by Form) search form, that is tailer made for your needs. I'm willing to share a copy with you, if you send me a private e-mail message with a valid reply address. My e-mail address is available at the bottom of the contributor's page indicated below. Please do not post your e-mail address (or mine) to a newsgroup reply. Doing so will only attract the unwanted attention of spammers. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > I have an Access database that is growing too large/too fast. I currently > move thru the form by scrolling the records one at a time, but it's too slow. [quoted text clipped - 12 lines] > specific. I've read thru posts looking for something similar, but can find > final resolution. I thank everyone for their help and suggestions. After reading some of the items that were given to me AND giving it some more thought, I might have a simple solution, but I can't get it to work. First, I learned that 'large' is a relative term. My database is not anywhere near 'large' in the sense that most of you deal with. It's only a few thousand names. Second, instead of an additional box/control on the form,as I had asked about on my initial post, I realized the LASTNAME text box should really be a combo box listing all the last names in the base, so the user can skip directly to a certain last name then go thru the records one by one. I've been able to place the combo box but can't it it formatted to list the names. For example, the current form shows the last name in a text box. I can move to the next record in the table by using the scroll buttons. My test combo box will only show the last name of the current record, with no list of names only blank. There are scrolling buttons that operate but show nothing when used. The wizard walked me through the set up so I didn't think I would have any problems. I have 23 columns in my table. #1 is Contact ID (primary key) and column #2 is Last Name. I have Column Count 2, Column Width 0;.8 and Bound Column is 1. I have Control Source as LastName (the name of the field). How do I get a LIST to appear? And how will it go to that record in the table? Hello - > Second, instead of an additional box/control on the form,as I had asked > about on my initial post, I realized the LASTNAME text box should really be > a combo box listing all the last names in the base, so the user can skip > directly to a certain last name then go thru the records one by one. I've > been able to place the combo box but can't it it formatted to list the names. Try this tutorial: Using A Combo Box To Find A Record http://www.access.qbuilt.com/html/find_a_record.html However, that said, I'm not sure you really want a combo box that includes a few thousand entries. You would probably be better off using an unbound text box. If you are willing to consider using some VBA code, you can create a powerful QBF (Query by Form) search form that I think you would be much happier using. You might want to try this tutorial to see this technique in action, using the sample Northwind database: http://www.seattleaccess.org/downloads.htm Look for the February 12, 2008 download: "Query By Form - Multi Select" If you've never dealt with VBA code, well, no time like the present to give it a try. Take a look at these two resources if you want some background material: DAO - Back To Basics Compilation/Demo by Tom Wickerath, Jan/Feb 2007 (Available on the same downloads page as the above sample) and Access Basics, written by Access MVP Crystal http://www.accessmvp.com/Strive4Peace/Index.htm The QBF technique I cover in the first download is one of my favorite things to implement in databases. I'm willing to make a pledge to help you get it working (no charge, of course), by private e-mail exchanges, if you want to explore this method. > I have 23 columns in my table. #1 is Contact ID (primary key) and column #2 > is Last Name. > I have Column Count 2, Column Width 0;.8 and Bound Column is 1. I have > Control Source as LastName (the name of the field). A combo box that is used to assist the user with finding records is unbound. An unbound control does not have a field specified as the Control Source--this property is left blank. A combo box that is used to assist the user with entering data, by allowing them to pick a value from a list, is generally bound. In that case, the field specified as the Control Source *MUST* be a compatible data type with the bound column. Your bound column is column 1, which corresponds to the first column of width 0 (hidden from the user). You didn't mention what the Row Source is for your combo box, but if you want to use a bound combo box, to assist with data entry, then the first field specified in the Row Source would be the bound column (as long as you don't change the bound column property). However, I'm thinking you don't want a bound combo box in any case. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > I thank everyone for their help and suggestions. After reading some of the > items that were given to me AND giving it some more thought, I might have a [quoted text clipped - 24 lines] > How do I get a LIST to appear? And how will it go to that record in the > table? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.