Multiple command button tasks

G

Guest

Dear Sirs,
I have a command button which transfers data from a table to a table in
another database.
This is the code:
*************************************************
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = " C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb "
strTargetTable = " webtotalbalancebie30p "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity) IN '" & _
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
******************************************************

I would like to transfer as well data to another table in the same taget
database.
which should look more or less like this:
strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

How can I combine both button commands so that the transactions are done
with one button click?
Thanks for your help
 
T

tina

after executing the first SQL statement contained in the strSQL variable,
simply change the values of the string variables as needed, then execute the
strSQL variable again.

hth
 
G

Guest

Dear Tina
thanks, but I have no idea what you mean.
I would like to excecute two commands simultaniously with one button.
Is it possible for you to give me an example with the codes I provided.
Thanks
Klaus

tina said:
after executing the first SQL statement contained in the strSQL variable,
simply change the values of the string variables as needed, then execute the
strSQL variable again.

hth


Amateur said:
Dear Sirs,
I have a command button which transfers data from a table to a table in
another database.
This is the code:
*************************************************
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = " C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb "
strTargetTable = " webtotalbalancebie30p "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity) IN '" & _
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
******************************************************

I would like to transfer as well data to another table in the same taget
database.
which should look more or less like this:
strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

How can I combine both button commands so that the transactions are done
with one button click?
Thanks for your help
Click to expand...
Click to expand...
 
R

Rick Brandt

Amateur said:
Dear Tina
thanks, but I have no idea what you mean.
I would like to excecute two commands simultaniously with one button.
Is it possible for you to give me an example with the codes I
provided. Thanks
Klaus
Click to expand...

Consider these lines form your first code snippet...

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

All you have to do is add your second code snippet before the Exit_Point label.
A code routine can execute as many lines as you need it to. Just include those
lines and they will be run.

Alternatively you can have one code routine call another one intead of having
all the codee in one block. Continuing the example above that wuld look like...

CurrentDb.Execute strSQL, dbFailOnError

Call SomeOtherSub

Exit_Point:
Exit Sub
 
T

tina

Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = " C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb "
strTargetTable = " webtotalbalancebie30p "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity) IN '" &
_
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

strTargetTable = " SomeOtherTableName "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
 
G

Guest

Hi Rick
thank you and I thought I did like you said - but somehow I did something
wrong. I always get the error message: Compile error, duplicate declaration
in current scope.
Do you have any idea what I could have done wrong?
Here is now my complete code:
*********************************************
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb"
strTargetTable = "webtotalbalancebie30p"

With CurrentDb

strSQL = _
"DELETE FROM [" & strTargetTable & "] " & _
"IN '" & _
strTargetDB & "';"

.Execute strSQL, dbFailOnError

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity,
monthstartingbalance, SumOfcontractvalue, commission, managementfeebasic,
Incentivefeetotal, electronicfee, vat, adjustment, netliquidationbalance,
SumOfopenpl, SumOfinitialmargin, SumOfmaintenancemargin, opentradebalance) IN
'" & _
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity,
webtotalbalancebie30p.monthstartingbalance,
webtotalbalancebie30p.SumOfcontractvalue, webtotalbalancebie30p.commission,
webtotalbalancebie30p.managementfeebasic,
webtotalbalancebie30p.incentivefeetotal, webtotalbalancebie30p.electronicfee,
webtotalbalancebie30p.vat, webtotalbalancebie30p.adjustment,
webtotalbalancebie30p.netliquidationbalance,
webtotalbalancebie30p.SumOfopenpl, webtotalbalancebie30p.SumOfinitialmargin,
webtotalbalancebie30p.SumOfmaintenancemargin,
webtotalbalancebie30p.opentradebalance " & _
"FROM webtotalbalancebie30p;"


CurrentDb.Execute strSQL, dbFailOnError

End With
On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb"
strTargetTable = "weboffsetordersbie30p"

With CurrentDb

strSQL = _
"DELETE FROM [" & strTargetTable & "] " & _
"IN '" & _
strTargetDB & "';"

.Execute strSQL, dbFailOnError

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber,
weboffsetordersbie30p.date,weboffsetordersbie30p.bought,
weboffsetordersbie30p.sold, weboffsetordersbie30p.commodity,
weboffsetordersbie30p.month, weboffsetordersbie30p.year,
weboffsetordersbie30p.price, weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

