I am working on a form created by someone else.
I am getting an error when the user tries to save a modified record.

The error is:

Click to view attachment

Here is the code behind the button:

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
If UpdSaved = False Then
UpdSaved = True
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub


If it wont save, how can I get the data to be updated?

My guess is to change the code to append, instead of update.
But, I dont know how to "write" that in to the code.

Hi,

The error message indicates that you are trying to "add" a record with the same value in a unique field of an existing record.

Find out which field is that and see if you can fix it.

Just my 2 cents...

I know what field it is, looking at the code and the error.
This particular field is not the PK, it is just indexed to not allow duplicates.

Would changing the code to append instead of updating fix the issue?
If yes, what do I change in the code I posted above?

Well, do you want to have "duplicates?"



There is nothing to change in the code because it is not specifically appending or updating a record - it's probably doing both (not at the same time).

We'll need to know more about the form function to give a valid suggestion.

Just my 2 cents...

The data being entered is the empty and loaded weight of the truck.
The record is created before the truck arrives at the warehouse to be loaded.
Once the truck is here, the tare weight has to be entered.
Once the truck is loaded, it has to have a loaded weight.

Both of those entries are made after the record is created.

So update would be right.
Why, since I am not creating a new record, wouldnt it work?

I dont know if this matters or not, but the table that holds the record is a linked table.

Hi,

Did that form ever work before? If never, then I would suggest you start over and design a new form.

Just my 2 cents...

As far as I know it did.

Here is the form, not filled in:
Click to view attachment

Most of the buttons on the right cause the same error as posted first.
Save BOL - Error
View BOL - Error
Input Seals - brings up append query warning
Print Day BOL - Error
Update Inventory - Error


Print Inspection - Works
Print COA - Works



I have copied a live database they use here that has this working in it.
However, I cannot get logged into the copied database.

Hi,

If it was working before but not now, did you make any changes to it? If not, is it just you who's getting an error? Only on your machine?

Just my 2 cents...

I am looking at an earlier version of this DB and it doesnt work there either.
I cant get logged into the live db for some reason.

If you're not getting any error messages, try stepping through the code to see what values are actually being evaluated for the login. Also, double-check the credentials you're supposed to use.

Just my 2 cents...

I removed the missing reference and I was able to log in.
However, this caused an issue with the earlier versions of Access.
They started to get the error that I resolved for myself.
I had to put the reference back into the database.
I will figure that one out myself.

But I still have the issue of the form not saving.
Even on the earlier versions of Access, you get the error.

If the user goes straight through the whole process, without quitting, it works.
But, if they save the record and move on to another task, then come back and modify later, they get the cannot save error.
To me, this makes no sense because they have to wait till the truck arrives and is weighed empty and full.
Once that is done, they have to enter the weights.
So, not allowing a save at that point baffles me.

Don't forget that the table causing this issue is a linked table.
I cant modify it.


Any ideas?

I may be on the right track here.
Here is what I am thinking.
Please chime in with any thoughts you have.



The form where I am getting the error is called GenerateBOL.
Maybe that is the problem.
When you go through the process the first time and hit the button to bring up GenerateBOL, it works fine because you are Generating a NEW BOL.
If you have already generated the BOL, when you go back to that same button, you are opening it up to generate a new BOL.
That is something it cannot do, since it was already created.

What I am thinking is to make another form, very similar, but call it ViewBOL.
They would hit this button and view the already created BOL.

Question, If I make it to where this ViewBOL form shows all the data from the GenerateBOL form, even the empty fields like weights; when the user enters weights into the fields, would that save them to the table? Therefore, modifying, and not creating a new duplicate entry.

I am glad to report that I have basically figured this issue out.
I had to create some new queries, forms, and macros to accomplish it.

I just showed it to the guy I have been working with and it works just like he wants it to.
He now has workarounds to accomplish what he need to do.

Hi,

Congratulations on finding a solution to your problem.

Continued success with your project.

I spoke too soon.
Now my new form has the same issues as the original one that was Generating the Bill of Lading.


