Update table from click on form

[jax]

I am trying to change all the entrys in a table that contain 0's to 1's when a user clicks a check box on a form. I am using the code below but it keeps erroring can anyone help?

Table is called tblChildren_Details and the Form is called frmBatch


Private Sub Check12_Click()

Dim rsttblChildren_Details As ADODB.Recordset
Dim conDatabase As ADODB.Connection
Dim strSQL As String

Set conDatabase = CurrentDb.Connection
strSQL = "Select * from tblChildren_Details where Batch = '" & 0 & "'"

Set rsttblChildren_Details = New Recordset
rsttblChildren_Details.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic

With rsttblChildren_Details
Do While Not .EOF
!Batch = 1
.Update
Loop
End With

rsttblChildren_Details.Close
conDatabase.Close
Set rsttblChildren_Details = Nothing
Set conDatabase = Nothing


End Sub


Thanks for any help

 

[KeithG]

I think you need a .edit before !Batch=1. I also noticed that in your loop you are not stepping through all the records.

 

[jax]

Hi KeithG, thanks for that, I have added .edit and .moveNext but I am still getting errors

I think i might have a problem with this line of code

Set conDatabase = CurrentProject.Connection

which is causing it to error here


rsttblChildren_Details.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic

Thanks for helping

 

[KeithG]

What error are you getting? Are you using CurrentProject.Connection or Currendb.Connetion? In your example you have CurrentDb.connection but I believe you need to use CurrentProject.Connection.

 

[jax]

"Runtime error

Data type mismatch in criteria expression"

 

[KeithG]

Are you using CurrentProject.Connection or CurrentDb.Connetion?

 

[jax]

I have tried

Set conDatabase = CurrentProject.Connection
and
Set conDatabase = CurrentDb.Connection
and
Set conDatabase = Application.CurrentDb.Connection

I admit I am just guessing and learning as I go along with the help of this Forum, so dont really know what I am doing sorry

 

[KeithG]

strSQL = "Select * from tblChildren_Details where Batch = '" & 0 & "'"

I think your SQL statement is the problem. What data type is the Batch Field? Numeric? If so try the below SQL

"Select * from tblChildren_Details where Batch =0"

 

[jax]

Yes its a numeric field

Thank you very much - that works -
Thats a real high on which to finish work on a sunny friday afternoon

Thank again