Combo box - how do you edit existing data using the form?

[creolejazz]

I have got a table with people's names, addresses, phones, birthdays, etc. as fields. I've created a form to use to enter data into the table but want to be sure I'm not duplicating data already in the table so have set up a combo box.

The combo box has three "columns" (last name, first name, and date of birth) which allows me to determine instantly whether or not I'm about to enter data that is already in the table.

If, by viewing the drop down in the combo box, I determine that I'm about to enter data that is already in the table, I would like to edit various fields of that existing entry directly from the form. How, using the combo box, can I get the data from an existing entry in the table to appear in the form so that I can edit the various fields?

Any help appreciated. Thanks.

 

[missinglinq]

If you have a query based on your table, go into it in Design View for the query. If you don't, you need to go into Design View for Queries and create a query with all the data from your table. Now go to a blank field in the query grid and type this in, substituting your actual field names:

RetrievalField: [LastName] & " " & [FirstName] & " " & [DOB]

Now go into Design View for your form and replace the table in the RecordSource with your query.

Delete your old combobox and create a new one. This time, when the Wizard comes up,

Select "Find a record based on the value I selected in my combobox."

When the Wizard asks for the table or query to retrieve the data from, select your new query.

From the query the form is based on, click on the field you're searching by (RetrievalField) to move it to the right side.

Hit Next.

Size the column appropriately.

Hit Next.

Name the combobox.

Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by, or you can start to enter the item, Last Name first then FirstName then DOB and the combobox will "autofill" as you type. If the person you want is there, hit <Enter> and the record will be retrieved and you can edit it.

 

[creolejazz]

Wow! Worked great. Thanks so much.

One more thing. When I finish completing (or updating) the last field in my form, I'd like to hit TAB and have it go to a NEW, blank entry. Instead, it takes me to the next name in the Retrieval "list". I'll bet it's easy. Am I right?

 

[creolejazz]

And one more thing...

When I type in the combo box, find a name, and hit TAB, all the data for that person that is already in the table appears. Perfect! But when I hit TAB again to move my cursor to the first field in the form (after my retrieval combo box), all the fields change to that of the NEXT entry in the list. I have to use the mouse to get move my cursor out of the combo box. Hmm. What do you make of that?

Thanks again. Great help. Appreciated.

 

[creolejazz]

And yet another thing related to this...

After I finish entering a new record using the form, how do you get it to update the table automatically? There's some sort of "Enter After" property or something like that, right? Where is it and what is the correct syntax? Thanks.

 

[boblarson]

Just to see if this helps:

1. You would use the combo's After Update event to move to the next field.
2. It appears that you have a preconceived notion about the Enter event that it has to do with the Enter key. This is not the case. It fires just as you enter a control, before it officially has focus.

 

[missinglinq]

...when I hit TAB again to move my cursor to the first field in the form (after my retrieval combo box), all the fields change to that of the NEXT entry in the list...

In Design View for the form, goto Properties - Other and set the Cycle Property to "Current Record."