acCmdSaveRecord Produces Duplicate Error (3022)

[Carol W.]

I am posting this for my husband PaulW on this board.

He has a routine (see code below) that enables the user to update a form whose recordsource is a query which is a join between two tables. See attachment. After the recordsource is updated, the goal is to print a report using the updated data (see code).

If the form is updated and then closed and then reopened by the user, all is well. The report is able to be printed. However, if the user clicks the command75 button after he/she updates the form, an error 3022 is produced on the
DoCmd.RunCommand acCmdSaveRecord statement.

Is there an alternative to using this statement to update the record source? We are not even sure which of the two tables is producing the duplicate error.

query screenshot.jpg

Code: Select all

Private Sub Command75_Click()
Dim strwhere As String
On Error GoTo Err_Command75_Click

DoCmd.RunCommand acCmdSaveRecord

frm256ErrorMsg = ""
strwhere = "[tbl330id] = " & Me.[badge number]

If IsNull(Badge_Number) Then
   frm256ErrorMsg = "Please search for a member before pressing ""Print A Badge"" button"
   Exit Sub
End If
DoCmd.OpenReport "rptBadgeVolunteer", acViewPreview, , strwhere


GoTo Exit_Command75_Click

    Screen.PreviousControl.SetFocus
    DoCmd.FindNext

Exit_Command75_Click:
    
  Exit Sub

Err_Command75_Click:
    MsgBox Err.Description
    Resume Exit_Command75_Click
    
End Sub

Thanks, in advance.

[HansV]

Has a relationship between the tables been defined? If not, please create one.
Is it possible to enforce referential integrity or do you get an error message when you try to do so?

[Carol W.]

Hans,

Thanks for the reply.

When I originally posted, there was no relationship between the two tables. I have since created one (tried all three join options) with no success. We're still getting the duplicate errror message.

The "enforce referential integrity" option is greyed out.

[HansV]

The "enforce referential integrity" option is greyed out.

That might be the cause of the problem - it means that it's not clear how the records in the two tables are related. Without knowing the structure of the database it's hard to say more about it.

[Carol W.]

We'll try to strip it down and compact it to a small enough size (256 KB ???), probably tomorrow (Monday).

Thanks.

[HansV]

Yes, the maximum size for an attachment is 256 KB. You need to zip an Access database in order to attach it.

[Carol W.]

Hans,

Attached is a stripped down, compacted, zip file containing a code.mdb and a data.mdb.

First, obviously, after you extract the two files, run the Linked Table manager and link all tables to the data.mdb file.

To reproduce the issue, please do the following:

• Click on Greeter Menu button (blue) on main menu form
  Click Volunteer Maintenance button
  Click Add New Record
  Enter first name and last name
  Click Volunteer checkbox on left side of form
  Click Save Record button on bottom of form
  Click Return to Menu
  Click Return to Menu again
  From main menu, click on Foreperson button (yellow)
  Enter Foreperson's password, which is blessings (lower case)
  Click Enter button
  Click Confer Qualifications button
  From the dropdown list on the right side of form, select person you just added above (list is in first name order, per user's request)
  Click any of the check boxes starting with Glue Boards (e.g. Drill Axle Holes). Today's date will be filled in.
  Click Print A Badge button. This is where the code errors out with a 3022.

Thank you so much, in advance.

[HansV]

See the attached version; I had to change quite a bit to make it work. You'll have to run the Linked Table Manager again of course.

Toys4Smiles.zip

[Carol W.]

Hans,

Thanks, as always.

We are in the process of importing your code into the main code.mdb file and changing the relationships.

If we have any questions on the latter, we'll post back.

Thanks again.

[Carol W.]

Hans,

Everything works well in the mdb you sent back. However, we imported your frm-1-2-0 ConferQualifications module into the existing code.mdb file and we get the following:

recordset not updateable.jpg

(I think this was the reason we used dynaset (inconsistent updates) recordset type in the first place. )

Also, we cannot get the relationships to look the same as in your data.mdb file. We can't get the 1 symbol and infinity symbol to show on the relationship lines.

BTW, we're using Access 2010, if that makes a difference.

Thanks.

[HansV]

As I wrote, I had to change a few things.
In the BackEnd:
I used the Query Wizard to create a Find Unmatched Query that found "orphan" records in the ShopQualification table, then removed the offending record.
I removed the 0 default value from number fields in the ShopQualification and People Table tables.
In the FrontEnd:
I changed the design of the qryTrainingQualification query.
I changed the Row Source of Combo97 - the bound column now corresponds to the badge number, not to the name.
I changed Combo97_AfterUpdate considerably - it doesn't use a record set but goes to a new record on the form itself.

[Carol W.]

Thank you, again, Hans.

We misunderstood and thought that the only object we needed to import was the form.

I followed your steps and was able to successfully get the form to work properly. Also, the relationships now show as yours do in the data.mdb. I assume that once referential integrity is able to be checked and enforced, the 1-1 and 1-infinity symbols show on the diagram. Am I correct?

There is still one remaining (minor) issue - when combo97 is first clicked for a newly created volunteer record, text66 and text67 are not populated with the first and last names, respectively. The rest of the form now works properly. However, if I bring up a volunteer who already has a shop qualification record (perhaps to add another qualification), text66 and text 67 are populated properly.

Any ideas on what we can do to fix this?

Once, again, as we say here in the States -- You're the Man!

[HansV]

In the present setup, there can be only one ShopQualification record per person. Is that what Paul wants? If so, it would be better to merge the two tables into one. This would make everything a lot easier.

(Even better would be to normalize the database, but I don't think you want to go into that now)

[Carol W.]

In the present setup, there can be only one ShopQualification record per person. Is that what Paul wants?

Yes. He says he was "trying to do it the right way" (i.e. create separate tables) because fewer than 1/3 of the records in the People Table will have records in the Shop Qualification table. Most people (e.g. Donors) will not have any Shop Qualification records.

We will probably take your suggestion and combine the two tables. It may not be the optimal design but it will be less complicated to maintain the 1-2-0 form (Recordsource will be the People Table only).

Thanks as always.

[HansV]

Access handles empty values efficiently, so the overhead of having a lot of empty fields is not a problem.

[Carol W.]

I'm sure that's true. I also told him that disk space was cheap!

BTW, the reason I'm posting this instead of Paul is that he said that I "speak Hans" better than he does. I take that as a compliment!

[JohnH]

There is still one remaining (minor) issue - when combo97 is first clicked for a newly created volunteer record, text66 and text67 are not populated with the first and last names, respectively. The rest of the form now works properly. However, if I bring up a volunteer who already has a shop qualification record (perhaps to add another qualification), text66 and text 67 are populated properly.

As far as I can see, this issue is still outstanding.

This happens because the names of the controls does not match the names of the fields. I have not looked at the sample, but I have had this happen where the After Update event is set to fill in fields based on the field names, not the control names. So the values are being written to the table, but not immediately displayed.

[Carol W.]

John,

Yes, given the current design, this issue is still outstanding. However, we plan on taking Hans' suggestion and redesign, combining the ShopQualification table fields into the People Table. We haven't had a chance to do that yet.

Thanks for the reply.