Cannot save record

[magicscreen]

I am using windows 7 64 bit 8 gigs of memory Access 2007

I am developing a tracking system
I have a main form and can have one or more employees woring on the problem

I created a continuous subform using a table named tblEmployeeAssignment.

One of the fields on the form is a combo box that is bound to a field named EmpFullName in my Employee table

When I try to save a record, I receive a message stating that the record could not be saved because it would create a duplicate.

However, when I close the form "without saving" the record, the record is actually saved in the tblEmployeeAssignment table.

There is only this one one record in the table.

It looks like the saving of the record tries to do so in the current record and the next record but I am not sure.

After the record is saved in the table, I close and reopen the form. The form does not repopulate even though I have a recordsource set as follows:
Me.RecordSource = "Select * From tblEmployeeAssignment Where EmpProblemID=1".

I know this is the correct EmpProblemID because I am force feeding it manually.

Primary Key fields of tblEmployeeAssignment:
ProblemID, EmpID


The fields on the form are: ProblemID, EmpID, FirstName, LastName FullName (ComboBox) and HoursWorked.

The combobox is filled from the Employee table.

Is there any way I can find out what table is having the duplication problem?

I tried to set up a recordset to save the assignment record and then set the RecordSource of the form to give me all tblEmployeeAssignment records for the current issue.

When I received the error, I commented out the recordsource and tried to do a Requery but still got the same error.

I set the ProblemID manually to 1 for the test.

After saving the recordset, I try to repopulate the form by resetting the recordsource but nothing appears on the form.

Since the EmpID and ProblemID fields are null, I need to manually set the EmpID on the form or it doesn't get set and I wind up with a "Key Field Cannot Be Blank" error.

This is my sub:

Private Sub cmbEmployee_AfterUpdate()

On Error GoTo Err_cmbEmployee_AfterUpdate

intProbID = Me.Parent.ProblemID

Set dbAssignment = CurrentDb
Set rsAssignment = dbAssignment.OpenRecordset("Select * From tblEmployeeAssignment", dbOpenDynaset)

rsAssignment.FindFirst "EmpID='" & cmbEmployee.Column(0) & "' and ProblemID=" & intProbID

If rsAssignment.NoMatch Then
rsAssignment.AddNew
rsAssignment!EmpID = Me!cmbEmployee.Column(0)
rsAssignment!ProblemID = intProbID
Else
rsAssignment.Edit
End If

rsAssignment!EmpLastName = Me!cmbEmployee.Column(1)
rsAssignment!EmpFirstName = Me!cmbEmployee.Column(2)
rsAssignment!EmpFullName = Me!cmbEmployee.Column(3)
rsAssignment!Hours = Me.Hours

rsAssignment.Update
rsAssignment.Close

DoEvents

Me.RecordSource = "Select * From tblEmployeeAssignment Where ProblemID=" & intProbID

Exit Sub

Err_cmbEmployee_AfterUpdate:

If Err.Number = 3058 Then
Exit Sub
End If

MsgBox Err.Number & " " & Err.Description
Exit Sub
Resume Next
End Sub

 

[kjv1611]

You need to post this in the correct forum:
forum702

 

[TheAceMan1]

How are ya magicscreen . . .

[blue]When I try to save a record ...[/blue]

Just how are you saving?

[blue]When I try to save a record, I receive a message stating that [purple]the record could not be saved because it would create a duplicate[/purple].[/blue]

Did you check to see if you actually entered a duplicate?

[blue]Primary Key fields of tblEmployeeAssignment: [purple]ProblemID, EmpID[/purple][/blue]

Are you saying [purple]ProblemID, EmpID[/purple] is a [blue]compound primarykey[/blue]?

Are the mainform/subform synchronized with the master/child link properties?

[blue]To save alot of additional questions[/blue] you may want to consider uploading a scaled down model of the db to say [link 4shared.com]4Shared[/url] ([purple]its free![/purple]). Once uploaded 4Shared will provide an [blue]http address[/blue] you can post where we can go to download.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[magicscreen]

TheAceMan1, thanks for your help.

I fixed the issue with the following code:

I first filled form with the the compound key fields.
I then did a Refresh to save the record with key fields and another field called Hours.

I then created a record set and did an "EDIT" so no new record would be created and this solved my issue.

The code is below.

Private Sub cmbEmployee_AfterUpdate()

On Error GoTo Err_cmbEmployee_AfterUpdate

intProbID = Me.Parent.ProblemID
Me.EmpID = Me!cmbEmployee.Column(0)
Me.ProblemID = intProbID
Me.Hours = Nz(Me.Hours, 0)

Me.Refresh

Set dbAssignment = CurrentDb
Set rsAssignment = dbAssignment.OpenRecordset("Select * From tblEmployeeAssignment Where EmpID='" & Me!EmpID & "' And ProblemID=" & intProbID, dbOpenDynaset)

rsAssignment.Edit

rsAssignment!ProblemID = intProbID
rsAssignment!EmpID = Me!EmpID
rsAssignment!EmpLastName = Me!cmbEmployee.Column(1)
rsAssignment!EmpFirstName = Me!cmbEmployee.Column(2)
rsAssignment!EmpFullName = Me!cmbEmployee.Column(3)
rsAssignment!Hours = Nz(Me.Hours, 0)

rsAssignment.Update
rsAssignment.Close

Exit Sub

Err_cmbEmployee_AfterUpdate:
If Err.Number = 3058 Or Err.Number = 3022 Then
Exit Sub
End If

MsgBox Err.Number & " " & Err.Description
Exit Sub
Resume Next
End Sub

This technique also allows me to use a ComboBox bound to a field. My original issue was that when I selected a value from a ComboBox in a continuous form, all records would contain the same selected value. The above code fixed the issue.

I am doing this operation because I am trying to learn Access 2007. I know Access up to 2003. The database is disjointed because I am trying different things to get a feel for Access 2007.

I eventually want to create a Maintenance Tracking system for an apartment building.

The database is now loaded onto 4Shared.

Thanks again for your help.

 

[magicscreen]

I forgot to tell you that I have a Twitter link as magicscreen2010 and this is where I think I set up the file sharing. I put the entire database in 4Shared.