Insert Into works... how about Remove From?

[HowardChr via AccessMonster.com]

I know the command "CurrentDb.Execute "INSERT INTO [Input] ([Loc_Desc])
VALUES (""" & Me.List12 & """)", dbFailOnError" works. I now need a way to
delete a value from a table using a similar command. I am wandering if there
is a "REMOVE FROM" or something similar that will take an option from List12
out of a Table named "Input". Thanks again for your help!

 

[Michel Walsh]

You can DELETE a record (or many records if the condition is met by many
records):


DoCmd. Execute "DELETE * FROM tableName WHERE
fieldName=FORMS!formName!ControlNameWithValue"


That delete the entire row, or you can SET some field(s) to NULL (or other
values), while keeping the record(s)


DoCmd.Execute "UPDATE tableName SET fieldName = NULL WHERE
fieldName=FORMS!formName!ControlNameWithValue"


Note that DoCmd allows you to keep FORMS!formName!ControlName INSIDE the
SQL string, without having to care about the proper delimiters. CurrentDb,
on the other hands, does not allow that.



Hoping it may help,
Vanderghast, Access MVP

 

[HowardChr via AccessMonster.com]

OK.. I put in "DoCmd.Execute "DELETE * FROM [Input] VALUES (""" & Me.List0 &
""")", dbFailOnError" but get an error: "Method or data member not found"
and it has the area highlighted where it says: ".List0" inside of the (""" &
Me.List0 & """). Any ideas?

You can DELETE a record (or many records if the condition is met by many
records):

DoCmd. Execute "DELETE * FROM tableName WHERE
fieldName=FORMS!formName!ControlNameWithValue"

That delete the entire row, or you can SET some field(s) to NULL (or other
values), while keeping the record(s)

DoCmd.Execute "UPDATE tableName SET fieldName = NULL WHERE
fieldName=FORMS!formName!ControlNameWithValue"

Note that DoCmd allows you to keep FORMS!formName!ControlName INSIDE the
SQL string, without having to care about the proper delimiters. CurrentDb,
on the other hands, does not allow that.

Hoping it may help,
Vanderghast, Access MVP

I know the command "CurrentDb.Execute "INSERT INTO [Input] ([Loc_Desc])
VALUES (""" & Me.List12 & """)", dbFailOnError" works. I now need a way

[quoted text clipped - 4 lines]

List12
out of a Table named "Input". Thanks again for your help!

 

[Aaron Kempf]

This syntax has been depecrated

DoCmd. Execute "DELETE * FROM tableName WHERE
fieldName=FORMS!formName!ControlNameWithValue"

You can DELETE a record (or many records if the condition is met by many
records):


DoCmd. Execute "DELETE * FROM tableName WHERE
fieldName=FORMS!formName!ControlNameWithValue"


That delete the entire row, or you can SET some field(s) to NULL (or other
values), while keeping the record(s)


DoCmd.Execute "UPDATE tableName SET fieldName = NULL WHERE
fieldName=FORMS!formName!ControlNameWithValue"


Note that DoCmd allows you to keep FORMS!formName!ControlName INSIDE the
SQL string, without having to care about the proper delimiters. CurrentDb,
on the other hands, does not allow that.



Hoping it may help,
Vanderghast, Access MVP

I know the command "CurrentDb.Execute "INSERT INTO [Input] ([Loc_Desc])
VALUES (""" & Me.List12 & """)", dbFailOnError" works. I now need a way
to
delete a value from a table using a similar command. I am wandering if
there
is a "REMOVE FROM" or something similar that will take an option from
List12
out of a Table named "Input". Thanks again for your help!

 

[Michel Walsh]

You mean ?

DELETE * FROM tableName

Well, Access 2007 automatically adds the * even if I leave it out. So,
while

DELETE FROM tableName

is more ... universal... it does NOT seem the first syntax is soon to be
abandoned by Microsoft, for Jet.


Vanderghast, Access MVP

This syntax has been depecrated

DoCmd. Execute "DELETE * FROM tableName WHERE
fieldName=FORMS!formName!ControlNameWithValue"

You can DELETE a record (or many records if the condition is met by many
records):


DoCmd. Execute "DELETE * FROM tableName WHERE
fieldName=FORMS!formName!ControlNameWithValue"


That delete the entire row, or you can SET some field(s) to NULL (or
other
values), while keeping the record(s)


DoCmd.Execute "UPDATE tableName SET fieldName = NULL WHERE
fieldName=FORMS!formName!ControlNameWithValue"


Note that DoCmd allows you to keep FORMS!formName!ControlName INSIDE the
SQL string, without having to care about the proper delimiters.
CurrentDb,
on the other hands, does not allow that.



Hoping it may help,
Vanderghast, Access MVP

I know the command "CurrentDb.Execute "INSERT INTO [Input] ([Loc_Desc])
VALUES (""" & Me.List12 & """)", dbFailOnError" works. I now need a way
to
delete a value from a table using a similar command. I am wandering if
there
is a "REMOVE FROM" or something similar that will take an option from
List12
out of a Table named "Input". Thanks again for your help!

 

[Michel Walsh]

The syntax requires you supply the WHERE key word, and the field name:


DoCmd.Execute "DELETE * FROM [input] WHERE Loc_Desc =
FORMS!yourFormNameHere!List0 "



and that, is assuming that the control List0 returns one value. Inside the
query, words in ALL CAPS are key word to be typed as they are. Change the
other to fit your need, but form what I understand, input is already your
table name, and Loc_Desc your field name. Just missing the form name, it is
the form supplying the control, List0, supplying the data.



Hoping it may help,
Vanderghast, Access MVP

