SQL Code

[Ange Kappas]

Hi,
I have a form called DEPARTURES which has a STATUS field and a check
Box field called CHECK OUT.
What I want to do by using SQL in code is to set on a Table called RESPEL
the STATUS field to OUT when I check the check box, which checks the
corresponding RESNO field in the DEPARTURES form to be the same as the RESNO
field in the RESPEL form.

I tried to use the following but probably got it wrong:

Private Sub CHECK OUT_AfterUpdate()

strSQL = "Update RESPEL SET([STATUS] = OUT) WHERE DEPARTURES.RESNO =
RESPEL.RESNO"


End Sub

Any Help?

Thanks

Ange

 

[Roger Carlson]

Well, assuming your SQL is correct, all you need to do is execute it:

Private Sub CHECK OUT_AfterUpdate()
Dim strSQL as String

strSQL = "Update RESPEL SET([STATUS] = "OUT") WHERE DEPARTURES.RESNO =
RESPEL.RESNO"
CurrentDb.Execute strSQL

End Sub

I added quotes around OUT and declared the string variable.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ange Kappas]

Hi Roger,
Added the code but it returns to me COMPILE ERROR End Of Statement on the
"OUT" section.
I have the code copy paste below:

Private Sub CHECK OUT_AfterUpdate()
Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = "OUT") WHERE DEPARTURES.RESNO =
RESPEL.RESNO ""


CurrentDb.Execute strSQL

End Sub

Maybe the fields should be in Brackets[], ?

Thanks
Ange

Well, assuming your SQL is correct, all you need to do is execute it:

Private Sub CHECK OUT_AfterUpdate()
Dim strSQL as String

strSQL = "Update RESPEL SET([STATUS] = "OUT") WHERE DEPARTURES.RESNO =
RESPEL.RESNO"
CurrentDb.Execute strSQL

End Sub

I added quotes around OUT and declared the string variable.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi,
I have a form called DEPARTURES which has a STATUS field and a
check Box field called CHECK OUT.
What I want to do by using SQL in code is to set on a Table called RESPEL
the STATUS field to OUT when I check the check box, which checks the
corresponding RESNO field in the DEPARTURES form to be the same as the
RESNO field in the RESPEL form.

I tried to use the following but probably got it wrong:

Private Sub CHECK OUT_AfterUpdate()

strSQL = "Update RESPEL SET([STATUS] = OUT) WHERE DEPARTURES.RESNO =
RESPEL.RESNO"


End Sub

Any Help?

Thanks

Ange

 

[Steve Sanford]

The delimiters weren't right. Note the single quotes -- 'OUT'

'-----------
Private Sub CHECK OUT_AfterUpdate()
Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = 'OUT') "
strSQL = strSQL & "WHERE DEPARTURES.RESNO = RESPEL.RESNO "

CurrentDb.Execute strSQL, dbFailOnError

End Sub
'-----------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Roger,
Added the code but it returns to me COMPILE ERROR End Of Statement on the
"OUT" section.
I have the code copy paste below:

Private Sub CHECK OUT_AfterUpdate()
Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = "OUT") WHERE DEPARTURES.RESNO =
RESPEL.RESNO ""


CurrentDb.Execute strSQL

End Sub

Maybe the fields should be in Brackets[], ?

Thanks
Ange

Well, assuming your SQL is correct, all you need to do is execute it:

Private Sub CHECK OUT_AfterUpdate()
Dim strSQL as String

strSQL = "Update RESPEL SET([STATUS] = "OUT") WHERE DEPARTURES.RESNO =
RESPEL.RESNO"
CurrentDb.Execute strSQL

End Sub

I added quotes around OUT and declared the string variable.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi,
I have a form called DEPARTURES which has a STATUS field and a
check Box field called CHECK OUT.
What I want to do by using SQL in code is to set on a Table called RESPEL
the STATUS field to OUT when I check the check box, which checks the
corresponding RESNO field in the DEPARTURES form to be the same as the
RESNO field in the RESPEL form.

I tried to use the following but probably got it wrong:

Private Sub CHECK OUT_AfterUpdate()

