Forum Home
Press F1
Thread ID: 66212	2006-02-15 04:09:00	MS Excel to display DB names similiar to Access	John W (523)	Press F1

Post ID	Timestamp	Content	User
430533	2006-02-15 04:09:00	Hi there. We all know MS Excel & Access are similiar beasts, so I was wondering if their was a way in Excel, to continually burrow down a surname database as you typed in the characters, displaying only those that meet the typed in critera. We have some PC shy staff, who love this facility in another program and wonder if it possible to do the same in excel. We use Excel for creating a jobcard from a selected client "record" where they currently have to use Ctrl + F to find the client. We use Excel to do so much more at the end of the day, some Im lothe to discard it. 15yrs of familiarity with Excel and 6mths with Access being the handicap at present. Thanks John in Mosgiel	John W (523)
430534	2006-02-15 05:53:00	Ummm .... many, many years ago I did something like this using VBA in Excel. From what I remember, it loaded the Access data (like your surname list), pre-sorted with a SQL select into an array. There was a textbox and a listbox(?) in the Excel sheet. The keypress event would fire a search on the array and place the appropriate results into the listbox. Selecting an item in the listbox would then hit db again and return the appropriate information. I think it was always rather slow, as you could type into the textbox faster than the listbox would be able to update - but this was 5+ years ago, and my skills and processor speed were far worse back then.	Antmannz (6583)
430535	2006-02-16 01:31:00	Similar to what Antmannz said, You could popup a Userform with a Listbox populated with all the clients names from the Excel database (preferrably sorted) and when the required name is clicked in the Listbox it can initiate the next desired event eg inputting client details into your form from fields in the database etc. The Userform would need to be designed and macro code written to populate the Listbox, and macro code written to detect the name clicked and then what to do next.	rad_s4 (7401)
430536	2006-02-16 03:10:00	Hi, VBA combo or listbox controls have a property MatchEntry which determines this behaviour unlike cells in a sheet. A pop-up form might be what your after as rad_s4 suggested. The issue with a sheet is at which point a cell is considered changed compared to when a combo or drop down box is changed. With code you can monitor cell changes in a sheet and do whizzy things depending upon whats entered but unfortunately this only kicks in when you press enter to confirm what you have typed in the cell - this is too late because you want to monitor whats been typed as you enter each character not when you've finished typing. Thats the difference between what you can do with a control compared to a cell. There is an AutoComplete type behaviour inherent in Excel where it will select text based on whats been entered in the cells above but is probably not what they require. hth	Parry (5696)
1