Using SQL in VBA instead of query

[dhstein]

I needed a function to provide a value, so I created a query that would
return a single row and opened the query as a recordset to get the value.
What syntax would I use to put the SQL directly in the code and avoid the
step of creating the query? Thanks for any help you can provide.


SQL here:

SELECT tblProductSKU.ProductShortSKU,
Sum([tblWarehouseLocation].[WarehouseLocationMultiplier]*[tblWarehouseLocation].[WarehouseLocationQty]) AS TotalWeight
FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProductSKU.ProductShortSKU
HAVING (((tblProductSKU.ProductShortSKU)=GetVariable("ShortSKU")));


Function here:

Private Function GetTotalWeight(SKU As String)

Dim rsWeight As DAO.Recordset

ShortSKU = SKU

Set rsWeight = CurrentDb.OpenRecordset("qryTotalWeight", dbOpenDynaset)
rsWeight.MoveFirst

GetTotalWeight = rsWeight!TotalWeight
rsWeight.Close
End Function

 

[Rick Brandt]

I needed a function to provide a value, so I created a query that would
return a single row and opened the query as a recordset to get the
value. What syntax would I use to put the SQL directly in the code and
avoid the step of creating the query? Thanks for any help you can
provide. [snip]
Set rsWeight = CurrentDb.OpenRecordset("qryTotalWeight", dbOpenDynaset)

Put your SQL into a string variable, then use what you have above except
replace the name of the query you have there now with the variable.

 

[Allen Browne]

Code like this:
Dim strSql As String
strSql = "SELECT ...
Set rsWeight = CurrentDb.OpenRecordset(strSql)
...

You can use the underscore as a line continuation character if you close the
string, e.g.:
strSql = "SELECT tblProductSKU.ProductShortSKU, " & _
"Sum(...

If you have mocked up a query, and want to copy the SQL statement into code,
this might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html
It's a form where you paste the SQL statement in, and it massages it to
create the VBA string (including line endings and underscores) and puts it
in your clipboard ready to paste into your code.

 

[Andrew]

Code like this:
    Dim strSql As String
    strSql = "SELECT ...
    Set rsWeight = CurrentDb.OpenRecordset(strSql)
    ...

You can use the underscore as a line continuation character if you close the
string, e.g.:
    strSql = "SELECT tblProductSKU.ProductShortSKU, " & _
        "Sum(...

If you have mocked up a query, and want to copy the SQL statement into code,
this might help:
    Copy SQL statement from query to VBA
at:
   http://allenbrowne.com/ser-71.html
It's a form where you paste the SQL statement in, and it massages it to
create the VBA string (including line endings and underscores) and puts it
in your clipboard ready to paste into your code.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I needed a function to provide a value, so I created a query that would
return a single row and opened the query as a recordset to get the value.
What syntax would I use to put the SQL directly in the code and avoid the
step of creating the query?  Thanks for any help you can provide.

SQL here:

SELECT tblProductSKU.ProductShortSKU,
Sum([tblWarehouseLocation].[WarehouseLocationMultiplier]*[tblWarehouseLocat­ion].[WarehouseLocationQty])
AS TotalWeight
FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProductSKU.ProductShortSKU
HAVING (((tblProductSKU.ProductShortSKU)=GetVariable("ShortSKU")));

Function here:

Private Function GetTotalWeight(SKU As String)

Dim rsWeight As DAO.Recordset

ShortSKU = SKU

Set rsWeight = CurrentDb.OpenRecordset("qryTotalWeight", dbOpenDynaset)
rsWeight.MoveFirst

GetTotalWeight = rsWeight!TotalWeight
rsWeight.Close
End Function- Hide quoted text -

- Show quoted text -

Might also be worth mentioning that all things being equal, the saved
query may run more quickly than the same SQL statement in VBA, because
it will be optimized before running...

Regards
Andrew

 

[Allen Browne]

Might also be worth mentioning that all things being equal, the saved
query may run more quickly than the same SQL statement in VBA,
because it will be optimized before running.