End With
Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
****************************************************
 
G

Guest

Dear Tina
I am thankful for any help because I am not knowing alot from this all. If I
try your code I get the error : Compile error Syntax error.
And did you not forget my "Delete" command?
Thank you for your help
Klaus

tina said:
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = " C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb "
strTargetTable = " webtotalbalancebie30p "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity) IN '" &
_
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

strTargetTable = " SomeOtherTableName "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub


Amateur said:
Dear Tina
thanks, but I have no idea what you mean.
I would like to excecute two commands simultaniously with one button.
Is it possible for you to give me an example with the codes I provided.
Thanks
Klaus
Click to expand...
Click to expand...
 
G

Guest

Hello Tina
sorry it's me again. I tried the following and I couldn't come out of error
messages. It's a loop of errors - I cannot explain because I have never seen
that before. It's just always an error windiw with numbers. Like : error 20,
error 0 etc.
This was my code:
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb"
strTargetTable = "webtotalbalancebie30p"

With CurrentDb

strSQL = _
"DELETE FROM [" & strTargetTable & "] " & _
"IN '" & _
strTargetDB & "';"

.Execute strSQL, dbFailOnError

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity,
monthstartingbalance, SumOfcontractvalue, commission, managementfeebasic,
Incentivefeetotal, electronicfee, vat, adjustment, netliquidationbalance,
SumOfopenpl, SumOfinitialmargin, SumOfmaintenancemargin, opentradebalance) IN
'" & _
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity,
webtotalbalancebie30p.monthstartingbalance,
webtotalbalancebie30p.SumOfcontractvalue, webtotalbalancebie30p.commission,
webtotalbalancebie30p.managementfeebasic,
webtotalbalancebie30p.incentivefeetotal, webtotalbalancebie30p.electronicfee,
webtotalbalancebie30p.vat, webtotalbalancebie30p.adjustment,
webtotalbalancebie30p.netliquidationbalance,
webtotalbalancebie30p.SumOfopenpl, webtotalbalancebie30p.SumOfinitialmargin,
webtotalbalancebie30p.SumOfmaintenancemargin,
webtotalbalancebie30p.opentradebalance " & _
"FROM webtotalbalancebie30p;"


CurrentDb.Execute strSQL, dbFailOnError

End With

strTargetTable = " weboffsetordersbie30p "
With CurrentDb

strSQL = _
"DELETE FROM [" & strTargetTable & "] " & _
"IN '" & _
strTargetDB & "';"

.Execute strSQL, dbFailOnError


strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

End With
Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub


tina said:
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = " C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb "
strTargetTable = " webtotalbalancebie30p "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity) IN '" &
_
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

strTargetTable = " SomeOtherTableName "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub


Amateur said:
Dear Tina
thanks, but I have no idea what you mean.
I would like to excecute two commands simultaniously with one button.
Is it possible for you to give me an example with the codes I provided.
Thanks
Klaus
Click to expand...
Click to expand...
 
R

Rick Brandt

Amateur said:
Hi Rick
thank you and I thought I did like you said - but somehow I did
something wrong. I always get the error message: Compile error,
duplicate declaration in current scope.
Do you have any idea what I could have done wrong?
Here is now my complete code:
*********************************************
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String
Click to expand...

You make these dim statements twice. You only need them once.
 
T

tina

well, hon, if you can see that i forgot a piece of code that you need, then
just stick it in there where you need it. the point of my post was to show
you what Rick and i have tried to explain: you can run as many lines of
code as you want, or need, in a single procedure in VBA, as

set value of strTargetDB
set value of strTargetTable
set value of strSQL

execute strSQL

set value of strTargetTable
set value of strSQL

execute strSQL

if there's an error in the syntax, i'm guessing that the problem is in how
the multi-line SQL string is put together. i simply copied the code from
your original post, where it was incorrectly line-wrapped by the newsreader.
you'll need to clean it up, rather than just doing a straight copy/paste
from the post into your VBA module.

hth


Amateur said:
Dear Tina
I am thankful for any help because I am not knowing alot from this all. If I
try your code I get the error : Compile error Syntax error.
And did you not forget my "Delete" command?
Thank you for your help
Klaus

