Prevent duplicate entry in a subform

[Eljefegeneo]

Using Access 2010.
I have a sub form [subClientLanguages] on a main form [frmMain] that list the languages which a person can speak.
The languages come from a tblLanguages which has two fields: LanguageID and Language.
tblClientLanguages which has three fields: ID (PK), ClientID (which is the relationship to the tblMain: frmMain) and Language (which is a combobox): [SELECT [tblLanguages].[Language] FROM tblLanguages;]
I then made a subform [subClientLanguages] on the main form [frmMain], this subform is based on the tblClientLanguages.
What I would like to do is have some code so that OnUpdate of a new language entry on the subform prevents entry of a duplicate Language.
I've tried Dcount, but can't seem to get the code correct.

 

[jdraw]

Look at making the ClientID and the LanguageId a unique compound index in table tblClientLanguages .

 

[Eljefegeneo]

Thanks, but I tried that and it didn't seem to work. I made the LanguageID the main PK and the ClientID the secondary PK. But it still permits me to enter a second record with the same language. What am I doing wrong?

 

[jdraw]

There is no Primary PK and secondary PK.

Show me you structure for tblClientLanguages.
Show me the indexes also.
Show me the details of the unique compound index you created.

use jpgs and capture screen shots.

 

[Eljefegeneo]

Thanks, I check my PK's and I had it on the wrong field. Put it on [Languages] and it works fine.

 

[jdraw]

Glad you have it solved.

A table can only have 1 Primary Key, but it could be composed of several nonNull fields.

A unique index does not have to be the Primary key, and it may or may not be composed of several nonNull fields.

 

[Eljefegeneo]

Have a related question. If I add a language to the list and it is a duplicate, I get an error message. If I try to leave the subform I can't and if I try to delete the entry I can't. It keeps telling me that I can't have a null value in the PK. So I have to add another language which is not wanted and then delete it. I can't seem to find any code that would void the record entry on error. Is there a code that I can use.

 

[jdraw]

If I add a language to the list and it is a duplicate,

What exactly are you talking about?

tblLanguages which has two fields: LanguageID and Language.

LanguageID is the PK right?

Post some pictures or make an sample.

Why would you add a duplicate Language?
?????????????????

 

[Eljefegeneo]

I am not sure what you wanted to see, but I have attached ad DB that shows the following:
tblClientlanguages and tblLanguages; frmClientLanguagesSub
The subform is in continuous form view.
The subform is on frmMain, based on tblMain and has the ClientID field as the primary key. (Not shown).
The problem arises if someone doesn't see the language that they want to enter in the subform, and it is a duplicate, the error message appears. They don't want to enter duplicate data; they might do it by accident. Then realizing that they made a mistake, try to go on without entering any more "languages", they can't. They get the first error message that says that they have created a duplicate primary key. When they try to delete the erroneous entry in the subform they get a second error message.

What I would like is some code that says if an error is caused by a duplicate primary key, then the record is not saved. To force the user to try and figure out how to get out of the subform would not be a good idea.

Or have I done something wrong in designing the tblClientLanguages?

Thanks.

 

[jdraw]

I looked at your database. I didn't see a Main form.

I added newtblClient and newtblClientLanguages, copied your language table but created a PrimaryKey.
I also created 2 new tables frmNewMain and frmNewtblClientLanguages.

I created some sample clients. I added a combo box to the Main form from which you can select a language to add to the Client Language skills ( NewtblClientLanguages).

On the main form, select the client you want to add language skills.
Then use the combo and select a Language. Once selected a record will be inserted into NewtblClientLanguages for this client.

If you try to add a duplicate for this client a warning message will be given.

You can add 1 language at a time for the client.

Good luck.

I am attaching the modified version of your database.

 

[Eljefegeneo]

Thanks. I think this should work. I kept trying to figure out some code to check before update if there was a duplicate that it would give me an error message. That was easy. But I just couldn't figure out how to then undo the entry if there was a message box, check OK and the entry would be undone. It just wouldn't happen.

I have used a pop-up modal form in the past to enter records in a subform, but this is much better. I may use it for other fields where I had used the tricky multi-value field.

