Updating a yes/no box

[graeme34 via AccessMonster.com]

Hi I have a yes/no flag I would like to update, I have had to build a temp
table to hold the yes/no box on the subform I'm using as the original query
wasnt updatable, now I have placed the following code on the On close event
of the main form:
Private Sub Form_Close()

Dim strSQL As String
Dim strSQLUpdate As String
Dim rs As Recordset

strSQL = "SELECT * FROM tblTempReceivePayment TRP " _
& "WHERE TRP.AccountName = " & "'" & Me.txtAccName _
& "'" & ";"

Set rs = CurrentDb.OpenRecordset(strSQL)

Do Until rs.EOF = True
If rs!SalesInvoicePaid = True Then
strSQLUpdate = "UPDATE tblSalesInvoice SI SET " _
& "SI.SalesInvoicePaid = True " _
& "WHERE SI.SalesInvoiceNumber = " & _
rs!SalesInvoiceNumber & ";"
End If
rs.MoveNext
Loop
End Sub

All I am trying to do is set the SalesInvoicePaid flag to true if the check
box control is ticked, I have debugged the code and it works fine even
entering the if statement, its just not updating the SalesInvoice table....???

 

[Douglas J. Steele]

You're creating a SQL statement, but you're not executing it.

You either need

CurrentDb.Execute strSQLUpdate, dbFailOnError

inside the loop, or else have a single SQL statement that updates
tblSalesInvoice based on tblTempReceivePayment, and don't bother with the
loop.

Something like the following untested air-code:

strSQLUpdate = "UPDATE tblSalesInvoice SI SET " & _
"SI.SalesInvoicePaid = True " & _
"WHERE SI.SalesInvoiceNumber IN " & _
"(SELECT SalestInvoiceNumber FROM " & _
"tblTempReceivePayment " & _
"WHERE AccountName = ""'" & Me.txtAccName & """ " & _
"AND SalesInvoicePaid = True)"

 

[graeme34 via AccessMonster.com]

Thank you Douglas
I was just about to reply, saying I'd found my error......

Having a bad day

thanks anyway

You're creating a SQL statement, but you're not executing it.

You either need

CurrentDb.Execute strSQLUpdate, dbFailOnError

inside the loop, or else have a single SQL statement that updates
tblSalesInvoice based on tblTempReceivePayment, and don't bother with the
loop.

Something like the following untested air-code:

strSQLUpdate = "UPDATE tblSalesInvoice SI SET " & _
"SI.SalesInvoicePaid = True " & _
"WHERE SI.SalesInvoiceNumber IN " & _
"(SELECT SalestInvoiceNumber FROM " & _
"tblTempReceivePayment " & _
"WHERE AccountName = ""'" & Me.txtAccName & """ " & _
"AND SalesInvoicePaid = True)"

Hi I have a yes/no flag I would like to update, I have had to build a temp
table to hold the yes/no box on the subform I'm using as the original

[quoted text clipped - 30 lines]

entering the if statement, its just not updating the SalesInvoice
table....???