Andrew, you have no doubt read this information (e.g. some Microsoft sources
say this), but you might want to give it a try and see how much difference
it does make. IME, there is no significant difference, so the advice is
misguided.

It's true that a saved query has a compilation plan saved with it, whereas
Access has to calculate a plan for a query string. For a straightforward
query, this calculation would be in fractions of a millisecond.

If you still think that's significant, bear in mind that if the data has
changed significantly since the query was saved (e.g. if lots of records
have been added since the query was saved), then the saved plan may be
completely wrong for the current data. The execution speed may therefore be
*much* longer than time it would have taken to calculate the plan, in which
case the unsaved query could execute considerably faster.

You're not wrong, of course: you could point to good sources that back you
up. But I question whether those statements are meaningful in the real
world. It's good to have varying opinons.

 

[dhstein]

Rick, Allen, and Andrew,

Thanks for your responses. There is something wrong with either my
system or the forum, because when I posted this request I got a message that
there was an error. Consequently, I posted it several other times. But now
I see the responses and I thank you all for your help.

David

 

[David W. Fenton]

It's true that a saved query has a compilation plan saved with it,
whereas Access has to calculate a plan for a query string. For a
straightforward query, this calculation would be in fractions of a
millisecond.

In a loop, it might make a significant difference, but I'm having a
hard time conceiving of a loop where you'd constantly be opening the
same query over and over again, or opening lots of different saved
queries repeatedly. For one, if your query is not parameterized, if
you're filtering it, you'll likely not get the benefit of any
optimization.

In most cases, alternatives like this are never going to make a
significant difference in performance unless you are using the
object in a loop. For saved QueryDefs, I can't think of how that
would ever make sense.

 

[dhstein]

Allen,

I set up your sql to VBA converter - that's great - thanks for that. My
original post was slightly in error, what I'm trying to do may not be
possible but here's the question. I have a query that will return a single
value. I'd like to have the function return that value by using the SQL
statement. So is it possible to do that? The function is below. I get a
run-time error 13 - type mismatch.

Private Function GetValue(SKU As String)

LongSKU = SKU

strSql = "SELECT tblWarehouseLocation.WarehouseLocationMultiplier AS
Multiplier " & vbCrLf & _
"FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU " &
vbCrLf & _
"GROUP BY tblProductSKU.ProductSKU,
tblWarehouseLocation.WarehouseLocationMultiplier " & vbCrLf & _
"HAVING (((tblProductSKU.ProductSKU)=GetVariable(""LongSKU"")));"


Set TheValue = CurrentDb.OpenRecordset(strSql)
MsgBox TheValue
GetValue = TheValue

End Function

Code like this:
Dim strSql As String
strSql = "SELECT ...
Set rsWeight = CurrentDb.OpenRecordset(strSql)
...

You can use the underscore as a line continuation character if you close the
string, e.g.:
strSql = "SELECT tblProductSKU.ProductShortSKU, " & _
"Sum(...

If you have mocked up a query, and want to copy the SQL statement into code,
this might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html
It's a form where you paste the SQL statement in, and it massages it to
create the VBA string (including line endings and underscores) and puts it
in your clipboard ready to paste into your code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I needed a function to provide a value, so I created a query that would
return a single row and opened the query as a recordset to get the value.
What syntax would I use to put the SQL directly in the code and avoid the
step of creating the query? Thanks for any help you can provide.


SQL here:

SELECT tblProductSKU.ProductShortSKU,
Sum([tblWarehouseLocation].[WarehouseLocationMultiplier]*[tblWarehouseLocation].[WarehouseLocationQty])
AS TotalWeight
FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProductSKU.ProductShortSKU
HAVING (((tblProductSKU.ProductShortSKU)=GetVariable("ShortSKU")));


Function here:

Private Function GetTotalWeight(SKU As String)

Dim rsWeight As DAO.Recordset

ShortSKU = SKU

Set rsWeight = CurrentDb.OpenRecordset("qryTotalWeight", dbOpenDynaset)
rsWeight.MoveFirst

GetTotalWeight = rsWeight!TotalWeight
rsWeight.Close
End Function

 

[Allen Browne]

You need more detail on how to code this?

