Messsage if duplicate on 2 combo boxes (1 Viewer)

DalGal

Registered User.
Local time
Today, 00:50
Joined
Oct 23, 2018
Messages
23
I already have a primary key (member id) on table MembersList, but want to test for duplicates on a form where both first and last names are combo boxes before creating the record. so far I have the following as an Event procedure on the Before Update of the second combo box names Last Combo. The Last Combo saves to [Member Last], and First Combo saves to [Member First]. I want to show a message if the First name, Last name, and CenterID duplicate one already in the table MembersList. I also want to not save the record if there is a duplicate.

Private Sub Last_Combo_BeforeUpdate()
Where [Member First] = [MembersList].[Member First] And [Member Last] = [MembersList].[Member Last] And [CenterID] = [MembersList].[CenterID]
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:50
Joined
Oct 29, 2018
Messages
21,358
Hi. What other information are on your form? You can use the form's BeforeUpdate event to validate the entry. You can set the Cancel argument to True if you don't want to save the new record. If you want immediate notification/validation, you can use the control's BeforeUpdate event instead.
 

DalGal

Registered User.
Local time
Today, 00:50
Joined
Oct 23, 2018
Messages
23
Hi. What other information are on your form? You can use the form's BeforeUpdate event to validate the entry. You can set the Cancel argument to True if you don't want to save the new record. If you want immediate notification/validation, you can use the control's BeforeUpdate event instead.
It is a data entry form. The user enters the CenterName, which is a combo box and saves the CenterID. Then just the First name combo box and last name combo box, and a notes field. Hidden is the MemberID which is an automatic number.
1595954319420.png
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:50
Joined
Oct 29, 2018
Messages
21,358
It is a data entry form. The user enters the CenterName, which is a combo box and saves the CenterID. Then just the First name combo box and last name combo box, and a notes field. Hidden is the MemberID which is an automatic number.
View attachment 83809
Hi. Since you're using a Combobox, are you saying the "Limit To List" property is set to No?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:50
Joined
Oct 29, 2018
Messages
21,358
Okay, sounds like you could design this a little different. For example, you are storing the MemberID in the table instead of their names, correct? If not, I would suggest you do it the other way (store the ID as foreign keys).
 

DalGal

Registered User.
Local time
Today, 00:50
Joined
Oct 23, 2018
Messages
23
Hi. Since you're using a Combobox, are you saying the "Limit To List" property is set to No?
It is ok to have a duplicate first name, OR last name, OR CenterID, but not all 3 in one record.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:50
Joined
Oct 29, 2018
Messages
21,358
It is ok to have a duplicate first name, OR last name, OR CenterID, but not all 3 in one record.
Right, but if each combination of first and last name corresponds to a MemberID number in a parent table, it's common practice to store that ID instead of the names. In other words, you only need to store one value, instead of two. Can you describe the basic purpose of your db? Can you post a screenshot of your Relationships window?
 

DalGal

Registered User.
Local time
Today, 00:50
Joined
Oct 23, 2018
Messages
23
Okay, sounds like you could design this a little different. For example, you are storing the MemberID in the table instead of their names, correct? If not, I would suggest you do it the other way (store the ID as foreign keys).
In the CenterName combo box, they enter the center name, and it stores the center id. The table is:
CenterID, MemberID, FirstName, LastName, Notes, Active
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:50
Joined
Oct 29, 2018
Messages
21,358
In the CenterName combo box, they enter the center name, and it stores the center id. The table is:
CenterID, MemberID, FirstName, LastName, Notes, Active
So, referring to the bolded fields in the above quote, does the MemberID refer to the matching FirstName and LastName? If so, then there's really no reason to store them again with the MemberID field.
 

DalGal

Registered User.
Local time
Today, 00:50
Joined
Oct 23, 2018
Messages
23
I guess I'm not sure what you are saying. They are 3 separate fields. I use member ID in other tables and link it via relationship with the MembersList table. I guess the MemberID is not really needed as I COULD key on the CenterID,FirstName,LastName, but--I only very loosely understand Access and am just getting by on google and forums. LOL. I have all the relationships, forms, reports, and everything set with the MemberID to bring up the record, and the database is currently being used already. I just wanted to prevent duplicates on those 3 fields, give a message about duplicates, and either clear the form or just not save it.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:50
Joined
Oct 29, 2018
Messages
21,358
I guess I'm not sure what you are saying. They are 3 separate fields. I use member ID in other tables and link it via relationship with the MembersList table. I guess the MemberID is not really needed as I COULD key on the CenterID,FirstName,LastName, but--I only very loosely understand Access and am just getting by on google and forums. LOL. I have all the relationships, forms, reports, and everything set with the MemberID to bring up the record, and the database is currently being used already. I just wanted to prevent duplicates on those 3 fields, give a message about duplicates, and either clear the form or just not save it.
Hi. If you don't mind, could you please address my previous request for you to help us understand your situation better? Specifically, I asked if you could post some images, so we can see what you're working with. The best option, if available, is to post a sample copy of your db with test data only. Can you do that? Thanks.
 

smtazulislam

Member
Local time
Today, 08:50
Joined
Mar 27, 2020
Messages
806
You can follow up this thread, it was same reason...
 

Users who are viewing this thread

Top Bottom