Mass table changes with VB

[Guest]

if I wanted to do something (in VB) like,

if commandbutton is clicked,

filter column A in table by criteria: "X"
filter column B by criteria: "Y"

now with X and Y as filters,
change all the data in column C to "Z"

anyone know how i can do that?

Thanks~

 

[Marshall Barton]

if I wanted to do something (in VB) like,

if commandbutton is clicked,

filter column A in table by criteria: "X"
filter column B by criteria: "Y"

now with X and Y as filters,
change all the data in column C to "Z"

Be sure to test on a copy of your table:

Set db = CurrentDb()
strSQL = "UPDATE table SET [C] = 'Z' " _
& "WHERE [A] = 'X' And = 'Y' "
db.Execute strSQL, dbFailOnError

 

[Guest]

I can't seem to get any change on the table.
the code looks like,

Private Sub alertassignallcommand_Click()

Dim strSQL As String
strQuote = Chr$(34)
Set db = CurrentDb()
strSQL = "UPDATE AlertTable SET [Assigned] = 'Z'"
' & "WHERE [ILEC] = " & strQuote & Forms!SeanIn!alertILECtext & strQuote
& " And [ST] = " & strQuote & Forms!SeanIn!alertstatetext & strQuote

db.Execute strSQL, dbFailOnError
' DoCmd.RunSQL strSQL


End Sub

AlertTable is the table that the subform in the main form queries

I was hoping this code would fill the entire "Assigned" Column with 'Z'
it didn't do anything though =(

I wasn't sure what db meant either...

if I wanted to do something (in VB) like,

if commandbutton is clicked,

filter column A in table by criteria: "X"
filter column B by criteria: "Y"

now with X and Y as filters,
change all the data in column C to "Z"

Be sure to test on a copy of your table:

Set db = CurrentDb()
strSQL = "UPDATE table SET [C] = 'Z' " _
& "WHERE [A] = 'X' And = 'Y' "
db.Execute strSQL, dbFailOnError

 

[Marshall Barton]

Seems like you tried to cram the entire SQL statement in one
line and then hit the enter key when it got too long.

Private Sub alertassignallcommand_Click()
Dim db As DAO.Database
Dim strSQL As String
Dim strQuote As String
strQuote = Chr$(34)
Set db = CurrentDb()
strSQL = "UPDATE AlertTable " _
& "SET [Assigned] = 'Z' " _
& "WHERE [ILEC] = " & strQuote & _
Forms!SeanIn!alertILECtext & strQuote _
& " And [ST] = " & strQuote & _
Forms!SeanIn!alertstatetext & strQuote
db.Execute strSQL, dbFailOnError
End Sub
--
Marsh
MVP [MS Access]

I can't seem to get any change on the table.
the code looks like,

Private Sub alertassignallcommand_Click()

Dim strSQL As String
strQuote = Chr$(34)
Set db = CurrentDb()
strSQL = "UPDATE AlertTable SET [Assigned] = 'Z'"
' & "WHERE [ILEC] = " & strQuote & Forms!SeanIn!alertILECtext & strQuote
& " And [ST] = " & strQuote & Forms!SeanIn!alertstatetext & strQuote

db.Execute strSQL, dbFailOnError
' DoCmd.RunSQL strSQL


End Sub

AlertTable is the table that the subform in the main form queries

I was hoping this code would fill the entire "Assigned" Column with 'Z'
it didn't do anything though =(

I wasn't sure what db meant either...

Be sure to test on a copy of your table:

Set db = CurrentDb()
strSQL = "UPDATE table SET [C] = 'Z' " _
& "WHERE [A] = 'X' And = 'Y' "
db.Execute strSQL, dbFailOnError

 

[Guest]

That worked, thanks MB

Seems like you tried to cram the entire SQL statement in one
line and then hit the enter key when it got too long.

Private Sub alertassignallcommand_Click()
Dim db As DAO.Database
Dim strSQL As String
Dim strQuote As String
strQuote = Chr$(34)
Set db = CurrentDb()
strSQL = "UPDATE AlertTable " _
& "SET [Assigned] = 'Z' " _
& "WHERE [ILEC] = " & strQuote & _
Forms!SeanIn!alertILECtext & strQuote _
& " And [ST] = " & strQuote & _
Forms!SeanIn!alertstatetext & strQuote
db.Execute strSQL, dbFailOnError
End Sub
--
Marsh
MVP [MS Access]

I can't seem to get any change on the table.
the code looks like,

Private Sub alertassignallcommand_Click()

Dim strSQL As String
strQuote = Chr$(34)
Set db = CurrentDb()
strSQL = "UPDATE AlertTable SET [Assigned] = 'Z'"
' & "WHERE [ILEC] = " & strQuote & Forms!SeanIn!alertILECtext & strQuote
& " And [ST] = " & strQuote & Forms!SeanIn!alertstatetext & strQuote

db.Execute strSQL, dbFailOnError
' DoCmd.RunSQL strSQL


End Sub

AlertTable is the table that the subform in the main form queries

I was hoping this code would fill the entire "Assigned" Column with 'Z'
it didn't do anything though =(

I wasn't sure what db meant either...

Kaseano wrote:
if I wanted to do something (in VB) like,

if commandbutton is clicked,

filter column A in table by criteria: "X"
filter column B by criteria: "Y"

now with X and Y as filters,
change all the data in column C to "Z"

Be sure to test on a copy of your table:

Set db = CurrentDb()
strSQL = "UPDATE table SET [C] = 'Z' " _
& "WHERE [A] = 'X' And = 'Y' "
db.Execute strSQL, dbFailOnError