Use form for lookup and data entry

[rjmccafferty1]

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?

 

[lameid]

1) Yes. You want a multi column combobox or listbox. The key point is to make sure your bound column uniquely specifies the record otherwise if you use last name and it is smith, it will not have the right value. For this I would use the combobox wizard that allows you to search or find a record.

2) Yes this should be possible. Modify the after update event from number 1. If the recordset that is the clone is at the End of File (EOF property) then you want to go the next record.

Hopefully that is enough with your books for you to get it. If you need more help let us know.

 

[rjmccafferty1]

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.

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?

 

[rjmccafferty1]

The problem of not seeing the entire record is solved. I used the third option in the combo box wizard to look up a record.

If I do not find an entry in the combo box drop down, then I want to enter a new record and would prefer not to make the user go to the bottom of the form and press the new record icon. Is there way for the system to just go into a state that would allow me to just enter a new record if I cannot find a match.

A responder above suggested that I "modify the after update event, if the recordset that is the clone is at the End of File (EOF property) then you want to go the next record."

I don't think I understand what he was trying to tell me. I certainly understand what it means to be and the EOF. I understand what the after update property is and the wizard automatically made this entry:
, , First, ="[CustomerID] = " & Str(Nz(Screen.ActiveControl,0))

But I have no idea how to implement what the other person was trying to tell me.

 

[TheAceMan1]

How are rjmccafferty1 . . .

As far as input at the top, have a look at thread702-1309854. Since the input line is unbound you can put the [blue]FirstName[/blue] unbound combobox right next to the unbound [blue]LastName[/blue] textbox. With this setup I'd use code in the [blue]AfterUpdate[/blue] of the combobox as follows:
[ol][blue][li]Hold last/first names in variables.[/li]
[li]Perform a DLookUp to see if the name exists.[/li]
[li] If name exists
Clear the input line.
LookUp the name in the form & set focus to that record.
Else
Continue entering the new record!
End If[/li][/blue][/ol]
A popup message that the name was not found and the user can continue entering the new record, can be applied in the [blue]Else[/blue] section.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[lameid]

An easier option occured to me. Assuming of course all the possible values are in the drop down, you can just use a subform.

Create an unbound Main Form.

Add your "navigation" combobox to the main form.

Add your existing form to the main form as a sub form.

Use the combobox name as the Master Field and the appropriate child field in the sub form.


This will navigate if it is there or it won't find anything and the default value will be the master field.

The only weird thing is that you can't build the master and child fields, you have to type them in.