how to assigne correct client_ID to order record from name, adress or create a new one if it doesn&#-CodePudding

I am trying to build a simple ms Access database. For the sake of convenience, I will translate into sales, orders and such.

Orders
OrderID - PK (auto number)
Client ID - FK to clientID in the Clients table

Clients
ClientID - PK (auto number)
LastName
FirstName
Company
Address
...

I created a form (main form) that allow the user to specify details for a new order, including the client details. The same form is used to look the details of older orders, to change their step for instance. When the clerks types in, I don't know if the client is already registered. After everything has been completed, the clerk saves the new order as a new record in the Orders table. However, Access complains rightly that the clientID FK has not been set: we set the clients name, first name... but not the ID.

My ultimate goal is that, at the saving of the new record, a request that look up into the Clients table to identify a list of possible candidates that match the client first and last name and open a form (contact form) that nicely displays the data from the first record of the request and allow the clerk to navigate between the records, and eventually select one or select create new record with the data from the main form.

I figured that a good place to do this would be in the save button on my form. I tried to add something in the event code (macro) with something like (not VBA, a pseudo code I've never seen in the other office applications):

if IsNull([main form]![clientID]) then
    bip
    launch a request to identify the clients in the clients table with the same first and last name
    open a form to look into each selected record and select the correct one or if none is identical generate a new one and return the clientID
end if
executeMenuCommand
    command saveRecord

The code I tried above didn't even bipped... I have placed the bip first, and that didn't bipped either. I receive a message (explicit) that ms Access could not find a record in the client table with the specified clientID.

So I guess my main question is how do I test if there is a clientID associated with the current record? The next problem is to come back to the main form with the selected clientID.

From what I have seen in the templates provided by Microsoft, it is avoided by defining a single field for customer in their main form. This field is a list populated from the client table, and if you stray away from the list you get a new form for editing a new client. This is not the behaviour I am looking into. I need to have the posibility to free text enter every field and chose at the saving moment to create or not a new customer.

I hope I am clear enough, please feel free to ask for more details.

edit: thanks to Gustav's hint, I figured that the event was not to be associated with a button click but on the form itself. It helped me uncover other underlying problems... so I guess my problem is not ripe anymore for soliciting external help.

CodePudding user response：

In VBA, use IsNull, and Me if the form is bound to table Orders:

If IsNull(Me![clientID].Value) Then