RunSQL update statement doesn't work : Having a hard time pls help

[g]

Hi, I'm aware that alot of discussion in this forum had tackled my problem
but still I can figure out what I'm missing here.

What I'm trying to accomplish is to update my table (Name: Tbl_Unit , Type:
Yes/No) once an event happen & criteria according to what been selected in my
combo box (Afterupdate of my combo box name combo3). My statement is this:

DoCmd.RunSQL "UPDATE[Tbl_Unit]SET [Availability]= '-1'where
[tbl_Unit].[UnitType]=Form1!Combo3"

The result I'm getting here is a pop up parameter Form1!Combo3.

What am I missing!!!!

Thank you very much

 

[Cheese_whiz]

Hi g,

Try = Forms!Form1!Combo3
or
=Me.Combo3

HTH,
CW

 

[Stuart McCall]

Hi, I'm aware that alot of discussion in this forum had tackled my problem
but still I can figure out what I'm missing here.

What I'm trying to accomplish is to update my table (Name: Tbl_Unit ,
Type:
Yes/No) once an event happen & criteria according to what been selected in
my
combo box (Afterupdate of my combo box name combo3). My statement is this:

DoCmd.RunSQL "UPDATE[Tbl_Unit]SET [Availability]= '-1'where
[tbl_Unit].[UnitType]=Form1!Combo3"

The result I'm getting here is a pop up parameter Form1!Combo3.

What am I missing!!!!

Thank you very much

The reference to Form1!Combo3 needs to be concatenated onto the end of the
string, like this:

.... [tbl_Unit].[UnitType]=" & Form1!Combo3

 

[Carl Rapson]

A few things:

1. Concatenate the value from Combo3 onto your SQL statement
2. Use True instead of -1 for the Yes/No field
3. Make sure you have spaces where they are needed

DoCmd.RunSQL "UPDATE [Tbl_Unit] SET [Availability] = True where
[tbl_Unit].[UnitType]='" & Form1!Combo3 & "'"

Note that quotes are not needed around the True, and I'm assuming UnitType
is a text value so I put quotes around it.


Carl Rapson

 

[g]

O yah, I forgot the "Forms!" & that works. Thank you very much cheese_whiz. I
think "me" wont work in SQL as far as I know though.

Hi g,

Try = Forms!Form1!Combo3
or
=Me.Combo3

HTH,
CW

Hi, I'm aware that alot of discussion in this forum had tackled my problem
but still I can figure out what I'm missing here.

What I'm trying to accomplish is to update my table (Name: Tbl_Unit , Type:
Yes/No) once an event happen & criteria according to what been selected in my
combo box (Afterupdate of my combo box name combo3). My statement is this:

DoCmd.RunSQL "UPDATE[Tbl_Unit]SET [Availability]= '-1'where
[tbl_Unit].[UnitType]=Form1!Combo3"

The result I'm getting here is a pop up parameter Form1!Combo3.

What am I missing!!!!

Thank you very much

 

[g]

Carl,

Thanks for your reply also, but it didn't work as I tried it. Error says
Object required.
UnitType is a text field.

Using the Forms!Form1.Combo3 as parameter works perfectly.

But still thank you again.

A few things:

1. Concatenate the value from Combo3 onto your SQL statement
2. Use True instead of -1 for the Yes/No field
3. Make sure you have spaces where they are needed

DoCmd.RunSQL "UPDATE [Tbl_Unit] SET [Availability] = True where
[tbl_Unit].[UnitType]='" & Form1!Combo3 & "'"

Note that quotes are not needed around the True, and I'm assuming UnitType
is a text value so I put quotes around it.


Carl Rapson

Hi, I'm aware that alot of discussion in this forum had tackled my problem
but still I can figure out what I'm missing here.

What I'm trying to accomplish is to update my table (Name: Tbl_Unit ,
Type:
Yes/No) once an event happen & criteria according to what been selected in
my
combo box (Afterupdate of my combo box name combo3). My statement is this:

DoCmd.RunSQL "UPDATE[Tbl_Unit]SET [Availability]= '-1'where
[tbl_Unit].[UnitType]=Form1!Combo3"

The result I'm getting here is a pop up parameter Form1!Combo3.

What am I missing!!!!

Thank you very much

 

[Tony Toews [MVP]]

DoCmd.RunSQL "UPDATE[Tbl_Unit]SET [Availability]= '-1'where
[tbl_Unit].[UnitType]=Form1!Combo3"

You've got your answer but I thought I'd comment on Docmd.RunSQL.

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/