Avoid duplication entries on an input form (1 Viewer)

[eugzl]

Hi All.
I have an input form that asks for a Brand name and Device type (PC, Printer, Monitor, and ets) . What I would like to happen is when a user selected brand name from combo box cboBrand and then enters Device type in txtDeviceType text box that already exists in the tblDeviceType table, something triggers an error message will popup that this device for according brand already exists. So, I need to check duplicates by two values BrandID and DeviceType. Because different brands can have devices, such as PC, Printer, Monitor and ets.
Please help with this because I'm not exactly sure how to do it based on two values. I will appreciate someone will show on a sample how it to do.

Thanks

 

[theDBguy]

Hi. Preventing duplicate entries are best checked/done using the BeforeUpdate event (either Form or Control). I tend to use the DCount() function to check for duplicates. If you can post a sample db, we can try to show you how to do it.

 

[Pat Hartman]

The dCount() has two conditions: I assumed you would be checking the ID rather than the name. If the PK is a string rather than a long integer, you need to enclose the value in quotes.

If dCount("*", "yourtable", "BrandID ="  & Me.BrandID & " AND DeviceTypeID = " & Me.DeviceTypeID) > 0 Then
    Msgbox "Brand and Device already exist. Please choose a different Device.",vbOKOnly
    Cancel = True
    Exit Sub
End If

 

[eugzl]

Hi. Preventing duplicate entries are best checked/done using the BeforeUpdate event (either Form or Control). I tend to use the DCount() function to check for duplicates. If you can post a sample db, we can try to show you how to do it.

Hi theDBguy. Thanks for reply.
I attached Access file where in the fDeviceType I would like to check duplicate by two values BrandID and DeviceType.

 

[Pat Hartman]

Did you at least try try the suggestions?

 

[eugzl]

Did you at least try try the suggestions?

Hi Pat. Thanks for reply.
Didn't try yet. Can you explain you code. I mean how in your code specify BrandID and related cboBrand like first parameter. And text/string value entered in txtDeviveType text box.
Thank

 

[Pat Hartman]

I don't know what the key fields are. They should NOT be text names. That is why I didn't use the names from your example. If the names from your example are truly the key names, then use those fields - BUT - if they are text values, you will need to enclose the strings in single or double quotes.

I just looked at your db. I actually guessed correctly (almost) The names are BrandID and DevTypeID

However, there is a logic problem with the DeviceType table. Remove BrandID. The table also has invalid data. Sort by BrandID to see what I mean. Fix the table and the data. OR BrandID belongs in DeviceType table but NOT in Device. It cannot be in two places.

I think you need to clarify the schema. The concepts are murky. If only certain manufacturers make certain types, then you need four tables, not three. You need Brand, Type , BrandType which is a junction table That allows PK to exist for Dell, HP, and IBM.

Then the device table would have the BrandTypeID so it picks the Dell PC rather than the IBM PC and includes the serial number, model number and all the other attributes specific to the actual piece of equipment.

Brand has invalid data and so does Device so you need to go back to the drawingborard and fix your relationships AFTER you fix the data. THEN and only THEN can we come back to make the forms work.

 

[eugzl]

I don't know what the key fields are. They should NOT be text names. That is why I didn't use the names from your example. If the names from your example are truly the key names, then use those fields - BUT - if they are text values, you will need to enclose the strings in single or double quotes.

I just looked at your db. I actually guessed correctly (almost) The names are BrandID and DevTypeID

However, there is a logic problem with the DeviceType table. Remove BrandID. The table also has invalid data. Sort by BrandID to see what I mean. Fix the table and the data. OR BrandID belongs in DeviceType table but NOT in Device. It cannot be in two places.

I think you need to clarify the schema. The concepts are murky. If only certain manufacturers make certain types, then you need four tables, not three. You need Brand, Type , BrandType which is a junction table That allows PK to exist for Dell, HP, and IBM.

Then the device table would have the BrandTypeID so it picks the Dell PC rather than the IBM PC and includes the serial number, model number and all the other attributes specific to the actual piece of equipment.

Brand has invalid data and so does Device so you need to go back to the drawingborard and fix your relationships AFTER you fix the data. THEN and only THEN can we come back to make the forms work.

I briefly describe my script. The f_Device form gives user ability to add new device to the system. That form has cascade combo boxes cboBrand, cboDeviceType and cboModel. When user selected all required value data will saved. In case when user need add device but brand of new device doesn't exist in the system a user will click button from the right from cboBrand to call form fBrand to add new brand name. In case when user need add device but device type doesn't exist in the system a user will click button from the right from cboDeviceType to call form fDeviceType to add new device type. The similar story for device model.
The Form1 is test form. It has only one button to call f_Device form to add new device.
The f_Defice form binding to Device table
The fBrand form binding to Brand table
The fDeviceType form binding to DeviceType table
Like you absolutely correct point must to have intermediate table between Brand and DeviceType but I cannot figure out how it create. What structure of that table must be. And second question is how to avoid duplicates in case when need to add new device.
I will very appreciate if you can help me to solve those problems.
In the attachment my last version of Access file
Thanks.

 

[Pat Hartman]

I'm telling you that your tables are not defined correctly. Brand does NOT belong in two tables.

Don't worry about how to create the data. You first need to define what you need to store and how to structure the tables correctly. Here's a sample that shows a working m-m relationship which is what you have between Brand and Type.

 

[eugzl]

I'm telling you that your tables are not defined correctly. Brand does NOT belong in two tables.

Don't worry about how to create the data. You first need to define what you need to store and how to structure the tables correctly. Here's a sample that shows a working m-m relationship which is what you have between Brand and Type.

Thanks for sample.
In you post you said that BrandID not correct have in Device and DeviceType tables.
In first case if I create new device record and save data into Device table that table must to has BrandID.
If need to add new device type I select value in cboBrand (BrandID), type new device type to text box and save new record into DeviceType table. For that case DeviceType table should has BrandID.
I'm feeling that DeviceType table has problem but cannot figure out how to fix. I will appreciate if you will show how to fix table and relationship structure.
Thanks.

 

[Gasman]

Now crossposted athttps://www.accessforums.net/showthread.php?t=84865&highlight=Structure

 

[Pat Hartman]

In first case if I create new device record and save data into Device table that table must to has BrandID.

But Device is connected to DeviceType and that is connected to Brand. Therefore, Device is related to Brand Via Type. If you have Brand in the Device record there could be a conflict. Device related to brandX directly but via type related to brandZ. That would be illogical.

Device type seems to be generic types of equipment. If you don't connect device type to Brand, you can have IBM selling ink and that probably isn't what you want so that is what you need to decide. Do you want to have just straight types and make the connection to Brand in the Device table or do you want to duplicate types since multiple Brands apply to any given type.

I am not the domain expert. My suggestion is based on real world experience. I have no idea what the application is for or how precise the relationships need to be.

The point is Brand goes ONE place but not TWO. I cannot decide for you.

 

[LarryE]

Thinking logically, you have brands that manufacture many different devices which come in different models, each with a different serial number. I took the liberty of re-designing your table structure to reflect this idea. I did add a SerialNumTbl so each device and model has its own serial number. Of course, you can add new fields as needed. You don't really need the old DeviceType table because the Device table can hold that information. You can add a DeviceType field to that table if you wish. this design will get you started on the right track hopefully.

 

[Pat Hartman]

You might also need a device type table.