Find Record OR Create New

[James Tebb]

I have a form 'JOB FORM' on which is a text box 'CLIENT'

I have a table 'CLIENT LIST' which holds all my client details eg'CLIENT NAME'

I have a form 'CLIENT INPUT FORM' that allows me to input their details.

I want to set up a macro (or other method) so that when I type the clients name in the box on the job form and hit ENTER the following happens:-

1- The client input form opens
2- The relevant client record (matching the text box) appears OR If a record does not exist a new record is created with the clients name automatically entered.

Please be gentle -

 

[Norie]

Use a combobox not a textbox, then you can restrict the user to only entering/selecting an existing customer.

You can also deal with them not entering something in the list by setting the Limit To List of the combobox to Yes and creating an event like this.

Option Compare Database

Option Explicit
 
Private Sub Customer_NotInList(NewData As String, Response As Integer)
Dim resp
    
    resp = MsgBox("Customer" & Me.Field1 & " not on list. Do you wish to add them?", vbYesNo)
    
    If resp = vbYes Then
        Response acDataErrAdded
        ' code to add new customer
        Msgbox "New customer - " & NewData & " added."
    End If
    
End Sub

That's the basics, and it should be straightforward to add the new customer.

Don't have any code for that to hand right now but I'll try and post something later.

 

[James Tebb]

I'll give that a go
Thanks