OK hope this helps. I've just copied the code for starters. I was also completely new to VB and only a user rather than a programmer and still managed to get it working so don't worry. It isn't perfect code but it works.
Basically I have a pupil moving from one class to another. So on the form I ask for input: FromClass, Pupil, ToClass. I then update the old class record to make it inactive, then add the new class record. However, if a new class record already exists, it just reactivates it. Below includes also the little messages and error checking that make it prettier!
Good luck- let me know how you get on.
Train2
Private Sub OK_Click()
Dim Msg, Response, NewSet, OldSet, UpdateProgress, clearbox As String
Dim Existrec As Integer
' just basic declarations for variables
' Checks if data is present and sensible in all fields and gives message accordingly
clearbox = ""
Msg = clearbox
Response = clearbox
If IsNull(Me.cboFromclass) Or IsNull(Me.cboPupil) Or IsNull(Me.cboToclass) Then
Msg = MsgBox("Sorry - not enough information entered. Please enter all fields.", vbOKOnly)
ElseIf (Me.cboFromclass) = (Me.cboToclass) Then
Msg = MsgBox("Please check the fields - at the moment the classes are the same.", vbOKOnly)
Else
' updates old class with deactive flag
OldSet = "Update T_ClassPupil " & _
"SET [Active] = FALSE " & _
"WHERE [C_ID]='" & [cboFromclass] & "' AND " & _
" [P_ID]='" & [cboPupil] & "'"
' checks to see if a records for newclass already exists
Existrec = Nz(DCount("[P_Id]", "[T_ClassPupil]", "[P_Id]= '" & cboPupil & "' AND [C_Id] = '" & cboToclass & "'"

, 0)
' if not add anew record, else just update old one
If Existrec = 0 Then
NewSet = "Insert into T_ClassPupil (C_Id, P_Id) " & _
"VALUES ('" & [cboToclass] & "','" & [cboPupil] & "')"
ElseIf Existrec > 0 Then
NewSet = "Update T_ClassPupil " & _
"SET [Active] = TRUE " & _
"WHERE [C_ID]='" & [cboFromclass] & "' AND " & _
" [P_ID]='" & [cboPupil] & "'"
End If
' updates all progress records from old to new class
UpdateProgress = "Update T_Progress " & _
"SET [C_ID] = '" & [cboToclass] & "' " & _
"WHERE [C_ID]='" & [cboFromclass] & "' AND " & _
" [P_ID]='" & [cboPupil] & "'"
Msg = "You're about to move " & (Me.Firstname) & " " & (Me.Surname) & " from " & [cboFromclass] & " to " & [cboToclass] & "."
Response = MsgBox(Msg, vbOKCancel)
If Response = 1 Then
DoCmd.RunSQL OldSet
DoCmd.RunSQL NewSet
DoCmd.RunSQL UpdateProgress
' once done, clear up all fields and blank them.
Msg = (Me.Firstname) & " " & (Me.Surname) & " is now in " & [cboToclass] & "."
Response = MsgBox(Msg, vbOK)
[cboFromclass] = clearbox
[cboToclass] = clearbox
[cboPupil] = clearbox
[Firstname] = clearbox
[Surname] = clearbox
[Tutorgroup] = clearbox
Else
'Do nothing
End If
End If
End Sub