Searching in a combo box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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.
 
Glenn

I don't understand why you need two. A combo box with the first row source
can have the column width of the first column (the ID) set to zero. This
causes display of the [LastName] & ... text, which you can then use for
AutoExpand/lookup.

I see potential confusion for both you and Access in setting the value of
[PersonID] to [PersonID] -- I suspect you have a text control named
[PersonID] and a table field named [PersonID]. Which is which?

Access is adding in the square brackets because a space (or special #(^*$
character) is ambiguous and confusing to it. If you must use spaces in your
field names, put the square brackets around the entire name to avoid
confusion.

Let's go back to the beginning. WHY are you trying to put the PersonID in
something, when you've just selected it (and it already is IN the combobox)?
 
Hi, Jeff

To the first point, I don't have both of the pairs I listed at once. I was just trying to show that I had tried it "both ways"

So, as you suggest, back to basics

I have a table called People. It has as fields, primary key, ID, First Name and Last Name (and others not relevant). I have a table called Memberships, which has, among other fields, Membership ID and Person ID. All of the ID fields are Long Ints and the names are Text fields.

I want to be able to search through the combo box based on the name of the person and insert that person's People.ID into Membership.[Person ID], reflecting the many-to-one relationship (a person can have multiple memberships)

I do name my form fields the same as the corresponding table field. If this is a bad practice, let me know

Best

Glen

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

Glen

I don't understand why you need two. A combo box with the first row sourc
can have the column width of the first column (the ID) set to zero. Thi
causes display of the [LastName] & ... text, which you can then use fo
AutoExpand/lookup

I see potential confusion for both you and Access in setting the value o
[PersonID] to [PersonID] -- I suspect you have a text control name
[PersonID] and a table field named [PersonID]. Which is which

Access is adding in the square brackets because a space (or special #(^*
character) is ambiguous and confusing to it. If you must use spaces in you
field names, put the square brackets around the entire name to avoi
confusion

Let's go back to the beginning. WHY are you trying to put the PersonID i
something, when you've just selected it (and it already is IN the combobox)
 
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...?)
 
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...?
 
Back
Top