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

[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..

 

[Pat Hartman]

You also need to fix your relationship it is incorrect.
1. Naming all your primary keys "ID" is not cool or efficient and does nothing except foster confusion. Use CustomerID and OrderID so you can easily distinguish relationships without having to open a relationships window. If it makes you feel more comfortable, add _FK as a suffix when using the PK as a FK.
2. Remove the existing relationship.
3. Then you have to change Customers to add the field CustomerID_FK and remove the field named CustomerName and redraw the relationship.

It is quite possible that you are confused because you are using table level lookups.