CurrentDb.Execute command..

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

Guest

I would like to run a sql command on a button wich inserts values from one
table into another based on values in two combo boxes.

It would have to look something like this, but it doesn't work:

CurrentDb.Execute("INSERT INTO tblNameX ([Id],[ValueX],[ValueY]) VALUES
(tblNameY.[Id], tblNameY.[ValueX], tblNameY.[ValueY] WHERE (tblNameY.[Id] = "
& cboNameX & " AND tblNameY.[Value] = " & cboNameY & ") "), dbFailOnError

Anybody sees what goes wrong??
 
What data types are Id and Value? Also, are cboNameX and cboNameY bound to
the correct field (so that referring to them returns the correct values).
 
Hai Douglas,

Thansk for your reply. I'm not sure if I understand you completely, but I
will try to answer your questions.

The values of Id and Value are Number and Text. I already made a change in
the SQL to add two extra ' ' quotes around the Text (Value).

Can I just create a simple script, a text box for instance, wich will
display the output/result of referring to both the ComboBoxes.

Thanks! You're a great helpt already!

Jochem


Douglas J Steele said:
What data types are Id and Value? Also, are cboNameX and cboNameY bound to
the correct field (so that referring to them returns the correct values).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jochem Davids said:
I would like to run a sql command on a button wich inserts values from one
table into another based on values in two combo boxes.

It would have to look something like this, but it doesn't work:

CurrentDb.Execute("INSERT INTO tblNameX ([Id],[ValueX],[ValueY]) VALUES
(tblNameY.[Id], tblNameY.[ValueX], tblNameY.[ValueY] WHERE (tblNameY.[Id] = "
& cboNameX & " AND tblNameY.[Value] = " & cboNameY & ") "), dbFailOnError

Anybody sees what goes wrong??
Click to expand...
Click to expand...
 
Jochem Davids wrote in message
I would like to run a sql command on a button wich inserts values
from one table into another based on values in two combo boxes.

It would have to look something like this, but it doesn't work:

CurrentDb.Execute("INSERT INTO tblNameX ([Id],[ValueX],[ValueY])
VALUES (tblNameY.[Id], tblNameY.[ValueX], tblNameY.[ValueY] WHERE
(tblNameY.[Id] = " & cboNameX & " AND tblNameY.[Value] = " &
cboNameY & ") "), dbFailOnError

Anybody sees what goes wrong??
Click to expand...

strSql = "INSERT INTO tblNameX ([Id],[ValueX],[ValueY]) " & _
"select Id, ValueX, ValueY " & _
"from tblNameY where id = " & cboNameX & _
" and [Value] = " & cboNameY
currentdb.execute strsql, dbfailonerror

If id or [Value] is a text field, then you would need single quotes
around it (are you sure, BTW, this is named Value and not ValueY?)

.... " and [Value] = 'ins" & cboNameY & "'"
 
RoyVidar wrote in message <[email protected]> :
Bit of paste error, sorry, this

... " and [Value] = 'ins" & cboNameY & "'"

should perhaps look more like

... " and [Value] = '" & cboNameY & "'"
 
Hai Roy,

Also thanks for your replay!

I think the SQL statement I'm using is incorrect. I should perhaps use the
UPDATE SET statement, I;m trying this right now..

This will look something like:

CurrentDb.Execute ("UPDATE tlbNameX SET (ID=tblNameY.[ID],
valueX=tblNameY.[ValueX], valueY=tblNameY.[ValueY]) WHERE ( tblNameY.[ID] =
" & cboNameX & " AND tblNameY.[ValueX] = " & cboNameY & ") "), dbFailOnError

RoyVidar said:
Jochem Davids wrote in message
I would like to run a sql command on a button wich inserts values
from one table into another based on values in two combo boxes.

It would have to look something like this, but it doesn't work:

CurrentDb.Execute("INSERT INTO tblNameX ([Id],[ValueX],[ValueY])
VALUES (tblNameY.[Id], tblNameY.[ValueX], tblNameY.[ValueY] WHERE
(tblNameY.[Id] = " & cboNameX & " AND tblNameY.[Value] = " &
cboNameY & ") "), dbFailOnError

Anybody sees what goes wrong??
Click to expand...

strSql = "INSERT INTO tblNameX ([Id],[ValueX],[ValueY]) " & _
"select Id, ValueX, ValueY " & _
"from tblNameY where id = " & cboNameX & _
" and [Value] = " & cboNameY
currentdb.execute strsql, dbfailonerror

If id or [Value] is a text field, then you would need single quotes
around it (are you sure, BTW, this is named Value and not ValueY?)

.... " and [Value] = 'ins" & cboNameY & "'"
Click to expand...
 
If you are going to be pulling from tblNameY, you will need it in the
query as well:

Update tblNameX INNER JOIN tblNameY on (I'm not sure how they link..)
SET ....

What I would recommend is to create the query in the QBE window, then
copy the SQL code over to the VBA code.


Chris Nebinger
 
Hi Chris,
Thanks for your reply.
Access denies an INNER JOIN on my UPDATE sql statement... Is it at all
posible??
Jochem
 
Okok, I already answered my own question, by running two different SQL queries.

The first one INSERTs all data from the first table to the second. The
second query deletes all data but NOT the data from my selected ComboBoxes.
Nice work around he??

But now only just one minor thing doesn't work:

CurrentDb.Execute ("DELETE FROM tblOne WHERE tblTwo.[Id] <> " & cboId & "
AND tblname.[name] <> " & cboName & " "), dbFailOnError

This AND statement will not work?! I also tried && ... What should I use? Is
it at all possible?
 
Hi,



Be careful with a delete and a where clause based on <>. Make a backup
first.

Your problem seems to be that you supply a string, not a field name, so you
must use delimiter:


CurrentDb.Execute ("DELETE FROM tblOne WHERE tblTwo.[Id] <> " & cboId & "
AND tblname.[name] <> '" & Replace(cboName, "'", "''") & "' "),
dbFailOnError



where I added ' as delimiter, and duplicate any occurrence of it in cboName
content, if any, to be in accordance with what the database expect. If ID
is not numerical, also use delimiter:

CurrentDb.Execute ("DELETE FROM tblOne WHERE tblTwo.[Id] <> '" & cboId & "'
AND tblname.[name] <> '" & Replace(cboName, "'", "''") & "' "),
dbFailOnError



where, this time, I just add the ' delimiters.


You can use DoCmd.RunSQL to avoid the delimiter problem:



DoCmd.RunSQL "DELETE FROM tblOne WHERE tblTwo.[Id] <>
FROMS!FormNameHere!cboId
AND tblname.[name] <> FORMS!FormName!cboName"



You don't need delimiter because you refer to the container of the data, not
to the data, as constant. You don't put delimiter around the field name, as
example, only around constant. With your CurrentDb.Execute, you build a
string that will see the data as constant (the string send to
CurrentDb.Execute will be just that, a string, with the data from cbold and
cboName embedded into it as a constant), so, these constants need the
required delimiters ( quotes or # for dates). The DoCmd syntax uses the
container, so no delimiters required. CurrentDb does not understand the
FORMS!formName!ControlName syntax, but DoCmd does.


Hoping it may help
Vanderghast, Access MVP



Jochem Davids said:
Okok, I already answered my own question, by running two different SQL
queries.

The first one INSERTs all data from the first table to the second. The
second query deletes all data but NOT the data from my selected
ComboBoxes.
Nice work around he??

But now only just one minor thing doesn't work:

CurrentDb.Execute ("DELETE FROM tblOne WHERE tblTwo.[Id] <> " & cboId & "
AND tblname.[name] <> " & cboName & " "), dbFailOnError

This AND statement will not work?! I also tried && ... What should I use?
Is
it at all possible?

Jochem Davids said:
Hi Chris,
Thanks for your reply.
Access denies an INNER JOIN on my UPDATE sql statement... Is it at all
posible??
Jochem
Click to expand...
Click to expand...
 
Back
Top