Update Query

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

Guest

I'm trying to change the "Update to" in an Update query from code in the
module behind a form. I have build everything in the design view of the
Access 2000 database window and have a limited knowledge of VBA

I have Table1 with three fields, One, Two & Three. Field One is formated
for short date, field Two is a check box and field Three is formated for
text. I also have an Update query that I want to use twice in the same sub
procedure. The first time I want it to change field Two to "-1" depending on
what's in field Three. I do not want to change the date in field One this
time. Between the first & second use of the query I will append the records
to an other table. The second time I want the query to change those same
records in Table1 as follows: Field Two back to "0" and add a time interval
to field One. The time interval needs to be changeable depending on other
circumstances. The query works fine if I manually put [One] the first time
and DateAdd("yyyy",1,[One]) the second time in column one.

I've tried making a text box on a form = [One] & DateAdd("yyyy",1,[One]) and
referring to it in the query with Forms!FormName!TextBox but I get a type
conversion error.

I repeat that I'm not very knowledgable in VBA and I've had trouble trying
to use some of the code in this forum, even when I copy it directy into the
module.

Can anyone help me?
 
I have Table1 with three fields, One, Two & Three. Field One is
formated for short date, field Two is a check box and field Three is
formated for text.
Click to expand...

.... and what is the PK?
I also have an Update query that I want to use
twice in the same sub procedure. The first time I want it to change
field Two to "-1" depending on what's in field Three. I do not want
to change the date in field One this time.
Click to expand...

jetSQL = "UPDATE Table1 " & _
"SET FieldTwo = TRUE " & _
"WHERE FieldThree = ""Something"";"

db.Execute jetSQL, dbFailOnError
Between the first & second
use of the query I will append the records to an other table.
Click to expand...

jetSQL = "INSERT INTO OtherTable (FieldA, FieldB, FieldC) " & _
"SELECT FieldOne, FieldTwo, FieldThree " & _
" FROM Table1 " & _
" WHERE FieldThree = ""Something"";"

db.Execute jetSQL, dbFailOnError
The
second time I want the query to change those same records in Table1 as
follows: Field Two back to "0" and add a time interval to field One.
Click to expand...


jetSQL = "UPDATE Table1 " & _
"SET FieldTwo = FALSE, " & _
" FieldOne = DATEADD(""yyyy""," & _
Format(NumberOfYears, "0") & ", FieldOne)
"WHERE FieldThree = ""Something"";"

' always check a complex query explicitly
debug.assert vbyes = msgbox(jetSQL, vbYesNo, "Is this okay?")

' ... before actually doing it
db.Execute jetSQL, dbFailOnError


Hope that helps


Tim F
 
The following code executes on the click of a command button. (I put it
there to play with it and hopefully gain some understanding. I expected it
to update field One of Table2 to XX for each record where field Three was
Test. It didn't do anything, not even an error.

Private Sub Command0_Click()
jetSQL = "UPDATE Table2" & _
"Set One = ""XX"", " & _
"Where Three = ""Test"";"
db.Execute jetSQL
End Sub

What am I doing wrong?
 
I was able to get the following code to do what I expected. Is there any
distinct advantage of the code Tim Ferguson suggested over the RunSQL method?

Private Sub Command0_Click()
DoCmd.RunSQL "UPDATE Table2 " & _
"Set Table2.One = 'XX' " & _
"Where Table2.Three = 'Test';"
End Sub
 
I was able to get the following code to do what I expected. Is there
any distinct advantage of the code Tim Ferguson suggested over the
RunSQL method?

Private Sub Command0_Click()
DoCmd.RunSQL "UPDATE Table2 " & _
"Set Table2.One = 'XX' " & _
"Where Table2.Three = 'Test';"
End Sub
Click to expand...

The execute method gives you more control: no user warning messages,
trappable errors, longer command string (32K versus 256 chars, I think);
in general doing stuff via VBA direct on the database is better than
using the GUI (that's what DoCmd does) and hoping Access knows what you
want.

I guess you are using ADO rather than DAO -- that's why it didn't work
with "double quotes" and did with "single quotes". In that case you need
to use the .Execute method of the Connection or Command object. It's all
documented in Help.

HTH


Tim F
 
Thank you for you help. I've taken another step up the learning curve with
you assistance.
 
Back
Top