Running multiple SQL commands

shaneucc

Registered User.
Local time
Today, 23:11
Joined
Jul 25, 2013
Messages
28
I want to run multiple SQL commands on click of a button. I have these three command,
CurrentDb.Execute "INSERT INTO UserMadeDeviceT(Product, ORESector) " & _
" VALUES ('" & Me.D_NewDeviceTxt & "', '" & Me.D_ORECmb & "')"
CurrentDb.Execute "INSERT INTO UserMadeDeviceT (RatedKilowattPower, KilogramWeight)" & _
" SELECT RatedKilowattPower, Weight FROM UserSelectedComponentT " & _
" WHERE [TotalComponent] = '" & D_ComponentNameCmb & "'"
CurrentDb.Execute "INSERT INTO UserMadeDeviceT (Cost) SELECT SUM (EuroCost) FROM UserSelectedComponentT"

all of these work indevidually and return the correct value but the add three rows to the table. How can I combine these three commands into one so that it will only add one entry?
 
Do you mean that a new record is created for each instance of the command, when you instead want the value to be modified in a single (new) record?
 
Yes. When I click the button three new rows of data are added to the table when I want the information from all three of these commands added into one new row.
 
Then you probably need to use the SQL INSERT INTO command only for the first instance, and then an SQL UPDATE command:
Code:
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
(source: http://www.w3schools.com/sql/sql_update.asp)

You will have to somehow reference the record you are updating though, in your WHERE clause.
 
MikeLeBen's approach to creating and then updating the record valid, although what you want should be able to be accomplished using Domain Aggregate Functions.

I should state up front that I am not the greatest fan of Domain Aggregate Functions, since they can slow down some processing, but your case presents a good example of how to take advantage of them.

Your five parts appear to be as follows:

  1. Product - From the value contained in The Control D_NewDeviceTxt on the Current Form.
  2. ORESector - From the value contained in the Control D_ORECmb on the Current Form.
  3. RatedKilowattPower - From the value contained in the Field RatedKilowattPower in the Table UserSelectedComponentT where the Field TotalComponent matches the value in the Control D_ComponentNameCmb on the Current Form.
  4. KilogramWeight - From the value contained in the Field Weight in the Table UserSelectedComponentT where the Field TotalComponent matches the value in the Control D_ComponentNameCmb on the Current Form.
  5. Cost - From the Sum of the values value contained in the Field EuroCost in the Table UserSelectedComponentT
Product and ORESector are straightforward and can be done the way they are coded. RatedKilowattPower and KilogramWeight can be calculated using the Domain Aggregate Function DLookup(). Cost can be calculated using the Domain Aggregate Function DSum().

Once you have all five of the pieces, creating an insert Function should be easy for you.

Take a look at the Domain Aggregate Functions and see what you can do with them. Get back to us after you have tried.

DLookup: http://msdn.microsoft.com/en-us/library/office/aa172176(v=office.11).aspx

DSum: http://msdn.microsoft.com/en-us/library/office/aa172193(v=office.11).aspx

Also take a look at the calculation of Cost. Cost appears to be based on the entire Table UserSelectedComponentT as opposed to records in the Table UserSelectedComponentT where the Field TotalComponent matches the value in the Control D_ComponentNameCmb on the Current Form. I believe that this will make Cost the same every time. If iit should not be, then the formula needs tobe revised.

-- Rookie
 
Thank you. I tried to use DSum and DLookup. This is what I did,

Dim dblPower As Double
varPower = DLookup("RatedPower", "DeviceRatedPowerQ")
Dim dblWeight As Double
varWeight = DLookup("ComponentWeight", "DeviceWeightQ")
Dim dblCost As Double
dblCost = DSum("[EuroCost]", "UserSelectedComponentT")

CurrentDb.Execute "INSERT INTO UserMadeDeviceT(Product, ORESector, RatedKilowattPower, KilogramWeight, Cost) " & _
" VALUES ('" & Me.D_NewDeviceTxt & "', '" & Me.D_ORECmb & "','dblPower','dblWeight','dblCost')"

This still results in an error. What am I doing wrong?
 
Actually I see my mistake. It's just a syntax problem. Thank you
 
Actually I see my mistake. It's just a syntax problem. Thank you

What was the final syntax for your SQL Code? I noticed some things (See Below) you should look at:

  1. Your definition and usage of your variables needs to be checked. Adding a line containing OPTION Explicit at the top of your Code Module will help with this
  2. Your Where Statements are not represented in your Dlookups
  3. Since dblPower, dblWeight, and dblCost are Doubles, They do not require Quotes around them. You might need to convert them using the Clng() Function in order to add them to the SQL String.
-- Rookie


------------------------------------------------------------------


All of your modules (both in forms or standard modules) should look something like the following at the top:

Option Compare Database
Option Explicit



------------------------------------------------------------------


Dim dblPower As Double
varPower = DLookup("RatedPower", "DeviceRatedPowerQ")
Dim dblWeight As Double
varWeight = DLookup("ComponentWeight", "DeviceWeightQ")
Dim dblCost As Double
dblCost = DSum("[EuroCost]", "UserSelectedComponentT")

CurrentDb.Execute "INSERT INTO UserMadeDeviceT(Product, ORESector, RatedKilowattPower, KilogramWeight, Cost) " & _
" VALUES ('" & Me.D_NewDeviceTxt & "', '" & Me.D_ORECmb & "','dblPower','dblWeight','dblCost')"
 
This is the code I used that seemed to work.

Dim dblPower As Double
dblPower = DLookup("RatedPower", "DeviceRatedPowerQ")
Dim dblWeight As Double
dblWeight = DSum("[Weight]", "UserSelectedComponentT")
Dim dblCost As Currency
dblCost = DSum("[EuroCost]", "UserSelectedComponentT")

CurrentDb.Execute "INSERT INTO ProjectDevicesT(DeviceName, Quantity, ORESector, Power, Cost, Weight, TotalDevice) " & _
" VALUES ('" & D_NewDeviceTxt & "', '" & D_QuantityBtn & "', '" & D_ORECmb & "', '" & dblPower & "', '" & dblCost & "', '" & dblWeight & "', 'User made - ' + '" & D_NewDeviceTxt & "' + ' - ' + '" & dblPower & "' + 'kW - €' + '" & dblCost & "')"
 

Users who are viewing this thread

Back
Top Bottom