Error 3022 on Form Refresh

[Cheesewoman]

Sorry if this is a total newbie and over-asked question, but I have been trawling the internet for hours (...days) and cannot seem to find an answer that works...

I have an Access 2010 database with a form that allows me to input data which is saved into two tables (i.e. Orders and OrderDetails). I use a simple select query to bind the form to the tables.

When the form loads it asks for an order number, which it uses to pull details from the Orders table and auto-fill some fields on the form (these fields are not enabled as they are predefined, such as the order date).

So, when the order number is entered my VBA code does a Form.Refresh, which then breaks the code with the Run-time error 3022 - "The changes you requested to the table were not successful yada yada.." (I'm sure you all know the one)...

I have checked that there are no duplicated keys in my tables, I have checked that what should be indexed, is indexed. I have done a compact and repair on the database, and even created a new database and imported all the tables (used new data). Still get the same issue.

Please can someone help me figure out where I am going wrong? I haven't really done anything with Access for years, so I am probably missing something really obvious

 

[burrina]

Do you have error handling for your code? Example:

On Error GoTo Errhandler
'all your code
Exit Sub
ErrHandler:
'what should happen in case of an error

 

[Cheesewoman]

Thanks for the quick reply!

Not at the moment, I did try using On Error Resume Next, but of course it then doesn't do the refresh which is a bit of an issue.

To be honest I wanted more to get to the bottom of why the error was occurring in the first place... not a fan of workarounds if I can avoid them

 

[burrina]

Is this a main form/subform setup? What exactly is your refresh form code? When is it being called? Also, sometimes it is necessary to trap errors. Maybe take a look at this discussion.

http://answers.microsoft.com/en-us/office/forum/office_2007-access/trapping-key-violaton-error-3022/1c55e25f-024a-46aa-98a6-6a8070555798

 

[MarkK]

Doesn't that error message--the yada yada part--actually tell you exactly what went wrong? What is the rest of the message?

 

[Cheesewoman]

Thanks, I will take a look at the thread.

It's just a main form that I'm using, no sub forms. The code is executed on form load;

Sub LoadProcessOrder()
    Dim procOrder As Variant
    procOrder = ""
    
    Do
        procOrder = InputBox("Please enter a Process Order number", "Enter Process Order")
        If procOrder = vbNullValue Then
            Exit Sub
        ElseIf IsNumeric(procOrder) And Len(procOrder) = 7 Then
            txt_procOrderNo.Value = procOrder
        Else
            procOrder = ""
        End If
    
    Loop While procOrder = ""
    
   ' On Error Resume Next
    Form.Refresh
End Sub

My understanding might be a bit off on the trapping part, but I'll look into it...

 

[Cheesewoman]

Doesn't that error message--the yada yada part--actually tell you exactly what went wrong? What is the rest of the message?

The entire error is:

Run-time error '3022':

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

I have checked my primary keys, indexes and relationships and they are all set up fine. I have also checked for duplicate data in all of my tables and there is none. So actually, what the error message tells me is kinda useless unless I know more about the underlying issues...

I used "yada yada" because this is a well known issue and most people who know about it would probably already know what I'm talking about.

 

[JHB]

Is "txt_procOrderNo" a control on the form, then add a "Me." in front of it, (Me.txt_procOrderNo, forget the Value part it is for no use)?
Why are you doing a Form.Refresh?
Could you post a stripped version of your database with some sample data, (zip it) + name of the form in which you've the problem.

 

[spikepl]

As MarkK said, it's the yada-part! It tells you pretty accurately what Access doesn't like.

A Refresh saves current record. So if you are looking for an existing order, your mechanics apparently attempt to save a new order with that existing order number , and hence yada yada!

Put up a copy as JHB suggested, because your entire setup sounds quite unusual.