[BASE] Create a form that checks for unique entry before adding

[fbaldeva]

I have a table called CONTACTS, with ContactID (primary key), lastname, firstname, address, phone, etc.

I want to create a form that will take the input of lastname and firstname, then check against CONTACTS if the combination lastname||firstname is ther or not. If there, output some error, if not already there, present the user with all the other fields to input.

I envision the “query” form calling one of two other forms: a “that entry is already there” form or a form with the other entries to be filled out.

Is this possible with forms on its own or does there need to be some SQL checks behind it.

Thanks!

[Ratslinger]

Hello,

The controls on a form can be hidden and revealed depending upon a condition using macro code (major undertaking to learn from scratch). The same is true in calling another form.

But the situation itself presents problems. The biggest of which is data entry errors. If a name is entered with transposed letters (do this myself a lot) it will not be found and a new entry is made. If different characters are used in a spelling ( a back tick vs an apostrophe for example) a new entry is made creating the unwanted duplicate. Also what if there are two different contacts with the same first & last name?

Not certain of reasoning behind all this but it appears flawed in design. If with reasoning there is still the coding factor.

[Ratslinger]

Certainly I would not go this route, but you could always add the First and Last name fields as part of the table Key Instead of the ID. This way it could only be entered once. This does not account for the other fields to be entered.

[fbaldeva]

Thanks. Is there a proper way then, to set up a contacts table so that users cannot duplicate names?
I did think that maybe the last and first names could be linked to created some sort of unique key.

NOTE: If there are two John Smiths, then the first would be John Smith and the second would be John Smith1. Or I could also force a middle initial to decrease the likelihood of duplicate names.

Best!

[Ratslinger]

Having a difficult time comprehending the purpose of not duplicating names. Imagine a retailer with a database of customers that not does not want two with the same name. In my book there is no way that it is proper to not allow duplicate names. Names (people, companies etc) should not be considered unique or used as a key in databases.

That said, yes you can reduce the chance of duplication by using a middle initial. You can use multiple fields as the basis for a key. But go back to my answer. There is ALWAYS the chance of incorrect entries and duplication anyway. Don’t see any way around this.

[fbaldeva]

I’m thinking more of an address book type table.
For example an “actors” table with names like “Matt Smith”, “Vincent Price”, and the like.
Eventually there will be a “movie” table that will use the primary key of the “actors” table, so that the user does not have to keep typing “Vincent Price” over and over…
Hope that helps.

[Ratslinger]

Yes, that has been fully understood throughout. Have already given you multiple methods to accomplish in previous comment. Was it not clear?

[fbaldeva]

Thank you for your answer and further comments. I will have to spend more time learning forms and macros to accomplish what I want.
All the best!

[Ratslinger]

@fbaldeva Here are some links to examples with parts of what you may need:

Base macro that opens a new/clean record in another form

Libreoffice Base - Display Form based on Group Box Option selected

base: how to make a form that relates to different possible tables

Unique function to stop duplicate entry in either upper or lowercase