Saving record changes to table in form

[robina]

I have a form [Emp_Edit] which lets you search for a record and then edit the fields it returns. I cannot get this form to save the changes. I get the error “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 the underlying table and there are no conflicts. I think that the access is trying to save this as a new record instead of saving changes to the existing record. The form is based on the “Emp” table only. The form has seven fields. All are bound controls. 4 combo boxes and 3 text boxes. One of the combo boxes is [CboName]. It is this field that the user chooses a name and it then populates the rest of the controls. A text box [txtEmpID] is the PK and is auto populated by the selection in [CboName]. That all works fine, but as I stated, it won’t save the changes instead it is trying to save a new record. Thank you for your help.

 

[boblarson]

Your description sounds like the CboName is bound to the field when it shouldn't be. It should be unbound and use the appropriate search code to find the record:

Private Sub CboName_AfterUpdate()
Dim rst As DAO.Recordset
 
Set rst = Me.RecordsetClone
rst.FindFirst "[EmployeeName]=" & Chr(34) & Me.CboName & Chr(34)
 
If rst.NoMatch Then
   Msgbox "That record doesn't exist"
Else
   Me.Bookmark = rst.Bookmark
End if
 
rst.Close
Set rst = Nothing
End Sub

 

[robina]

Thank you for such a quick response. I used your code and the debugger stopped and highlighted ".Close" the Compile error is "Invalid or unqualified reference."

 

[boblarson]

Sorry, somehow a space got in between rst and .Close it should read

rst.Close

I've fixed it in the code above too.

 

[robina]

Thank you so much. I fixed that and then when i make a selection from the combobox i get the message "that record doesn't exist", At this point all of my controls are unbound.

 

[boblarson]

Thank you so much. I fixed that and then when i make a selection from the combobox i get the message "that record doesn't exist", At this point all of my controls are unbound.

1. Your form and all controls except the one you use for selecting which record you want should be bound.

2. You would include another combo box for that employee name field which would be bound but the one you do the search with won't be.

3. If you don't have the form bound, there is nothing for the combo to find.

4. Be sure to replace the field name with your actual field name (I used EmployeeName just as a placeholder).

5. Make sure you don't have lookups defined in the field at table level (talking TABLE level, and not form level. Lookups on forms are okay but not directly in the fields in the table). Those will screw things up. See here for why:
http://www.mvps.org/access/lookupfields.htm

 

[robina]

I changed all the controls to be bound except for the combobox I am searching by and added the combo box i am searching as a bound control so both exists. I still get "record does not exist." my form is bound to the table it is pulling from and needs to save changes to. Any more suggestions?

 

[boblarson]

You need to find out exactly what your combo box is providing. So, in the after update event put

Msgbox Me.CboName

so a message box will come up with the value that is being provided to the search. Post the message box message here.

 

[robina]

The message box showed the employee name is being provided for the search. I've been trying to attach a zipped file of the dbase or parts of it and its too large no matter what i do. Its down to the bare bones, just 3 small tables and the form. Any suggestions, what can i do to best communicate this? thank you!

 

[boblarson]

The message box showed the employee name is being provided for the search. I've been trying to attach a zipped file of the dbase or parts of it and its too large no matter what i do.

Have you remembered to run COMPACT AND REPAIR from inside the database before zipping?

 

[robina]

okay, thank you for being so patient. The dbase it attached now.

 

[boblarson]

I'll have to take a look later when I get home (about 6-7 hours from now) as I just have Access 2003 here at work and can't read ACCDB files.

Someone else is welcome to chime in if they can look at the file before I can get to it.

 

[robina]

No pressure. I don't mean to be a pain. I did save it down to 2003 but can definitely wait. thanks again.

 

[boblarson]

I just took a look and I think you'll be fine if you change the form's DATA ENTRY property from YES to NO.

 

[boblarson]

Oh, and also change your CmdUndo code to this instead:

Me.Undo

Don't try to set the controls to NULL like you are doing as that doesn't really undo the record.

 

[robina]

Allow data entry was already set to yes.

 

[boblarson]

Allow data entry was already set to yes.

I think you don't understand what that property means.

If it is set to YES then no other records that have been entered will show up. Only the records entered at the time that form is opened and the data entered will show. Once you close the form and reopen then it will be blank again.

So, I said SET IT TO NO (not yes, set it to NO)

See my original text and notice I said to change FROM yes TO no.

I just took a look and I think you'll be fine if you change the form's DATA ENTRY property from YES to NO.

 

[robina]

Thank you for your patience! This now works. You are a genius!