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.

 

[Pat Hartman]

Assuming you are selecting all the rows of a subform, the select clause of the append query, needs a where clause

Where theFK = Forms!mainform!thePK

It also might need a second argument if you are appending them to a different parent record.

You, of course have told us only part of the problem. We have no idea what you are intending to do with the selected records. Selecting rows as you seem to want to do, requires a bound field and as someone mentioned, you actually need to run the query. BUT once the records are selected, then what??????????

Also, selecting batches of records this way is dangerous in a multi-user environment. What happens if two people select different sets of records?

 

[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

 

[Pat Hartman]

Now I just have to figure out how to unselect them

You might want to read #11.