The table that holds the data has a PK and an additional field that is also indexed to not allow duplicates.
The table is a linked table so I cant change the layout or properties.

I am updating an existing record, not generating a new one.
What should I be looking at?

Is there a way to tell the save button that if the record does not exist, generate a new record

or

If the record exists, just update the existing one, not generate a new one?

If I may be so bold: you're jumping around like water on a hot griddle. You're grasping for any straw you see that might be a workaround to the problem instead of a solution to the problem. This is not the way to do it... in general, workarounds are an extremely poor substitute for a solution, and in relational design and programming, this is even more true.

Think yourself through the solution instead of around it.

Let's define the core problem: you have a form that is able to enter a new record, but not update an existing record as it's causing a validation error on a unique index.

This can be further broken down into one of two possible problems: 1) the field has an incorrect index, or 2) the form has incorrect code logic preventing it from updating properly.

The Field has an Incorrect Index
Can this be? Who owns this backend table? Some guy off the street or some company that is responsible for the integrity of the data? Do you think that it's reasonable for this field to be indexed so, or does it make absolutely no sense whatsoever? Remember that the foundation of a database application is it's data structure: the tables and relationships - forms come after. Is it safe to assume the person who put this together knew what they were doing? How long has it been around, etc etc? Basically, if you can come to the conclusion that this index is wrong and should not be there, you stop trying to find workarounds, go to whoever's in charge and say "this index is wrong and should not be there" and then explain why you can't go creating any 'ol workaround to fix the problem.

I would guess, though I sure don't know, but I would guess that this data structure should be in somewhat decent shape. It appears as though it's been around and working for a while, which indicates that the index on the field itself is not problematic. This isn't always the case, but usually it is...

The form has incorrect code logic preventing it from updating propertly
Is this a possiblility? Is it any more or less plausible than whoever designed the backend severely screwed up a unique index? Assuming you are responsible for the form and it's code, are you completely understanding of everything that's happening at any point? Do you know what every procedure that's called is doing, and what every variable holds at any given time? Do you know the order of events like the back of your hand and are positive that there is no conflicting codes due to the intricacies of variable event firing? Until you do, you can't very well say for sure that there's not some problem in the current state of the form. A problem in the current state of the form isn't something that you ignore and instead create a workaround for, it's something you track down (though it may take days), put under your thumb and smooth over.

This whole "save the form only once" approach is junk (you said so yourself... makes no sense, right? they need to come back and update weights etc). Go ahead and scrap that. What you should be looking at is finding out the details of how the form appends and updates. Run some testing and found out exactly what the offending duplicate is: "your" record or a previous one? If so, why? etc etc. Either that or prove to someone that the index is garbage, but I think if it were me I'd want to have a pretty good handle on what was going on in my own court first.

Cheers,

I didnt quote because it was so long and didnt want to take up more space.

Thanks to jleach.

I found out something very interesting today.

Going back to yesterday; the boss and I had a discussion on why this error was happening.
We copied production to my desktop and looked at the structure.
I told him the same error had to exist in the production DB.
He swore up and down that it was not possible.
If it was happening, the users would be raising a stink about the error.

I went to the main user I have been working with and asked him to run an order through the production system.
He did and the results were interesting.

I was right all along.
There is no way, once the record is saved, to change it and not get the error.
Even in the production system, you get the error.

While talking to this user, he says in a matter of fact kind of way, "Yeah, we have always known it would error if we saved the record."

I asked him over a week ago if they had any kind of an error on the production side when they modify a saved record.
He said, "NO".

Now I find this out today.
Whatever.

So, maybe I have figured out my issue.
I was misinformed.

Hi,

Glad to hear you've shed some light on the problem. Not sure what your plan is now but if you ask me, I think it's worth it to find the root cause and fix it - unless of course that is the way they really want the db to work.

Just my 2 cents...

I think that is exactly how they want it to work.
I havent been told anything different.

Then, I guess we move on. Such a shame though...

Cheers