Record update

[hduvall]

I have a table which has 4 fields, 2 of which are populated when the record is created. These two fields combined are the primary key. The first field is BadgeNbr and the second CourseNbr. There will be multiple course records for each BadgeNbr. I have created a form which is bound to this table. The form contains a text box for of the four fields in the record plus a list box which is populated from a query with all record with the badge number which is entered into the text box on the form. The remaining two fields are entered to complete the record.

Now the problem comes. How do I save this record and not get a duplicate key error. I assume that this will require code which is ok but I have not been able to come up with the required code,

Seems straight forward. Any suggestions?

Maybe there is an easier way to update the record.

 

[GinaWhipp]

Seems to me you're missing a table...

Course Table
Badge Table

CourseBadgeTable <-- Many to Many

It's that last one you should be using on your Form. That will allow many Badges with one Course.

 

[hduvall]

There is a course table with course number and name only. There is a volunteer table with badge number and other data. The table I am attempting to update has a relationship of many course records to a single badge or volunteer. That is why the primary key in this table is both badge and course number. All works well until I attempt to save the record.

 

[GinaWhipp]

Correct, so why is the Badge Number a PK in the second table? It can be a FK (Foreign Key) thereby allowing many on that side as a Volunteer will take many courses so their Badge ID will need to be entered multiple times in the second table.

 

[hduvall]

Other forms queries and reports are using the table this way make a change such as this timely and difficult. Is there a way to save the record in question?

 

[GinaWhipp]

The table is set up with Badge Number as a PK. There is no way without the changes to make it except a duplicate of a field you told it was one-to-one. You need to remove its PK status.