Thanks again.

 

[jdraw]

Did you try adding a duplicate? You should get a message.

The secret is to structure you tables to not allow duplicates -
Unique compound index - and to prepare for the trappable error in your error handling routine.

Good luck. and there may be other approaches that will work in your situation

 

[Eljefegeneo]

Yes, it does work. I modified it slightly so that the combo box would be blank on going to a new client record in frmMain. I was wondering why you need to have a languageID instead of just using the language as the PK. And I discovered that sometimes when I make a new entry, the subform takes a few seconds (or sometimes more) to populate the language field from the combo box. This is especially true for the first entry of a language for a particular client. I have some 20,000 plus entries in tblMain. Do I assuming that the requery is taking a long time? Is there a way to speed this up?

In any case I thank you again and I'll be using this in the DB, also with another similar subform.

 

[Eljefegeneo]

Please see the attached DB. I just can't understand what I am doing wrong.
Your "language" subform worked so well I thought I would just modify it to work on one for "areas" (of the world). I have modified each of the tables and forms for "language" and made them for "areas". I cannot find any difference in the tables except, of course, in the names of the tables and the fields. The primary keys are the same. The properties of each form seem to be the same. Yet the combo box [cboArea] lookup and the related subform allows duplicate entries.
The new subform and lookup are on the Copy of NewFormMain.
Thanks.

 

[jdraw]

I looked at your database. You needed a unique compound index on the AreaId and ClientId see the jpg.
I also attached the revised database with slight mods to the Form and the index on the tblAreas. I added an On Error goto statement in you area combo after update.

 

[Eljefegeneo]

Thanks. I'll try it tomorrow.

 

[Eljefegeneo]

Thanks, it works great! Have a few questions. First of all, the code for after update looks the same for cboAreas as for combo21 (Languages). But you don't have the dim statement in the one for cboAreas. "Dim msql As String" Yet it seems to work. Why? Or rather why do you need or do not need this Dim statement?
I see the changes to the Indexes, something new for me. I am going to have to learn what these mean. But I don't see the modification that you made to the form. The reason I am asking is so that I can understand what you have done differently.
Anyway, thanks again. You are a real time saver.

 

[jdraw]

I added a label above your combo for Areas.

I have attached a jpg showing the local variables when I pause the code in the Areas AfterUpdate.

You will see that msql has been defined by Access, and by default it is a Variant as you can see in the top right.

As a "best practices" you should turn on Option Explicit

That will give you a message if you try to use a variable that hasn't been defined. In effect, it forces you to DIM all your variables.

 

[Eljefegeneo]

If I may prevail on you again, I am trying to use more and more calculated fields in my DB. I am having a problem combining the DCount function with an "And" [ControlName] either equal or not equal to something. I can get it to work with just the DCount, but when I combine it with the "And", all I get is the #Name? error:


=IIf(DCount("[ClientLangID]","qryLanguage","[ClientID] = " & [ClientID])=1 And [Language]="English","No",IIf(DCount("[ClientLangID]","qryLanguage","[ClientID] = " & [ClientID])=1 And [Language]<>"English","Yes", IIf(DCount("[ClientLangID]","qryLanguage","[ClientID] = " & [ClientID])=2 And [Language]<>"English","Yes", "Both")))
Please see the attached sample (that you so kindly helped with me previously)

I would like to have a calculated field so that:
1. If there is only one language and it is "English", the answer would be "no"
2. If there is one or more language and one of them is not "English", the answer would be "yes"
3. Else "both"

 

[Eljefegeneo]

I've come up with a slight problem caused by an inexperienced user. On the list populated by the drop-down list on the form (Languages), the user did not right click the entry and use delete, but highlighted the entry and hit the delete key. This wipes out the text for the entry, but not the corresponding primary key number (autonumber). This a blank field is displayed where the corresponding text was previously.
On the sample DB that is the last post that you sent me, if you were to highlight a language and the hit the delete or backspace key, the text is gone. This is the problem to which I address. Any way of preventing it? Thanks.