Private Function GetValue(SKU As String) As Variant
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT ...
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount <> 0 Then
GetValue = rs.Fields(0)
Else
GetValue = Null
End If
rs.Close
Set rs = Nothing
End Function

You need to OpenRecordset, check that there is a record, and then return the
value of the first field. Otherwise I assume you want to return Null.

Can I strongly encourage you to use Option Explicit at the top of all your
modules? Really helps debugging.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I set up your sql to VBA converter - that's great - thanks for that. My
original post was slightly in error, what I'm trying to do may not be
possible but here's the question. I have a query that will return a
single
value. I'd like to have the function return that value by using the SQL
statement. So is it possible to do that? The function is below. I get a
run-time error 13 - type mismatch.

Private Function GetValue(SKU As String)

LongSKU = SKU

strSql = "SELECT tblWarehouseLocation.WarehouseLocationMultiplier AS
Multiplier " & vbCrLf & _
"FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU " &
vbCrLf & _
"GROUP BY tblProductSKU.ProductSKU,
tblWarehouseLocation.WarehouseLocationMultiplier " & vbCrLf & _
"HAVING (((tblProductSKU.ProductSKU)=GetVariable(""LongSKU"")));"


Set TheValue = CurrentDb.OpenRecordset(strSql)
MsgBox TheValue
GetValue = TheValue

End Function

Code like this:
Dim strSql As String
strSql = "SELECT ...
Set rsWeight = CurrentDb.OpenRecordset(strSql)
...

You can use the underscore as a line continuation character if you close
the
string, e.g.:
strSql = "SELECT tblProductSKU.ProductShortSKU, " & _
"Sum(...

If you have mocked up a query, and want to copy the SQL statement into
code,
this might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html
It's a form where you paste the SQL statement in, and it massages it to
create the VBA string (including line endings and underscores) and puts
it
in your clipboard ready to paste into your code.

I needed a function to provide a value, so I created a query that would
return a single row and opened the query as a recordset to get the
value.
What syntax would I use to put the SQL directly in the code and avoid
the
step of creating the query? Thanks for any help you can provide.


SQL here:

SELECT tblProductSKU.ProductShortSKU,
Sum([tblWarehouseLocation].[WarehouseLocationMultiplier]*[tblWarehouseLocation].[WarehouseLocationQty])
AS TotalWeight
FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProductSKU.ProductShortSKU
HAVING (((tblProductSKU.ProductShortSKU)=GetVariable("ShortSKU")));


Function here:

Private Function GetTotalWeight(SKU As String)

Dim rsWeight As DAO.Recordset

ShortSKU = SKU

Set rsWeight = CurrentDb.OpenRecordset("qryTotalWeight", dbOpenDynaset)
rsWeight.MoveFirst

GetTotalWeight = rsWeight!TotalWeight
rsWeight.Close
End Function

 

[dhstein]

Thanks Allen - that worked perfectly. I know I should use Option Explicit -
it's on my list of things to correct. I'm kind of new to Access and learning
as I go so thanks for all your help and advice. BTW is there much difference
between:

Set rs = dbEngine(0)(0).OpenRecordset(strSql)

and

Set rs = CurrentDb.OpenRecordset(strSql)

You need more detail on how to code this?

Private Function GetValue(SKU As String) As Variant
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT ...
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount <> 0 Then
GetValue = rs.Fields(0)
Else
GetValue = Null
End If
rs.Close
Set rs = Nothing
End Function

You need to OpenRecordset, check that there is a record, and then return the
value of the first field. Otherwise I assume you want to return Null.

Can I strongly encourage you to use Option Explicit at the top of all your
modules? Really helps debugging.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I set up your sql to VBA converter - that's great - thanks for that. My
original post was slightly in error, what I'm trying to do may not be
possible but here's the question. I have a query that will return a
single
value. I'd like to have the function return that value by using the SQL
statement. So is it possible to do that? The function is below. I get a
run-time error 13 - type mismatch.

Private Function GetValue(SKU As String)

LongSKU = SKU

