Form - problem trying to find record with combo box

S

sherry.walker

I am using Access 2007. Have created a combo box on a form with the wizard
to "Find a record on my form based on the value I selected in my combo box."
I have used this feature in previous versions of Access and it always worked
properly. But this one stops on the first record with matching info and then
must use navigation keys to get to correct record ... e.g., list shows Allen,
James; Allen, Kevin; Allen, Leon ... if click on Allen, Leon, it moves to the
first Allen in the list instead of the Allen chosen. How can I make it move
to the record selected instead?
 
L

Linq Adams via AccessMonster.com

Are you sure the names are appearing in the combobox as

Allen, James
Allen, Kevin
Allen, Leon

with a comma between the last name and first name? This would indicate that
the complete name is held in one field, and if this is true, then the
combobox should retrieve the correct record. If, however, the last name and
first name are held in separate fields, as is usually done, and actually
appear as

Allen James
Allen Kevin
Allen Leon

with simply a space between the first and last name, this would explain the
problem. The command the combobox uses to retrieve a record is rs.FindFirst
and it does exactly as its name suggests, finds the FIRST record that matches.
If it's matching on last names, it'll find the first record with the last
name of, in this case, Allen, regardless of which Allen you click on.

If this is the case, the fix involves using a calculated field in a query. If
the form is based directly on a table, you need to make a simple query
including all the fields of the table, and then change the RecordSource for
the form from the table to the query. If the form is already based on a query,
more's the better. All form really should be based on queries anyway, for
situations just like this.

Now open the query in Design View, and in a blank "Field" box enter this code:


CompleteName: [LastName] & " " & [FirstName]

Be sure to include the colon after CompleteName and before [LastName] & " " &
[FirstName] (I say this because it's very hard to see on my monitor)

Save and Exit Design View

Run the query and you should have a new field named CompleteName and the data
should look like

Allen James
Allen Kevin
Allen Leon

Now, delete your current combobox, create another combobx based on your
calculated fiels CompleteName that you just added to the query. Not only will
Access take you to the correct Allen, if you keep entereing the name (typing
Allen L for instance will take you to Allen Leon, without scrolling) when you
click on Allen Leon it’ll take you to his record.

It may be that in the previous DBs where this worked before, your name fields
had last name and first name in a single field.
 
S

sherry.walker

Thank you so much! I've got it working now!

I didn't explain the problem very well; you were right about the names
appearing with a space. I did have the form based on the table and not a
query. The combo box wizard created a query as the record source with the
two fields I'm using (LastName FirstName).

I've used this method many times in previous versions of Access and it used
both fields as the identifier and moved to the correct record. However,
Access 2007 apparantly no longer works the same way.

I've created the query for the table the form is based on and added the
CompleteName field. Based my form on that query, recreated the combo box and
now everything is working properly.

Again, thanks SO much!!
Sherry

Linq Adams via AccessMonster.com said:
Are you sure the names are appearing in the combobox as

Allen, James
Allen, Kevin
Allen, Leon

with a comma between the last name and first name? This would indicate that
the complete name is held in one field, and if this is true, then the
combobox should retrieve the correct record. If, however, the last name and
first name are held in separate fields, as is usually done, and actually
appear as

Allen James
Allen Kevin
Allen Leon

with simply a space between the first and last name, this would explain the
problem. The command the combobox uses to retrieve a record is rs.FindFirst
and it does exactly as its name suggests, finds the FIRST record that matches.
If it's matching on last names, it'll find the first record with the last
name of, in this case, Allen, regardless of which Allen you click on.

If this is the case, the fix involves using a calculated field in a query. If
the form is based directly on a table, you need to make a simple query
including all the fields of the table, and then change the RecordSource for
the form from the table to the query. If the form is already based on a query,
more's the better. All form really should be based on queries anyway, for
situations just like this.

Now open the query in Design View, and in a blank "Field" box enter this code:


CompleteName: [LastName] & " " & [FirstName]

Be sure to include the colon after CompleteName and before [LastName] & " " &
[FirstName] (I say this because it's very hard to see on my monitor)

Save and Exit Design View

Run the query and you should have a new field named CompleteName and the data
should look like

Allen James
Allen Kevin
Allen Leon

Now, delete your current combobox, create another combobx based on your
calculated fiels CompleteName that you just added to the query. Not only will
Access take you to the correct Allen, if you keep entereing the name (typing
Allen L for instance will take you to Allen Leon, without scrolling) when you
click on Allen Leon it’ll take you to his record.

It may be that in the previous DBs where this worked before, your name fields
had last name and first name in a single field.
Click to expand...
 
Top