strSQL = "Update RESPEL SET([STATUS] = OUT) WHERE DEPARTURES.RESNO =
RESPEL.RESNO"


End Sub

Any Help?

Thanks

Ange

 

[Ange Kappas]

Hi Steve,
Unfortunatly I am now getting a
"Run Time Error 3144 Syntax error in UPDATE statement"

with the following:

Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = 'OUT') "
strSQL = strSQL & "WHERE DEPARTURES.RESNO = RESPEL.RESNO "

CurrentDb.Execute strSQL, dbFailOnError

exactly as I have it.

Ange

The delimiters weren't right. Note the single quotes -- 'OUT'

'-----------
Private Sub CHECK OUT_AfterUpdate()
Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = 'OUT') "
strSQL = strSQL & "WHERE DEPARTURES.RESNO = RESPEL.RESNO "

CurrentDb.Execute strSQL, dbFailOnError

End Sub
'-----------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Roger,
Added the code but it returns to me COMPILE ERROR End Of Statement on the
"OUT" section.
I have the code copy paste below:

Private Sub CHECK OUT_AfterUpdate()
Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = "OUT") WHERE DEPARTURES.RESNO =
RESPEL.RESNO ""


CurrentDb.Execute strSQL

End Sub

Maybe the fields should be in Brackets[], ?

Thanks
Ange

Well, assuming your SQL is correct, all you need to do is execute it:

Private Sub CHECK OUT_AfterUpdate()
Dim strSQL as String

strSQL = "Update RESPEL SET([STATUS] = "OUT") WHERE DEPARTURES.RESNO =
RESPEL.RESNO"
CurrentDb.Execute strSQL

End Sub

I added quotes around OUT and declared the string variable.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi,
I have a form called DEPARTURES which has a STATUS field and a
check Box field called CHECK OUT.
What I want to do by using SQL in code is to set on a Table called
RESPEL
the STATUS field to OUT when I check the check box, which checks the
corresponding RESNO field in the DEPARTURES form to be the same as the
RESNO field in the RESPEL form.

I tried to use the following but probably got it wrong:

Private Sub CHECK OUT_AfterUpdate()

strSQL = "Update RESPEL SET([STATUS] = OUT) WHERE DEPARTURES.RESNO =
RESPEL.RESNO"


End Sub

Any Help?

Thanks

Ange

 

[Roger Carlson]

Sorry about the delimiters on OUT.

As for your new error, my original statement was "assuming your SQL is
correct". I didn't look at the SQL very closely. You need to create a
query and get it working in the query window before you try to execute it in
VB.

As I look at it now, it looks like you have another table DEPARTURES. You
can do this one of two ways:

UPDATE DEPARTURES INNER JOIN RESPEL ON DEPARTURES.RESNO = RESPEL.RESNO SET
RESPEL.Status = True;

or

UPDATE DEPARTURES, RESPEL SET RESPEL.Status = True
WHERE RESPEL.RESNO=DEPARTURES.RESNO;

However, this is still only a guess, as I don't know your database.

Then convert the above into a string variable and execute as below.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi Steve,
Unfortunatly I am now getting a
"Run Time Error 3144 Syntax error in UPDATE statement"

with the following:

Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = 'OUT') "
strSQL = strSQL & "WHERE DEPARTURES.RESNO = RESPEL.RESNO "

CurrentDb.Execute strSQL, dbFailOnError

exactly as I have it.

Ange

The delimiters weren't right. Note the single quotes -- 'OUT'

'-----------
Private Sub CHECK OUT_AfterUpdate()
Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = 'OUT') "
strSQL = strSQL & "WHERE DEPARTURES.RESNO = RESPEL.RESNO "

CurrentDb.Execute strSQL, dbFailOnError

End Sub
'-----------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Roger,
Added the code but it returns to me COMPILE ERROR End Of Statement on
the
"OUT" section.
I have the code copy paste below:

Private Sub CHECK OUT_AfterUpdate()
Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = "OUT") WHERE DEPARTURES.RESNO =
RESPEL.RESNO ""


CurrentDb.Execute strSQL

End Sub

Maybe the fields should be in Brackets[], ?

Thanks
Ange



Well, assuming your SQL is correct, all you need to do is execute it:

Private Sub CHECK OUT_AfterUpdate()
Dim strSQL as String

strSQL = "Update RESPEL SET([STATUS] = "OUT") WHERE DEPARTURES.RESNO =
RESPEL.RESNO"
CurrentDb.Execute strSQL

End Sub

I added quotes around OUT and declared the string variable.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi,
I have a form called DEPARTURES which has a STATUS field and a
check Box field called CHECK OUT.
What I want to do by using SQL in code is to set on a Table called
RESPEL
the STATUS field to OUT when I check the check box, which checks the
corresponding RESNO field in the DEPARTURES form to be the same as
the
RESNO field in the RESPEL form.

I tried to use the following but probably got it wrong:

Private Sub CHECK OUT_AfterUpdate()

strSQL = "Update RESPEL SET([STATUS] = OUT) WHERE DEPARTURES.RESNO =
RESPEL.RESNO"


End Sub

Any Help?

Thanks

Ange

 

[Steve Sanford]

Sorry, I saw the delimiters and quit thinking.

You can't have the WHERE clause like you wrote it because the table
"DEPARTURES" sin't in the query . But since you are updating a form, the
RESNO on form DEPARTURES should be available.

Try this:

strSQL = "Update RESPEL SET([STATUS] = 'OUT') WHERE RESNO = " &
FORMS!DEPARTURES.[controlName]

Replace [controlName] with the name of the control bound to the field resno.

This might also work:

strSQL = "Update RESPEL SET([STATUS] = 'OUT') WHERE RESNO = " &
FORMS!DEPARTURES.[resno]


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Steve,
Unfortunatly I am now getting a
"Run Time Error 3144 Syntax error in UPDATE statement"

with the following:

Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = 'OUT') "
strSQL = strSQL & "WHERE DEPARTURES.RESNO = RESPEL.RESNO "

CurrentDb.Execute strSQL, dbFailOnError

exactly as I have it.

Ange

The delimiters weren't right. Note the single quotes -- 'OUT'

'-----------
Private Sub CHECK OUT_AfterUpdate()
Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = 'OUT') "
strSQL = strSQL & "WHERE DEPARTURES.RESNO = RESPEL.RESNO "

CurrentDb.Execute strSQL, dbFailOnError

End Sub
'-----------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Roger,
Added the code but it returns to me COMPILE ERROR End Of Statement on the
"OUT" section.
I have the code copy paste below:

Private Sub CHECK OUT_AfterUpdate()
Dim strSQL As String

strSQL = "Update RESPEL SET([STATUS] = "OUT") WHERE DEPARTURES.RESNO =
RESPEL.RESNO ""


CurrentDb.Execute strSQL

End Sub

Maybe the fields should be in Brackets[], ?

Thanks
Ange



Well, assuming your SQL is correct, all you need to do is execute it:

Private Sub CHECK OUT_AfterUpdate()
Dim strSQL as String

strSQL = "Update RESPEL SET([STATUS] = "OUT") WHERE DEPARTURES.RESNO =
RESPEL.RESNO"
CurrentDb.Execute strSQL

End Sub

I added quotes around OUT and declared the string variable.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi,
I have a form called DEPARTURES which has a STATUS field and a
check Box field called CHECK OUT.
What I want to do by using SQL in code is to set on a Table called
RESPEL
the STATUS field to OUT when I check the check box, which checks the
corresponding RESNO field in the DEPARTURES form to be the same as the
RESNO field in the RESPEL form.

I tried to use the following but probably got it wrong:

Private Sub CHECK OUT_AfterUpdate()

strSQL = "Update RESPEL SET([STATUS] = OUT) WHERE DEPARTURES.RESNO =
RESPEL.RESNO"


End Sub

Any Help?

Thanks

Ange

 

[Ange Kappas]

Hi All,
THANK YOU SO MUCH,
Got it working finally using the below code, thanks to your help !!!

Private Sub CHECK OUT_AfterUpdate()


strSQL = "UPDATE RESPEL SET STATUS = " & _
"'OUT' WHERE RESNO = " & Forms!DEPARTURES.[RESNO]

CurrentDb.Execute strSQL

End Sub

Ange