Searching in a combo box

[Glenn Glazer]

Here's what I have at the moment. I have two relevant tables, People and Memberships. People is essentially a fancy phonebook. A person is allowed to have multiple memberships, so People.ID is a foreign key into Memberships.

Now, I have in my Memberships form, a three-column combo box, with the row source property as

SELECT People.ID, People.[Last Name], People.[First Name] FROM People;

and After Update Event as

[Person ID].Value=[Person ID].Column(1

In the Memberships table, Memberships.Person ID is a lookup to People.ID

So that I don't have to remember each person's ID. OTOH, there are almost a thousand people in People and I really don't want to scroll down the combo list for each one. Is there some way I could start typing the last name and it would do the completion for me? Note that last name is not unique, but Last Name + First Name is (as it happens)

I've set the Auto Expand property of the combo box to Yes, but this doesn't seem to do anything

Thanks muchly in advance

Glenn

 

[Jeff Boyce]

Glenn

Combo boxes have an Auto Expand property. This allows you to type in the
first few characters and "go" to the first row that has that. Keep that.

The .Column() function is zero-based. If you want to be updating a textbox
with the PersonID, based on your SQL statement, you'd use
cboYourComboBox.Column(0)
to get that value. Or just cboYourComboBox.

If you have a Lookup datatype in your membership table, you will be
confused! (OK, so maybe YOU won't, but many folks get confused when they
look in a table and SEE names, but Access is actually STORING numbers/IDs
behind the scene.) And you probably shouldn't be working in the tables
anyway. Consider converting that Lookup type field to the complementary
data type to the PersonID (i.e., if PersonID is an Autonumber, make
MembershipPersonID a Long).

If you want to do the Auto Expand thing, consider changing your SQL
statement to combine LastName and First name into one field. After all, you
want to store the ID, right? In a query, select PersonID, add a new field
with [LastName] & ", " & [FirstName] to get a concatenated field you can use
in your combo box to do your lookups.

 

[Glenn Glazer]

Hi, Jeff.

Thanks for getting back to me, I think we are a little closer.

I now have tried:

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

and the other way around:

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

(for some reason it started putting the [] around the Column, I have no idea why).

So, in the first case, Auto Expand doesn't work, but when I select a row, Access accepts it. In the second case, the Auto Complete works, but Access complains that I'm trying to put text in a numeric field. I've also noticed that the column index seems to have no effect - in the second case, 0 and 1 and even 2 have the same result.

I also turned the lookup field in the table back to a normal long integer field, but this did not seem to have any effect.

So now I'm completely confused. Any ideas?

Best,

Glenn

----- Jeff Boyce wrote: -----

Glenn

Combo boxes have an Auto Expand property. This allows you to type in the
first few characters and "go" to the first row that has that. Keep that.

The .Column() function is zero-based. If you want to be updating a textbox
with the PersonID, based on your SQL statement, you'd use
cboYourComboBox.Column(0)
to get that value. Or just cboYourComboBox.

If you have a Lookup datatype in your membership table, you will be
confused! (OK, so maybe YOU won't, but many folks get confused when they
look in a table and SEE names, but Access is actually STORING numbers/IDs
behind the scene.) And you probably shouldn't be working in the tables
anyway. Consider converting that Lookup type field to the complementary
data type to the PersonID (i.e., if PersonID is an Autonumber, make
MembershipPersonID a Long).

If you want to do the Auto Expand thing, consider changing your SQL
statement to combine LastName and First name into one field. After all, you
want to store the ID, right? In a query, select PersonID, add a new field
with [LastName] & ", " & [FirstName] to get a concatenated field you can use
in your combo box to do your lookups.

 

[Jeff Boyce]

Glenn

I may be more confused. Your Membership table has "names" in it?

To handle a many-to-many situation, you need three tables. If one person
can hold many member in multiple groups, and each group can be associated
with multiple persons (i.e., members), you need a Person table, a Group
table, and a Membership table.

I don't understand how you have a "Membership" table with MemberID and no
PersonID -- but it sounds like you do and are trying to update that table to
include PersonID. So, if you have more than one John Doe, how do you know
which PersonID to use in the Membership table?

Perhaps a brief list of example data would clarify this further? (or maybe
someone else following this thread has other ideas...?)

 

[Glenn Glazer]

Hi, Jeff

Thanks for hanging in here with me, I appreciate it. I'd attach a schema image, but I can't see how to do that in the BBS, so we'll have to be content with ASCII art. :

Here's the idea graphically

Peopl
-------

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


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

So, let us say that Joe Smith has People ID 1. As I enter the record with membership number, say 27, I want to lookup Smith, Joe in the combo box and have it insert a "1" in Membership.People ID in that record. Now, each person can have zero or more memberships, but each membership must belong to a single individual, thus, we have in the memberships table (numbers made up)

Membership ID People I
27
28
29
30
31
32

and so on. An expected query is going to be "Give me the names of all people with membership numbers > x" and so I will join on the Memberships.People ID to do this

Let me know if this is still unclear

Best

Glen

----- Jeff Boyce wrote: ----

Glen

I may be more confused. Your Membership table has "names" in it

To handle a many-to-many situation, you need three tables. If one perso
can hold many member in multiple groups, and each group can be associate
with multiple persons (i.e., members), you need a Person table, a Grou
table, and a Membership table

I don't understand how you have a "Membership" table with MemberID and n
PersonID -- but it sounds like you do and are trying to update that table t
include PersonID. So, if you have more than one John Doe, how do you kno
which PersonID to use in the Membership table

Perhaps a brief list of example data would clarify this further? (or mayb
someone else following this thread has other ideas...?