Mass table changes with VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

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~
 
Kaseano said:
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"
Click to expand...


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
 
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...



Marshall Barton said:
Kaseano said:
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"
Click to expand...


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
Click to expand...
 
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...


Marshall Barton said:
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
Click to expand...
Click to expand...
 
That worked, thanks MB

Marshall Barton said:
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"
Click to expand...

Marshall Barton said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top