Help with record saving (1 Viewer)

[PeterWieland]

I think I am at the stage now where I can't see the wood for the trees!

I have a one table db with the following fields ID(Autonumber)(PK), RollNumber,LessonNo,DataField1,DataField2,DataField3.....etc.

I have a unique index on RollNumber and LessonNo, the idea being that each RollNumber can only have one entry per Lesson Number. RollNumber is aquired from the users login.

I only need the one table, as other info will be taken from a different database at report run-time.

I have a form with an Unbound text box txtLessonNo and a SubForm that shows all of the data from the table, with it's Child field set as LessonNo (which is bound to the table) and Master as txtLessonNo on the master form.

I have a 'Save' button on the SubForm with the following code:

Private Sub cmdSave_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "ReturnUsersRecords", cn, , adLockOptimistic

With rs
.AddNew
![RollNumber] = Environ("username")
![LessonNo] = Me.txtLessonNo
![Objective1] = Me.grpObjective1
![Objective2] = Me.grpObjective2
![Objective3] = Me.grpObjective3
![Q2] = Me.txtQ2
![Q3] = Me.txtQ3
![Q4] = Me.txtQ4
![Q5] = Me.txtQ5
![Q6a] = Me.txtQ6a
![Q6b] = Me.txtQ6b
![Q6c] = Me.txtQ6c
.Update
End With
rs.Close
End Sub

This works up to a point. If the user types a new lesson number into the master form, a blank record is created, which the user can then save. If they type in an existing lesson number, the relevant record is displayed, but when they click the 'Save' button, a duplicate record is attempted to be created, but can't because of the unique index.

What I want is when an existing record is called up and a save attempted, the user is prompted, then the modified record is updated or discarded accordingly. I have found some examples on here that do this when the normal record navigation buttons are used, but I don't want them showing as this needs to be idiot proof, it is for samll children to enter data into.

Hope this makes sense!