Combo Box adventure came unstuck

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have had considerable help from this newsgroup with several combo
boxes that were giving me problems.

Now I have been adventurous by tailoring and my combo box is not
perfect.

I have changed the Surname and FirstName fields into a concatenated
field called Member.

Now I can type the surname "Smith" and the member names starting with
Smith are shown but the record is not found on the form.

Which part of the code is supposed to find the record? If there is
supposed to be an "On Click" event I have destroyed it with my
enthusiasm.

Your advice is very welcome.
 
Robin

You didn't provide a copy of the SQL statement that fills your "new"
combobox, so it may be difficult to troubleshoot.

If I needed a combobox that displayed LastName, FirstName, but saved the
PersonID, I would create a query. My query would include the PersonID field
(first), and a field I named Member, something like:
Member: [LastName] & ", " & [FirstName]

I'd then use this query as the source for my combobox. I'd set the bound
column to 1, and the width of that first column to 0.

(and if I wanted to save the PersonID, I'd bind the combobox to an
underlying PersonID field from the form.)
 
Jeff,

Here is the SQL

SELECT qryRollCall.ID, [LastName] & " " & [FirstName] & " " & [Club]
AS Member
FROM qryRollCall
ORDER BY qryRollCall.[LastName];

ID is Field 1, Member is field 2. I did not count the LastName field
since it does not produce output.

I don't want to save the ID. I need to find the record.

Thanks,

Robin
 
I have had considerable help from this newsgroup with several combo
boxes that were giving me problems.

Now I have been adventurous by tailoring and my combo box is not
perfect.

I have changed the Surname and FirstName fields into a concatenated
field called Member.
Click to expand...

That's a BIG step in the *WRONG DIRECTION*. These fields should be
separate. Your Table should have a unique MemberID (names are *not*
unique, you might have two members both named Robert Smith). The Combo
Box should use this ID as its bound column, and all tables other than
the member table itself should contain *only* the MemberID, not any of
the name fields.

The Combo Box can and should be based on a Query such as

SELECT MemberID, [Surname] & ", " & [FirstName],
[other-identifying-fields]
ORDER BY Surname, FirstName;

but the concatenation should be done only here, not in the table.

There is no magic about "finding a record". The Combo Box's
AfterUpdate event will need some VBA code (created by the Wizard or by
programming it yourself) to do so. Just creating a combo box won't
create this code.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I failed to explain myself properly. The fields are separate in the
table.

My second message in this thread does explain that I have an ID field
and that concatenation takes place in the SQL.

The problem arises because I used the wizard to make the combo box and
then did the concatenation in the SQL. In the process I managed to
break whatever function finds the record.

Thanks for you interest.

That's a BIG step in the *WRONG DIRECTION*. These fields should be
separate. Your Table should have a unique MemberID (names are *not*
unique, you might have two members both named Robert Smith). The Combo
Box should use this ID as its bound column, and all tables other than
the member table itself should contain *only* the MemberID, not any of
the name fields.

The Combo Box can and should be based on a Query such as

SELECT MemberID, [Surname] & ", " & [FirstName],
[other-identifying-fields]
ORDER BY Surname, FirstName;

but the concatenation should be done only here, not in the table.

There is no magic about "finding a record". The Combo Box's
AfterUpdate event will need some VBA code (created by the Wizard or by
programming it yourself) to do so. Just creating a combo box won't
create this code.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
I have had considerable help from this newsgroup with several combo
boxes that were giving me problems.

Now I have been adventurous by tailoring and my combo box is not
perfect.

I have changed the Surname and FirstName fields into a concatenated
field called Member.
Click to expand...
Click to expand...
 
I failed to explain myself properly. The fields are separate in the
table.

My second message in this thread does explain that I have an ID field
and that concatenation takes place in the SQL.

The problem arises because I used the wizard to make the combo box and
then did the concatenation in the SQL. In the process I managed to
break whatever function finds the record.
Click to expand...
sorry about the misinterpretation!

Please post the VBA code in the combo box's AfterUpdate event. That's
where the "finding" goes on.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John,

When I looked for the "AfterUpdate" VBA it had gone.

So I rebuilt the combo box four times. Three times I must have done
something wrong but the fourth time it works as I planned.

Thanks.

Now I have another question and will start a new thread.

Robin
 
Back
Top