tina said:
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = " C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb "
strTargetTable = " webtotalbalancebie30p "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity) IN '" &
_
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

strTargetTable = " SomeOtherTableName "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub


Amateur said:
Dear Tina
thanks, but I have no idea what you mean.
I would like to excecute two commands simultaniously with one button.
Is it possible for you to give me an example with the codes I provided.
Thanks
Klaus

:

after executing the first SQL statement contained in the strSQL variable,
simply change the values of the string variables as needed, then
Click to expand...
execute
the
strSQL variable again.

hth
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Tina
I really don't want to go on your nervs but I am totally lost now. To make
it easier here is the origin code which is working:
*******************************************
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb"
strTargetTable = "webtotalbalancebie30p"

With CurrentDb

strSQL = _
"DELETE FROM [" & strTargetTable & "] " & _
"IN '" & _
strTargetDB & "';"

.Execute strSQL, dbFailOnError

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity,
monthstartingbalance, SumOfcontractvalue, commission, managementfeebasic,
Incentivefeetotal, electronicfee, vat, adjustment, netliquidationbalance,
SumOfopenpl, SumOfinitialmargin, SumOfmaintenancemargin, opentradebalance) IN
'" & _
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity,
webtotalbalancebie30p.monthstartingbalance,
webtotalbalancebie30p.SumOfcontractvalue, webtotalbalancebie30p.commission,
webtotalbalancebie30p.managementfeebasic,
webtotalbalancebie30p.incentivefeetotal, webtotalbalancebie30p.electronicfee,
webtotalbalancebie30p.vat, webtotalbalancebie30p.adjustment,
webtotalbalancebie30p.netliquidationbalance,
webtotalbalancebie30p.SumOfopenpl, webtotalbalancebie30p.SumOfinitialmargin,
webtotalbalancebie30p.SumOfmaintenancemargin,
webtotalbalancebie30p.opentradebalance " & _
"FROM webtotalbalancebie30p;"


CurrentDb.Execute strSQL, dbFailOnError

End With


Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
*****************************************************
This code for a single comand I would like to change that it takes two
commands:
Here is the code for the second command which is working alone as well:
******************************************************
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb"
strTargetTable = "weboffsetordersbie30p"

With CurrentDb

strSQL = _
"DELETE FROM [" & strTargetTable & "] " & _
"IN '" & _
strTargetDB & "';"

.Execute strSQL, dbFailOnError

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber " & _
"FROM weboffsetordersbie30p;"


CurrentDb.Execute strSQL, dbFailOnError

End With


Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
*****************************************************
Is it possible for you, or, if it is not too much ask from me, to put once
both codes together so that the command button is running both with one click?
Thank you and Rick for your help.
Klaus

tina said:
well, hon, if you can see that i forgot a piece of code that you need, then
just stick it in there where you need it. the point of my post was to show
you what Rick and i have tried to explain: you can run as many lines of
code as you want, or need, in a single procedure in VBA, as

set value of strTargetDB
set value of strTargetTable
set value of strSQL

execute strSQL

set value of strTargetTable
set value of strSQL

execute strSQL

if there's an error in the syntax, i'm guessing that the problem is in how
the multi-line SQL string is put together. i simply copied the code from
your original post, where it was incorrectly line-wrapped by the newsreader.
you'll need to clean it up, rather than just doing a straight copy/paste
from the post into your VBA module.

hth


Amateur said:
Dear Tina
I am thankful for any help because I am not knowing alot from this all. If I
try your code I get the error : Compile error Syntax error.
And did you not forget my "Delete" command?
Thank you for your help
Klaus

tina said:
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = " C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb "
strTargetTable = " webtotalbalancebie30p "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity) IN '" &
_
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

strTargetTable = " SomeOtherTableName "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub


Dear Tina
thanks, but I have no idea what you mean.
I would like to excecute two commands simultaniously with one button.
Is it possible for you to give me an example with the codes I provided.
Thanks
Klaus

:

after executing the first SQL statement contained in the strSQL
variable,
simply change the values of the string variables as needed, then execute
the
strSQL variable again.

