Breaking down large VBA - SQL query code
Hi,
I have the above code which run over the MS VB window and into the 2nd line. How do I break it down into 2 lines? Is there is a possible way?
Thanks!
CurrentDb.Execute "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY], [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M) SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE], P2CSameTx65Table.CITY AS [DESTIN CITY], P2CSameTx65Table.PROVINCE AS [DESTIN PROVINCE], P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![Min]+[getDestinB eyond]![MI N] AS [MIN], [P2CSameTx65Table]![LTL]+[ getDestinB eyond]![LT L] AS LTL, [P2CSameTx65Table]![500]+[ getDestinB eyond]![50 0] AS 500, [P2CSameTx65Table]![1M]+[g etDestinBe yond]![1M] AS 1M, [P2CSameTx65Table]![2M]+[g etDestinBe yond]![2M] AS 2M, [P2CSameTx65Table]![5M]+[g etDestinBe yond]![5M] AS 5M, [P2CSameTx65Table]![10M]+[ getDestinB eyond]![10 M] AS 10M, [P2CSameTx65Table]![20M]+[ getDestinB eyond]![20 M] AS 20M"
FROM [ZONE] INNER JOIN INTER ON ZONE.[TCOMBI] = INTER.DTCOMBO WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo ] & Chr(34) & " And ZONE.[ZONE] = " & Chr(34) & "LOCAL" & Chr(34) & " And INTER.[TCOMBO] Like " & Chr(34) & userinput & "*" & Chr(34) & " ORDER BY ZONE.CITY, INTER.Class", dbFailOnError
I have the above code which run over the MS VB window and into the 2nd line. How do I break it down into 2 lines? Is there is a possible way?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for both answers. I know how to run using currentdb.execute but thought there maybe a shortcut!
As for creating query, I have variables among the query.
As for creating query, I have variables among the query.
you might also want to consider that all of your computed columns look like:
[P2CSameTx65Table]![500]+[
and that syntax will return a NULL value if either of these is NULL. To circumvent that, you could use:
NZ([P2CSameTx65Table]![500
[P2CSameTx65Table]![500]+[
and that syntax will return a NULL value if either of these is NULL. To circumvent that, you could use:
NZ([P2CSameTx65Table]
<As for creating query, I have variables among the query. >
And?
It's not like variables are a barrier to creating a saved query.
And?
It's not like variables are a barrier to creating a saved query.
ASKER
Well, I really didn't go into this deep.
When I created queries from query-design, I researched my options and found out that you can get a value as a criteria via 2 ways.
1. Directly calling the field:
eg: [forms][formname][field] or [table][field] - something like this
2. Calling the field via a function
function1(getfieldvalue)
Now I have the senario where I have to repeat the query for each value in another table.
So I am looping through a table (say table1) values. I don't see how I can call table1.value (at a particular moment) to query design.
I thought of creating a function which loops through table value but then that function would loop through the table for each call:
eg: function1(getvalue)
The above would run through the table and return all values.
I am sure you can assign primary keys and get related value ONLY but I really didnt want to go that far :/
When I created queries from query-design, I researched my options and found out that you can get a value as a criteria via 2 ways.
1. Directly calling the field:
eg: [forms][formname][field] or [table][field] - something like this
2. Calling the field via a function
function1(getfieldvalue)
Now I have the senario where I have to repeat the query for each value in another table.
So I am looping through a table (say table1) values. I don't see how I can call table1.value (at a particular moment) to query design.
I thought of creating a function which loops through table value but then that function would loop through the table for each call:
eg: function1(getvalue)
The above would run through the table and return all values.
I am sure you can assign primary keys and get related value ONLY but I really didnt want to go that far :/
There is a third, and very important way to pass in your parameters.
You saw this earlier
dim db as database
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("qryWhatever"
qdf.Execute dbFailOnError
If you have parameters in the query, it becomes
dim db as database
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("qryWhatever"
'for as many parameters as you have
qdf(0) = FirstParameterValue
qdf(1) = #SomeSecondParameterValueT
qdf(2) = "SomeThirdParameterThatIsA
'and so on an so on
qdf.Execute dbFailOnError
If you are doing this over and over, you can put it inside a loop that changes the parameters and executes.
If the parameters are functions you can also use Eval() to make the parameters evaluate themselves as written in the query.
You saw this earlier
dim db as database
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("qryWhatever"
qdf.Execute dbFailOnError
If you have parameters in the query, it becomes
dim db as database
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("qryWhatever"
'for as many parameters as you have
qdf(0) = FirstParameterValue
qdf(1) = #SomeSecondParameterValueT
qdf(2) = "SomeThirdParameterThatIsA
'and so on an so on
qdf.Execute dbFailOnError
If you are doing this over and over, you can put it inside a loop that changes the parameters and executes.
If the parameters are functions you can also use Eval() to make the parameters evaluate themselves as written in the query.
ASKER
OW!
How would this parameter be called in criteria?
qdf(number)?
Considering number = currentvalue from a table of 'numbers'
Is this called a term? how would I go on to google further studies on this?
Thanks!
How would this parameter be called in criteria?
qdf(number)?
Considering number = currentvalue from a table of 'numbers'
Is this called a term? how would I go on to google further studies on this?
Thanks!
The mechanism is straightforward.
Let's say we have the following Select query
"Select * from SomeTable where SomeID = [forms]![SomeForm]![SomeCo
We save the query and give it a name 'qrySomeThing'
In code, when we want to use that parameter query we do the following
dim db as database
dim rs as recordset
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("qrySomething
'for as many parameters as you have
qdf(0) = 1234 'the value we are interested in
set rs = qdf.OpenRecordset(dbOpenDy
For the qdf(0) you could also feed in a VBA variable
dim x as integer
x = 12345
qdf(0) = x
You can feed it a function
You can feed it a control value
qdf(0) = Me.SomeControl.value
Look in the Access 2003 help for QueryDef
The later versions of Access help aren't so helpful
Google up 'MS Access VBA QueryDef'
If the form that your parameters comes from you can add the following to the code snippets I have posted
dim prm as Parameter
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Start poking around here
http://access.mvps.org/access/queries/qry0013.htm
Let's say we have the following Select query
"Select * from SomeTable where SomeID = [forms]![SomeForm]![SomeCo
We save the query and give it a name 'qrySomeThing'
In code, when we want to use that parameter query we do the following
dim db as database
dim rs as recordset
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("qrySomething
'for as many parameters as you have
qdf(0) = 1234 'the value we are interested in
set rs = qdf.OpenRecordset(dbOpenDy
For the qdf(0) you could also feed in a VBA variable
dim x as integer
x = 12345
qdf(0) = x
You can feed it a function
You can feed it a control value
qdf(0) = Me.SomeControl.value
Look in the Access 2003 help for QueryDef
The later versions of Access help aren't so helpful
Google up 'MS Access VBA QueryDef'
If the form that your parameters comes from you can add the following to the code snippets I have posted
dim prm as Parameter
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Start poking around here
http://access.mvps.org/access/queries/qry0013.htm
ASKER
Much appreciated! Thanks Nick!
Create a new query
Switch it to SQL view
Paste in just the SQL
Save and name it qryWhatever
Then in code, execute it
dim db as database
dim qdf as querydef
set db = currentdb
set qdf = db.querydefs("qryWhatever"
qdf.Execute dbFailOnError