strSql = "SELECT tblWarehouseLocation.WarehouseLocationMultiplier AS
Multiplier " & vbCrLf & _
"FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU " &
vbCrLf & _
"GROUP BY tblProductSKU.ProductSKU,
tblWarehouseLocation.WarehouseLocationMultiplier " & vbCrLf & _
"HAVING (((tblProductSKU.ProductSKU)=GetVariable(""LongSKU"")));"


Set TheValue = CurrentDb.OpenRecordset(strSql)
MsgBox TheValue
GetValue = TheValue

End Function

Code like this:
Dim strSql As String
strSql = "SELECT ...
Set rsWeight = CurrentDb.OpenRecordset(strSql)
...

You can use the underscore as a line continuation character if you close
the
string, e.g.:
strSql = "SELECT tblProductSKU.ProductShortSKU, " & _
"Sum(...

If you have mocked up a query, and want to copy the SQL statement into
code,
this might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html
It's a form where you paste the SQL statement in, and it massages it to
create the VBA string (including line endings and underscores) and puts
it
in your clipboard ready to paste into your code.

I needed a function to provide a value, so I created a query that would
return a single row and opened the query as a recordset to get the
value.
What syntax would I use to put the SQL directly in the code and avoid
the
step of creating the query? Thanks for any help you can provide.


SQL here:

SELECT tblProductSKU.ProductShortSKU,
Sum([tblWarehouseLocation].[WarehouseLocationMultiplier]*[tblWarehouseLocation].[WarehouseLocationQty])
AS TotalWeight
FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProductSKU.ProductShortSKU
HAVING (((tblProductSKU.ProductShortSKU)=GetVariable("ShortSKU")));


Function here:

Private Function GetTotalWeight(SKU As String)

Dim rsWeight As DAO.Recordset

ShortSKU = SKU

Set rsWeight = CurrentDb.OpenRecordset("qryTotalWeight", dbOpenDynaset)
rsWeight.MoveFirst

GetTotalWeight = rsWeight!TotalWeight
rsWeight.Close
End Function

 

[Allen Browne]

Minimal difference.

Every time you call CurrentDb, Access creates a new object, updates pending
collections, and then points the new object at the default database, i.e.
dbEngine.Workspaces(0).Databases(0). Using dbEngine(0)(0) directly has the
advantage that it is marginally faster (since the collections are not
updated) and more memory efficient (since a new object is not created.) It
has the disadvantages that the collections are not updated (e.g. any new
table you just created may not be found), and even that dbEngine(0)(0) may
not be the default database (in some weird cases.)

Therefore CurrentDb is generally a better choice, and is the only sensible
choice if the schema is changing, but dbEngine(0)(0) is a lower-level hack
if you are sure of what you are doing.

Don't worry too much about that distinction. DO worry about Option Explicit.
Personally, it is one of the first things I would work on when asked to fix
anyone's code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen - that worked perfectly. I know I should use Option
Explicit -
it's on my list of things to correct. I'm kind of new to Access and
learning
as I go so thanks for all your help and advice. BTW is there much
difference
between:

Set rs = dbEngine(0)(0).OpenRecordset(strSql)

and

Set rs = CurrentDb.OpenRecordset(strSql)

You need more detail on how to code this?

Private Function GetValue(SKU As String) As Variant
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT ...
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount <> 0 Then
GetValue = rs.Fields(0)
Else
GetValue = Null
End If
rs.Close
Set rs = Nothing
End Function

You need to OpenRecordset, check that there is a record, and then return
the
value of the first field. Otherwise I assume you want to return Null.

Can I strongly encourage you to use Option Explicit at the top of all
your
modules? Really helps debugging.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I set up your sql to VBA converter - that's great - thanks for that.
My
original post was slightly in error, what I'm trying to do may not be
possible but here's the question. I have a query that will return a
single
value. I'd like to have the function return that value by using the
SQL
statement. So is it possible to do that? The function is below. I get
a
run-time error 13 - type mismatch.

Private Function GetValue(SKU As String)

LongSKU = SKU

