Update query in vba

[tdes42]

Hi :)

I have been banging away at this patchwork piece of code for some
time...
I'm trying to create a button that will disable after it is
successfully used to add records to table1, but on a continuous form,
so that only the button related to that particular row is disabled,
but any remaining rows that have not been added the button stays
enabled.

So... I created a textbox to look like a command button, and then set
a conditional formatting based on a field in table2 (Added).

My problem area is the SQL code near the bottom. If I click the
textbox once, the records are added but table2.Added is unchanged. If
I click it again, then table2.Added is changed :)

Thank you in advance for your time, comments, help, and well
goodwill :)

Tim Payton

Private Sub cmdOK_Click()
Dim intI As Integer
Dim dtApptDate As Date
Dim strCriteria As String
Const QUOTE = """"

Dim db As Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblSchedule")

dtApptDate = Forms.frmCourses.sufCourseScheduler.Form.txtStartD ate

For intI = 1 To Forms.frmCourses.sufCourseScheduler.Form.NumAppts
rs.AddNew
rs![CourseID] = Forms.frmCourses.cboCourseName
rs![TimeTableID] =
Forms.frmCourses.sufCourseScheduler.Form.txtTimeTa bleID
rs![TrainerID] =
Forms.frmCourses.sufCourseScheduler.Form.cboTraine rID
rs![ApptDate] = dtApptDate
rs![StartTime] =
Forms.frmCourses.sufCourseScheduler.Form.txtStartT ime
rs![EndTime] =
Forms.frmCourses.sufCourseScheduler.Form.txtEndTim e
rs![StatusID] = 0
rs.Update

dtApptDate = DateAdd("d", 7, dtApptDate)
Next

rs.Close
Forms!frmCourses!sufCourseSchedule.Form.Requery

Dim strSQL As String
Dim strAdded As String
Dim lng1 As Long

strAdded = "Added"
lng1 = [Forms]![frmCourses]![sufCourseScheduler].[Form]!
[txtTimeTableID]

strSQL = "UPDATE tblScheduler SET tblScheduler.Added = " & QUOTE &
strAdded & QUOTE _
& " WHERE tblScheduler.TimeTableID = " & "" & lng1 & ""
Me.txtSQL = strSQL
Me.txtID = lng1

CurrentDb.Execute strSQL

Set rs = Nothing

Forms!frmCourses!sufCourseScheduler.Form.Requery

'DoCmd.Close
End Sub