Solved If the customer's name exists, Get his record to add data to his subform which related to another table

[Hisoka]

Hello,
I have these two tables and this form,



I want that if I write the name of an already existing customer, That is, the program will bring his record to the main form (his ID & Number...etc) to be able to modify his order in subform without having to search for the customer through the Navigation Buttons..

In other words, I need if i write the name of a customer that does not exist, program must add him as a new customer,
and if the customer already exists, program must get his info to current form To be able to modify his subform..

I appreciate any help...

 

[Ranman256]

this Find screen would count the recs having given name.
if 0 exist, open the detail in dataentry mode
if 1 exists, the only exising rec, open that rec in detail form
if > 1 exists, open a LIST view of all recs that match, then user can choose the correct 1 and open detail from there.

Private Sub btnFind_click()
Dim iCt As Integer
Dim sWhere As String
Dim vFirst, vLast, vID
Const kTBL = "tClients"

vFirst = txtFirst
vLast = txtLast

sWhere = "[FirstName]='" & vFirst & "' and [LastName]='" & vLast & "'"
iCt = DCount("*", kTBL, sWhere)

Select Case iCt
   Case 0   'add new client
     DoCmd.OpenForm "frmClientDtl", , , , acFormAdd
     Forms!frmClientDtl!txtFirstName = vFirst
     Forms!frmClientDtl!txtLastName = vLast
    
   Case 1   'open the detail frm on only client
     vID = DLookup("[ID]", kTBL, sWhere)
     DoCmd.OpenForm "frmClientDtl", , , "[ID]=" & vID
  
   Case Else  'open a LIST VIEW of all hits, then user can select the 1 needed from that to open detail view
      DoCmd.OpenForm "frmClientList", , , sWhere
End Select
End Sub

 

[MajP]

Demo

 

[Hisoka]

Thank you, @Ranman256, Unfortunately, I did not know how to modify the code to suit my request. I will try it again.

Thank you, @MajP, Very nice idea. Combobox solve the problem. Thanks for the help..