strSql = "SELECT tblWarehouseLocation.WarehouseLocationMultiplier AS
Multiplier " & vbCrLf & _
"FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU "
&
vbCrLf & _
"GROUP BY tblProductSKU.ProductSKU,
tblWarehouseLocation.WarehouseLocationMultiplier " & vbCrLf & _
"HAVING (((tblProductSKU.ProductSKU)=GetVariable(""LongSKU"")));"


Set TheValue = CurrentDb.OpenRecordset(strSql)
MsgBox TheValue
GetValue = TheValue

End Function

:

Code like this:
Dim strSql As String
strSql = "SELECT ...
Set rsWeight = CurrentDb.OpenRecordset(strSql)
...

You can use the underscore as a line continuation character if you
close
the
string, e.g.:
strSql = "SELECT tblProductSKU.ProductShortSKU, " & _
"Sum(...

If you have mocked up a query, and want to copy the SQL statement into
code,
this might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html
It's a form where you paste the SQL statement in, and it massages it
to
create the VBA string (including line endings and underscores) and
puts
it
in your clipboard ready to paste into your code.

I needed a function to provide a value, so I created a query that
would
return a single row and opened the query as a recordset to get the
value.
What syntax would I use to put the SQL directly in the code and
avoid
the
step of creating the query? Thanks for any help you can provide.


SQL here:

SELECT tblProductSKU.ProductShortSKU,
Sum([tblWarehouseLocation].[WarehouseLocationMultiplier]*[tblWarehouseLocation].[WarehouseLocationQty])
AS TotalWeight
FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProductSKU.ProductShortSKU
HAVING (((tblProductSKU.ProductShortSKU)=GetVariable("ShortSKU")));


Function here:

Private Function GetTotalWeight(SKU As String)

Dim rsWeight As DAO.Recordset

ShortSKU = SKU

Set rsWeight = CurrentDb.OpenRecordset("qryTotalWeight",
dbOpenDynaset)
rsWeight.MoveFirst

GetTotalWeight = rsWeight!TotalWeight
rsWeight.Close
End Function

 

[Allen Browne]

Minimal difference.

Every time you call CurrentDb, Access creates a new object, updates pending
collections, and then points the new object at the default database, i.e.
dbEngine.Workspaces(0).Databases(0). Using dbEngine(0)(0) directly has the
advantage that it is marginally faster (since the collections are not
updated) and more memory efficient (since a new object is not created.) It
has the disadvantages that the collections are not updated (e.g. any new
table you just created may not be found), and even that dbEngine(0)(0) may
not be the default database (in some weird cases.)

Therefore CurrentDb is generally a better choice, and is the only sensible
choice if the schema is changing, but dbEngine(0)(0) is a lower-level hack
if you are sure of what you are doing.

Don't worry too much about that distinction. DO worry about Option Explicit.
Personally, it is one of the first things I would work on when asked to fix
anyone's code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen - that worked perfectly. I know I should use Option
Explicit -
it's on my list of things to correct. I'm kind of new to Access and
learning
as I go so thanks for all your help and advice. BTW is there much
difference
between:

Set rs = dbEngine(0)(0).OpenRecordset(strSql)

and

Set rs = CurrentDb.OpenRecordset(strSql)

You need more detail on how to code this?

Private Function GetValue(SKU As String) As Variant
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT ...
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount <> 0 Then
GetValue = rs.Fields(0)
Else
GetValue = Null
End If
rs.Close
Set rs = Nothing
End Function

You need to OpenRecordset, check that there is a record, and then return
the
value of the first field. Otherwise I assume you want to return Null.

Can I strongly encourage you to use Option Explicit at the top of all
your
modules? Really helps debugging.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I set up your sql to VBA converter - that's great - thanks for that.
My
original post was slightly in error, what I'm trying to do may not be
possible but here's the question. I have a query that will return a
single
value. I'd like to have the function return that value by using the
SQL
statement. So is it possible to do that? The function is below. I get
a
run-time error 13 - type mismatch.

Private Function GetValue(SKU As String)

LongSKU = SKU

