Joined table, subform entries

[Montez659]

On a form I have a subform that references a joined table called tblSiteVisitsJOINEDIssueCodes. From testing the form and subform input, I noticed that the user cannot enter more than one of the same issue code (both issueID and visitID are primary keys in the Joined table). However, if the user (who won't know any better) tries to add two "DEM" issues for instance, an error messge pops up saying that they cannot add the new one because there would exist two exact records via the visitID and issueID being the same.

All of this is understandable, however, even if the user tries to do it, the record is already created despite being not allowed. The user cannot leave the field blank, or another error pops up. If all "Issue Codes" are already in use, the user is sent into an endless loop of error messages until they close the record, at which point they are told it can't save the record.

So the question is, how can I validate an entry into the subform to check to see if that Issue Code has already been used BEFORE it even attempts to create the record. This should mitigate the Access error messages that pop up since as it stands the record is created as soon as an error code is selected in the combobox even if there is a duplicate. The subform is in datasheet view.

Thanks in advance.

 

[Montez659]

If in my Joined table I removed my two primary keys, created an autoincrement field and made that my primary key, would this have any effect on anything related to the Joined table, i.e., reports, queries, etc.?

 

[Norie]

If IssueID and VisitID are the primary keys of the other tables on either side then they should be foreign keys.

They definitely shouldn't be used, in anyway, as the primary key for the junction table.

A junction table doesn't necessarily need a primary key but there is no harm in adding one and it can be useful.

If you are going to add one you've done the right thing using an Automnumber field (I assume that's what you mean by Autoincrement anyway).

And, no they should have no direct affect on the other tables unless you use that table with those tables.

If you don't need the junction table, eg you want a summary of visits, with no data about issues, just use the tables on their own like you normally would.

 

[Montez659]

Thanks for the reply. Apparently in my attempts at my first Joined table, I misunderstood what was supposed to be a primary key in the joined table. I have updated and everything seems to be running smoothly.