hth
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hello Tina
This time really the last question.
Finally, with your help I figured out how it is working - thank you very much.
I still got the message : Syntax error in INSERT INTO statement. As well
there I found out that it doesn't work if I transfer the "date" field
(everything else is working). I looked at both tables and all the
fielddetails are the same in the source and in the target table for the field
"date"
Do you have any idea what I can do or change to get rid of this error message?

tina said:
well, hon, if you can see that i forgot a piece of code that you need, then
just stick it in there where you need it. the point of my post was to show
you what Rick and i have tried to explain: you can run as many lines of
code as you want, or need, in a single procedure in VBA, as

set value of strTargetDB
set value of strTargetTable
set value of strSQL

execute strSQL

set value of strTargetTable
set value of strSQL

execute strSQL

if there's an error in the syntax, i'm guessing that the problem is in how
the multi-line SQL string is put together. i simply copied the code from
your original post, where it was incorrectly line-wrapped by the newsreader.
you'll need to clean it up, rather than just doing a straight copy/paste
from the post into your VBA module.

hth


Amateur said:
Dear Tina
I am thankful for any help because I am not knowing alot from this all. If I
try your code I get the error : Compile error Syntax error.
And did you not forget my "Delete" command?
Thank you for your help
Klaus

tina said:
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = " C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb "
strTargetTable = " webtotalbalancebie30p "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity) IN '" &
_
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

strTargetTable = " SomeOtherTableName "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub


Dear Tina
thanks, but I have no idea what you mean.
I would like to excecute two commands simultaniously with one button.
Is it possible for you to give me an example with the codes I provided.
Thanks
Klaus

:

after executing the first SQL statement contained in the strSQL
variable,
simply change the values of the string variables as needed, then execute
the
strSQL variable again.

hth
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

If it's complaining about

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

the problem is likely that date, month and year are all reserved words, and
should not be used for your own purposes.

If you cannot (or will not) rename the table fields, at least put square
brackets around them:

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, [date], bought, sold, commodity,
[month], [year], price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.[date],
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.[month],
weboffsetordersbie30p.[year], weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Amateur said:
Hello Tina
This time really the last question.
Finally, with your help I figured out how it is working - thank you very
much.
I still got the message : Syntax error in INSERT INTO statement. As well
there I found out that it doesn't work if I transfer the "date" field
(everything else is working). I looked at both tables and all the
fielddetails are the same in the source and in the target table for the
field
"date"
Do you have any idea what I can do or change to get rid of this error
message?

tina said:
well, hon, if you can see that i forgot a piece of code that you need,
then
just stick it in there where you need it. the point of my post was to
show
you what Rick and i have tried to explain: you can run as many lines of
code as you want, or need, in a single procedure in VBA, as

set value of strTargetDB
set value of strTargetTable
set value of strSQL

execute strSQL

set value of strTargetTable
set value of strSQL

execute strSQL

if there's an error in the syntax, i'm guessing that the problem is in
how
the multi-line SQL string is put together. i simply copied the code from
your original post, where it was incorrectly line-wrapped by the
newsreader.
you'll need to clean it up, rather than just doing a straight copy/paste
from the post into your VBA module.

hth


Amateur said:
Dear Tina
I am thankful for any help because I am not knowing alot from this all.
If I
try your code I get the error : Compile error Syntax error.
And did you not forget my "Delete" command?
Thank you for your help
Klaus

:

Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = " C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb "
strTargetTable = " webtotalbalancebie30p "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity)
IN '" &
_
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

strTargetTable = " SomeOtherTableName "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold,
commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub


Dear Tina
thanks, but I have no idea what you mean.
I would like to excecute two commands simultaniously with one
button.
Is it possible for you to give me an example with the codes I provided.
Thanks
Klaus

:

after executing the first SQL statement contained in the strSQL
variable,
simply change the values of the string variables as needed, then execute
the
strSQL variable again.

hth
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you Douglas, I already got nuts about this - I did not know that they
are reserved. I believe now I can change my whole program. Thanks for letting
me know and helping me.
All the best
Klaus

Douglas J. Steele said:
If it's complaining about

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

the problem is likely that date, month and year are all reserved words, and
should not be used for your own purposes.