strSql = "SELECT tblWarehouseLocation.WarehouseLocationMultiplier AS
Multiplier " & vbCrLf & _
"FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU "
&
vbCrLf & _
"GROUP BY tblProductSKU.ProductSKU,
tblWarehouseLocation.WarehouseLocationMultiplier " & vbCrLf & _
"HAVING (((tblProductSKU.ProductSKU)=GetVariable(""LongSKU"")));"


Set TheValue = CurrentDb.OpenRecordset(strSql)
MsgBox TheValue
GetValue = TheValue

End Function

:

Code like this:
Dim strSql As String
strSql = "SELECT ...
Set rsWeight = CurrentDb.OpenRecordset(strSql)
...

You can use the underscore as a line continuation character if you
close
the
string, e.g.:
strSql = "SELECT tblProductSKU.ProductShortSKU, " & _
"Sum(...

If you have mocked up a query, and want to copy the SQL statement into
code,
this might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html
It's a form where you paste the SQL statement in, and it massages it
to
create the VBA string (including line endings and underscores) and
puts
it
in your clipboard ready to paste into your code.

I needed a function to provide a value, so I created a query that
would
return a single row and opened the query as a recordset to get the
value.
What syntax would I use to put the SQL directly in the code and
avoid
the
step of creating the query? Thanks for any help you can provide.


SQL here:

SELECT tblProductSKU.ProductShortSKU,
Sum([tblWarehouseLocation].[WarehouseLocationMultiplier]*[tblWarehouseLocation].[WarehouseLocationQty])
AS TotalWeight
FROM tblProductSKU INNER JOIN tblWarehouseLocation ON
tblProductSKU.ProductSKU = tblWarehouseLocation.WarehouseLocationSKU
GROUP BY tblProductSKU.ProductShortSKU
HAVING (((tblProductSKU.ProductShortSKU)=GetVariable("ShortSKU")));


Function here:

Private Function GetTotalWeight(SKU As String)

Dim rsWeight As DAO.Recordset

ShortSKU = SKU

Set rsWeight = CurrentDb.OpenRecordset("qryTotalWeight",
dbOpenDynaset)
rsWeight.MoveFirst

GetTotalWeight = rsWeight!TotalWeight
rsWeight.Close
End Function

 

[David W. Fenton]

If rs.RecordCount <> 0 Then
GetValue = rs.Fields(0)
Else
GetValue = Null
End If

Is it really necessary to have an Else clause here? It's going to be
Null if you assign nothing to it.

 

[David W. Fenton]

Every time you call CurrentDb, Access creates a new object,
updates pending collections, and then points the new object at the
default database, i.e. dbEngine.Workspaces(0).Databases(0). Using
dbEngine(0)(0) directly has the advantage that it is marginally
faster (since the collections are not updated)

The difference in speed is not enough to matter except if you do it
100s or 1000s of times, and there is no conceivable situation in
which you would repeatedly call CurrentDB() inside a loop, as the
returned object can never change inside the loop -- any call to it
belongs outside any loop. Thus, there is no *performance* reason to
prefer DBEngine(0)(0) over CurrentDB.

I avoid DBEngine(0)(0) simply because under the very rarest of
circumstances (i.e., after running a wizard), it could be pointing
to something other than the database open in the Access user
interface. CurrentDB() will never return a pointer to the wrong
database.

The chances are incredibly slim, yes, but given there is no
realistic performance benefit to balance that tiny risk, I use
CurrentDB.

Actually, I mostly used a cached database variable initialized via
CurrentDB.

 

[Allen Browne]

A variant defaults to Empty, not Null.

There are some very important differences, e.g.:
? Empty = 0, Null = 0
? Empty + 1, Null + 1

 

[David W. Fenton]

A variant defaults to Empty, not Null.

There are some very important differences, e.g.:
? Empty = 0, Null = 0
? Empty + 1, Null + 1

Ack. I wonder if I've got any production code out there that makes
this mistake? If I'd thought about it, I would have realized that
this is correct, but I work on the principle with non-Variant data
types of never setting the default return value.

Given that I have virtually no functions in any of my code that
return Variant type, I'm probably pretty well-protected, but now I'm
going to have to examine a lot of code for "As Variant" to see if
I'm setting the default return value.