Creating a select all button in an access front end using VBA

[Mainman]

I am creating a select all button using VBA for an Access form. The Access front end is connected to an SQL back end. It is working, with limitations: When I tick the select all box I still need to tick a single item to select all items. The code I have implemeneted is the following(me.requery does not induce a difference):

Private Sub Check45_Click()
If Check45.Value = True Then
sel = "1"
Set rst = CurrentProject.Connection.Execute("UPDATE machines.table3 set selected = 1")
End If
Me.Requery
End Sub

 

[Gasman]

Why are you setting rst?

Just execute the statement?

 

[Ranman256]

Recordset not needed.
just do:
docmd.runSql “Update table....”

 

[Mainman]

I have removed the set rst, but the issue still remains

Private Sub Check45_Click()

'Dim rst As ADODB.Recordset
'sel = "0"
If Check45.Value = True Then
    sel = "1"
    docmd.runSql "UPDATE machines.table3 set selected = 1"
End If
Me.Requery
End Sub

does not run, there is an error indication for the line containing 'docmd' line

 

[Minty]

machines.table3 is not a valid table name.

 

[Mainman]

machines.table3 is not a valid table name.

This is the table name in SQL

 

[cheekybuddha]

This is the table name in SQL

Do you mean SQLServer ? (SQL is a language)

If so, are your tables linked?

 

[Mainman]

Do you mean SQLServer ? (SQL is a language)

If so, are your tables linked?

I have added ADODB connection, so I think they should be connected. However I still need to tick a single line after hitting the select all button, in order for the select all to work.

Private Sub Check45_Click()

Dim rst As ADODB.Recordset
'sel = "0"
If Check45.Value = True Then
    sel = "1"
Set rst = CurrentProject.Connection.Execute("UPDATE machines3.tables set selected=1")
    
   'this varitation achieves the same: DoCmd.RunSQL "UPDATE HmdSolverStaging set selected = 1"

End If
Requery
End Sub

 

[cheekybuddha]

Do you mean SQLServer ? (SQL is a language)

If so, are your tables linked?

 

[Mainman]

Do you mean SQLServer ? (SQL is a language)

If so, are your tables linked?

SQL Server indeed, and they are linked.

 

[Mainman]

I have found the solution, which might seem quite obvious in the end.
I needed to requery the form, the rows are now automatically marked as selected in SQL server as well.

Now I just have to figure out how to unselect them

Private Sub Check45_Click()

    Dim rst As ADODB.Recordset
    sel = "0"
    If Check45.Value = True Then
        sel = "1"
    End If
    Set rst = CurrentProject.Connection.Execute("UPDATE machines3.table set selected=1")
  
    'RefreshTableTab
    subFrmHmdtable.Form.Requery
    Exit Sub
  




End Sub

 

[cheekybuddha]

SQL Server indeed, and they are linked.

I'm glad you have found the solution in Post#12

It might also have worked without the extra ADO stuff, eg:

With Me
  CurrentDb.Execute "UPDATE machines3_table SET [Selected] = " & .Check45, dbFailOnError  ' Use the name of the table as it is linked in Access
  .subFrmHmdtable.Form.Requery
End With