OK.. I put in "DoCmd.Execute "DELETE * FROM [Input] VALUES (""" & Me.List0
&
""")", dbFailOnError" but get an error: "Method or data member not found"
and it has the area highlighted where it says: ".List0" inside of the ("""
&
Me.List0 & """). Any ideas?

You can DELETE a record (or many records if the condition is met by many
records):

DoCmd. Execute "DELETE * FROM tableName WHERE
fieldName=FORMS!formName!ControlNameWithValue"

That delete the entire row, or you can SET some field(s) to NULL (or other
values), while keeping the record(s)

DoCmd.Execute "UPDATE tableName SET fieldName = NULL WHERE
fieldName=FORMS!formName!ControlNameWithValue"

Note that DoCmd allows you to keep FORMS!formName!ControlName INSIDE the
SQL string, without having to care about the proper delimiters. CurrentDb,
on the other hands, does not allow that.

Hoping it may help,
Vanderghast, Access MVP

I know the command "CurrentDb.Execute "INSERT INTO [Input] ([Loc_Desc])
VALUES (""" & Me.List12 & """)", dbFailOnError" works. I now need a
way

[quoted text clipped - 4 lines]

List12
out of a Table named "Input". Thanks again for your help!

 

[Guest]

It is:
CurrentDb.Execute "DELETE * FROM [Input] VALUES (""" & Me.List0 & """)",
dbFailOnError
--
Dave Hargis, Microsoft Access MVP

OK.. I put in "DoCmd.Execute "DELETE * FROM [Input] VALUES (""" & Me.List0 &
""")", dbFailOnError" but get an error: "Method or data member not found"
and it has the area highlighted where it says: ".List0" inside of the (""" &
Me.List0 & """). Any ideas?

You can DELETE a record (or many records if the condition is met by many
records):

DoCmd. Execute "DELETE * FROM tableName WHERE
fieldName=FORMS!formName!ControlNameWithValue"

That delete the entire row, or you can SET some field(s) to NULL (or other
values), while keeping the record(s)

DoCmd.Execute "UPDATE tableName SET fieldName = NULL WHERE
fieldName=FORMS!formName!ControlNameWithValue"

Note that DoCmd allows you to keep FORMS!formName!ControlName INSIDE the
SQL string, without having to care about the proper delimiters. CurrentDb,
on the other hands, does not allow that.

Hoping it may help,
Vanderghast, Access MVP

I know the command "CurrentDb.Execute "INSERT INTO [Input] ([Loc_Desc])
VALUES (""" & Me.List12 & """)", dbFailOnError" works. I now need a way

[quoted text clipped - 4 lines]

List12
out of a Table named "Input". Thanks again for your help!

 

[Guest]

If you have something to contribute, please do; otherwise, go annoy people
somewhere else.
--
Dave Hargis, Microsoft Access MVP

This syntax has been depecrated

DoCmd. Execute "DELETE * FROM tableName WHERE
fieldName=FORMS!formName!ControlNameWithValue"

You can DELETE a record (or many records if the condition is met by many
records):


DoCmd. Execute "DELETE * FROM tableName WHERE
fieldName=FORMS!formName!ControlNameWithValue"


That delete the entire row, or you can SET some field(s) to NULL (or other
values), while keeping the record(s)


DoCmd.Execute "UPDATE tableName SET fieldName = NULL WHERE
fieldName=FORMS!formName!ControlNameWithValue"


Note that DoCmd allows you to keep FORMS!formName!ControlName INSIDE the
SQL string, without having to care about the proper delimiters. CurrentDb,
on the other hands, does not allow that.



Hoping it may help,
Vanderghast, Access MVP

I know the command "CurrentDb.Execute "INSERT INTO [Input] ([Loc_Desc])
VALUES (""" & Me.List12 & """)", dbFailOnError" works. I now need a way
to
delete a value from a table using a similar command. I am wandering if
there
is a "REMOVE FROM" or something similar that will take an option from
List12
out of a Table named "Input". Thanks again for your help!

 

[Michel Walsh]

DELETE * FROM ... VALUES(...) ?

That is an unknown syntax to me, and it does not seem to work well in Access
2003.


Vanderghast, Access MVP

It is:
CurrentDb.Execute "DELETE * FROM [Input] VALUES (""" & Me.List0 & """)",
dbFailOnError
--
Dave Hargis, Microsoft Access MVP

OK.. I put in "DoCmd.Execute "DELETE * FROM [Input] VALUES (""" &
Me.List0 &
""")", dbFailOnError" but get an error: "Method or data member not
found"
and it has the area highlighted where it says: ".List0" inside of the
(""" &
Me.List0 & """). Any ideas?

You can DELETE a record (or many records if the condition is met by many
records):

DoCmd. Execute "DELETE * FROM tableName WHERE
fieldName=FORMS!formName!ControlNameWithValue"

That delete the entire row, or you can SET some field(s) to NULL (or
other
values), while keeping the record(s)

DoCmd.Execute "UPDATE tableName SET fieldName = NULL WHERE
fieldName=FORMS!formName!ControlNameWithValue"

Note that DoCmd allows you to keep FORMS!formName!ControlName INSIDE
the
SQL string, without having to care about the proper delimiters.
CurrentDb,
on the other hands, does not allow that.

Hoping it may help,
Vanderghast, Access MVP

I know the command "CurrentDb.Execute "INSERT INTO [Input] ([Loc_Desc])
VALUES (""" & Me.List12 & """)", dbFailOnError" works. I now need a
way
[quoted text clipped - 4 lines]
List12
out of a Table named "Input". Thanks again for your help!