If you cannot (or will not) rename the table fields, at least put square
brackets around them:

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, [date], bought, sold, commodity,
[month], [year], price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.[date],
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.[month],
weboffsetordersbie30p.[year], weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Amateur said:
Hello Tina
This time really the last question.
Finally, with your help I figured out how it is working - thank you very
much.
I still got the message : Syntax error in INSERT INTO statement. As well
there I found out that it doesn't work if I transfer the "date" field
(everything else is working). I looked at both tables and all the
fielddetails are the same in the source and in the target table for the
field
"date"
Do you have any idea what I can do or change to get rid of this error
message?

tina said:
well, hon, if you can see that i forgot a piece of code that you need,
then
just stick it in there where you need it. the point of my post was to
show
you what Rick and i have tried to explain: you can run as many lines of
code as you want, or need, in a single procedure in VBA, as

set value of strTargetDB
set value of strTargetTable
set value of strSQL

execute strSQL

set value of strTargetTable
set value of strSQL

execute strSQL

if there's an error in the syntax, i'm guessing that the problem is in
how
the multi-line SQL string is put together. i simply copied the code from
your original post, where it was incorrectly line-wrapped by the
newsreader.
you'll need to clean it up, rather than just doing a straight copy/paste
from the post into your VBA module.

hth


Dear Tina
I am thankful for any help because I am not knowing alot from this all.
If
I
try your code I get the error : Compile error Syntax error.
And did you not forget my "Delete" command?
Thank you for your help
Klaus

:

Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = " C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb "
strTargetTable = " webtotalbalancebie30p "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity)
IN
'" &
_
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

strTargetTable = " SomeOtherTableName "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold,
commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub


Dear Tina
thanks, but I have no idea what you mean.
I would like to excecute two commands simultaniously with one
button.
Is it possible for you to give me an example with the codes I
provided.
Thanks
Klaus

:

after executing the first SQL statement contained in the strSQL
variable,
simply change the values of the string variables as needed, then
execute
the
strSQL variable again.

hth
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You may want to check out what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html for a (hopefully!) complete
list of reserved words.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Amateur said:
Thank you Douglas, I already got nuts about this - I did not know that
they
are reserved. I believe now I can change my whole program. Thanks for
letting
me know and helping me.
All the best
Klaus

Douglas J. Steele said:
If it's complaining about

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

the problem is likely that date, month and year are all reserved words,
and
should not be used for your own purposes.

If you cannot (or will not) rename the table fields, at least put square
brackets around them:

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, [date], bought, sold,
commodity,
[month], [year], price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.[date],
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.[month],
weboffsetordersbie30p.[year], weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Amateur said:
Hello Tina
This time really the last question.
Finally, with your help I figured out how it is working - thank you
very
much.
I still got the message : Syntax error in INSERT INTO statement. As
well
there I found out that it doesn't work if I transfer the "date" field
(everything else is working). I looked at both tables and all the
fielddetails are the same in the source and in the target table for the
field
"date"
Do you have any idea what I can do or change to get rid of this error
message?

:

well, hon, if you can see that i forgot a piece of code that you need,
then
just stick it in there where you need it. the point of my post was to
show
you what Rick and i have tried to explain: you can run as many lines
of
code as you want, or need, in a single procedure in VBA, as

set value of strTargetDB
set value of strTargetTable
set value of strSQL

execute strSQL

set value of strTargetTable
set value of strSQL

execute strSQL

if there's an error in the syntax, i'm guessing that the problem is in
how
the multi-line SQL string is put together. i simply copied the code
from
your original post, where it was incorrectly line-wrapped by the
newsreader.
you'll need to clean it up, rather than just doing a straight
copy/paste
from the post into your VBA module.

hth


Dear Tina
I am thankful for any help because I am not knowing alot from this
all.
If
I
try your code I get the error : Compile error Syntax error.
And did you not forget my "Delete" command?
Thank you for your help
Klaus

:

Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = " C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb "
strTargetTable = " webtotalbalancebie30p "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment,
guaranteedequity)
IN
'" &
_
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

strTargetTable = " SomeOtherTableName "

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold,
commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub


Dear Tina
thanks, but I have no idea what you mean.
I would like to excecute two commands simultaniously with one
button.
Is it possible for you to give me an example with the codes I
provided.
Thanks
Klaus

:

after executing the first SQL statement contained in the
strSQL
variable,
simply change the values of the string variables as needed,
then
execute
the
strSQL variable again.

hth
Click to expand...
Click to expand...
Click to expand...
 
Top