Searching with a combo box (repost/rewrite)

[Guest]

I think this thread got lost a little in the passage of time and new postings. So, I'll try to compose most of the thread together in this note and hope for the best

The current setup
---------------------

Two tables, People and Memberships. I am only showing the relevant fields, there are others in each table

People Membership
-------- ---------------
ID Membership I
First Name Person ID (as a foreign key
Last Nam

One form, called MembershipsForm. All data entry is going on in the form. I am not editing the tables directly

The combo box on MembershipForms for the field Memberships.Person ID currently has the following relevant controls

Row Source: SELECT People.ID, [Last Name] & ", " & [First Name] FROM People;
After Update Event: =[Person ID].[Value]=[Person ID
Auto Expand: Ye

A graphical view of the tables
----------------------------------

Peopl
-------

ID First Name Middle Name Last Name ...other fields //this data is already entered, all names have ID


|
|------------------- Membership
| --------------
\
Membership ID Person ID ....other field

What I would like to do
---------------------------

When I enter the combo box, I would like to begin typing the first few letters of the person's last name as the hint to the auto expand. When I type in enough letters to uniquely identify the name, I want to hit return (and here's the tricky part) I want the ID to be entered into the Memberships.Person ID field. That is, I want to search on one (combined) field, but enter another field of the same record

What currently happens
---------------------------

It auto expands on People.ID

What doesn't work
---------------------

Row Source: SELECT [Last Name] & ", " & [First Name] , People.ID FROM People
After Update Event: =[Person ID].[Value]=[Person ID].[Column](1

This causes Access to try and put the name in the field, causing a type mismatch error. I have tried values of 0,1 and 2 to the Column function, with no change

Example Usage
-----------------

Do queries based on membership number and join on the ID field, producing address book data for the corresponding membership number.

TIA

Glenn

 

[Albert D. Kallal]

Make the first column of your combo box a ID field, then the 2nd field can
be lastname. You "can" concatenate the 2nd column, and have it LastName +
first name..but the problem is that indexing can't use that ...so it will be
slow. If your data file is only 1 or 2 thousands names..then this likely
does not matter.

In fact, try creating your combo box with the wizard. Choose ID, and
LastName. Get that working..and then consider modifying the sql of the combo
box. (just always make sure the 1st column is the id. So, just to note, the
wizard will create a combo box that returns the id, but searches by
LastName.

 

[Guest]

WE HAVE A SOLUTION!!!

Thanks so much for the suggestion, Albert. Here's what I have, I don't really understand what is different or why it works now, but it does. There must be some magic in the wizard, as it were. Which value is it that controls (as you put it) the return value

Row Source: SELECT People.ID, People.[Last Name] & ", " & [First Name] FROM People ORDER BY [Last Name], [First Name], [ID];

Control Source: Person I

Column Widths: 0";1.5";1.5

BTW, I do not expect to have more than three or four thousand names, so I'm willing to deal with the slowness if and when that happens

Again, many thanks - domo arigato gozaimashita

Best

Glen

----- Albert D. Kallal wrote: ----

Make the first column of your combo box a ID field, then the 2nd field ca
be lastname. You "can" concatenate the 2nd column, and have it LastName
first name..but the problem is that indexing can't use that ...so it will b
slow. If your data file is only 1 or 2 thousands names..then this likel
does not matter

In fact, try creating your combo box with the wizard. Choose ID, an
LastName. Get that working..and then consider modifying the sql of the comb
box. (just always make sure the 1st column is the id. So, just to note, th
wizard will create a combo box that returns the id, but searches b
LastName


-
Albert D. Kallal (MVP
Edmonton, Alberta Canad
(e-mail address removed)
http://www.attcanada.net/~kallal.ms