Find and goto if exists

[buratti]

I'm trying to create a form where when a user is entering information in the first field (account number field) after it is entered (I guess the after Update control) it first searches if it exists and if it does goto that record, and if not, add it, then just continue entering the rest of customer info. I can figure how to have an unbound combo box do it, but I'd rather not have a combo box.

A second part of my question is... After navigating to the correct customer record, i need the subform for that customer to open in add mode, or starting in a new subform record.

Any suggestions on how to do this?

Thanks

 

[HAMMAMABUARQOUB]

if you have office 2007 ,,, creat the Students database, or in Nortwind sample database, see the employees form. there is a technique "clever thing" used to decide if it's a possible duplicate record...try depending on that formula to creat a code or a msg that goes to the record!
try this,,,

=IIf(DCount("*","[Students Extended]","[ID]<>" & Nz([ID],0) & " And [Student Name] = '" & Replace(Nz([Student Name]),"'","''") & "'")>0,"Possible Duplicate","")

 

[Scooterbug]

I have a form that is similiar. How I resolve it is:

The query for the form contains all the data that I need for the form. For the field I search on (Customer Number in my case) i put the following in the Criteria section for that field in the query: Forms!FormName!txtCustomerNumber). I then do a Me.Requery with the push of a button. If a record exsists, then it will show up. If not, I create a new record.

As for the Subform, what exactly are you recording? Normally you would use a Parent/Child Field...but the only thing I can think of in your case would to use the customerID number...but that would return all of the records. Since you said you wanted it to add or start new records...I'm unsure the purpose for your subform.

 

[buratti]

The whole form is essentially a customers order form (slightly different though) with a customers parent form and an orders child/subform. BTW, would the child form be the same as a subform or am I using wrong terminology? I have created it already and have everything linked correctly (meaning only orders for the current customer are displayed). When I open the form and go to the customer i want, it shows all previous orders for that customer. I would just like for when the form is opened, and navigated to whatever customer I'm entering a new order for, have the order subform be displayed where its ready to enter a new record, as opposed to navigating to the end of the pervious records before I can enter a new record.

 

[Scooterbug]

You would want the subform that holds the order details to be linked to the order number....not the customer number. The order number should be linked to the customer number. This way, you can have a list of orders for only the customer whose record you are viewing. Then, by selecting the Order Number, you can view the details of that order.