Use a form for both lookup and new data entry

rmccafferty · September 8th, 2009, 11:28 AM

I know this is a naive question, but my books just do not answer it.

I need to use a form for both new customer data entry and for customer lookup. (It is actually an order entry form). I see the property that I need to set to allow a form to do both. So first I make fields for first name and last name. Then is there an automatic way to have the user enter a name and have the form look up the name and if it does not exist, then use the name as a new customer?

At the least I would like to avoid making the user go through the whole search process with whole name, part name, etc. options. Would also like to avoid making the user go to the bottom of the form (which will involve a scroll) to press the button for a new record.

Is there a way to
1)have the user enter a last name and see a drop down of all first and last names in the db that match that last name, letting them click on the appropriate line if they see the customer they want, and
2) if the use determines that there is no match, then create a new record easily without needing to scroll?

SerranoG · September 8th, 2009, 03:03 PM

If you want both data entry and look-up, you run into a problem. People may accidentally type over a record if you don't lock it first when they're searching. It's best to have a search field on the form for looking up data. Once found, an EDIT button would allow the user to change data by making the form read/write vs. read-only.

If you want to avoid that step, you can open a form for intended data entry at a new record. Populate a last name and first name dropdowns with all persons in the database. When users type into the last name dropdown, Access will try to autocomplete the name. When found, the person tabs to the first name field whereupon that dropdown will requery to be only those first names whose last names match what the person selected. Once done, if a record matches with that name combination, you can have the record jump to the one with that combination last/first name. If no records match, you'd be at a new record anyway and would be entering a new record.

Because last name / first name combination is not ideally unique (take "John Smith," for example), it's not the best approach for your desired situation. A better method would be for you to enter a UNIQUE identifier first (instead of name) and then the record would either jump to an existing one or become a new record. However, new customers will not have a unique ID yet.

Therefore, the best idea is to have that search field on top of the form with a subform that lists the finds. You can then highlight one and click an EDIT button, or find none and click a CREATE button.

rmccafferty · September 13th, 2009, 02:42 PM

In using the combo box, I ran into a problem I did not anticipate.

I want a use to select a customer on a form and have the form then reflect many fields for that record. Specifically, I want the user to use a combo box to select a customer (I show the last name and first name in the drop down of the combo box) for whom they can then perform other actions. The combo box itself seems to be fine as it displays the record selected in the drop down.

But I am doing something wrong. I have a form and the source of the data is the customer table. I have 3 dummy records with names as first & last, first2& last2, first3 & last3 as the entries for fname and lname fields. (I also have an address entered for the first record.)

I created a combo box using the wizard. I selected, in order:
Lookup values in table/query
Selected Customer table as the data source
Chose, in order, Lname and Fname fields
Chose Ascending for Lname and Ascending for Fname
Then finished.

In the form design view, this put a query into the row source field for the combo box that selected the CustID key field along with the Fname and Lname fields and left the control source property blank. (The control itself displays the last name at form view, which is what I prefer).

When I go to form view, the drop down works fine for this control. But none of the other fields in the form automatically switch to reflect the proper record. The form itself has its record source as the Customer table

I tried a number of things to force the issue, some didn't help, some caused the data in the underlying table to change.

Once I have the selection in the combo box, how do I make all the controls in the form automatically match the record which I just selected in the combo box?

SerranoG · September 17th, 2009, 04:54 PM

The combobox's AfterUpdate event would have to make a recordset clone of the data. Next you'd use the FindFirst method to match based on the criterion. Then you'd bookmark the find and set the current form's recordsource's bookmark match the found clone's bookmark. If not found at all in the clone, then you'd start a new record.