Ask Your Question
0

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

asked 2018-12-31 19:02:52 +0100

fbaldeva gravatar image

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!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-12-31 19:51:13 +0100

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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.

Ratslinger gravatar imageRatslinger ( 2018-12-31 20:35:24 +0100 )edit

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!

fbaldeva gravatar imagefbaldeva ( 2018-12-31 23:53:20 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2019-01-01 03:03:35 +0100 )edit

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.

fbaldeva gravatar imagefbaldeva ( 2019-01-02 17:31:15 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2019-01-02 19:45:25 +0100 )edit

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!

fbaldeva gravatar imagefbaldeva ( 2019-01-02 20:04:41 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-31 19:02:52 +0100

Seen: 22 times

Last updated: Dec 31 '18