Tip for code improvement and development time savings - set-oriented programming

[Steve Jorgensen]

First, an example - today, I wanted to print out the parameter values in a
querydef object at a break point in the code for subsequent manual debugging
of the query. In the past, I would have had to either manually write down the
values from the watch window, or break the code, write a custom procedure to
print the values for me, then run the code to the breakpoint again. By now,
though, my library of collection-oriented tools makes such manual labor or
custom hacks a thing of the past. I just type...

? JoinVector(MapVectorConcat(PropertyValFromSetItems (qdf.Parameters, "Name"),
qdf.Parameters, "="), vbCrLf)

This means to make a collection of parameter name values from the collection
of parameters, then concatenate each item from the collection of names to the
value of each corresponding item in the collection of parameters, inserting
line breaks between them, then concatenate together the resulting colelction
of strings with ", " inserted between each of those.

If I were doing the same thing in a procedure, I would deobfuscate that as
follows:

Dim colParamNames As VBA.Collection
Dim colNameEqValue As VBA.Collection
dim strNameEqValueList As String

Set colParamNames = PropertyValFromSetItems(qdf.Parameters, "Name")
Set colNameEqValues = MapVectorConcat(colParamNames,qdf.Parameters, "=")
strNameEqValueList = JoinVector(colNameEqValues, vbCrLf)
Debug.Print strNameEqValueList

The key concept here is that we replace what would be a special-purpose
looping and conditional string concatenation operations with calls to any of a
fairly small number of generic, reusable functions that work with vectors
(either arrays or collection objects).

The down side is that creating and populating all these collection objects is
not as efficient as the custom code, but that will rarely matter. The
benefits, however, are substantial when dealing with small sets (a -very-
frequent occurrence).

Here's a more potent example:

' Proc. body unindented to mitigate Usenet word wrap issues.
Public Sub DebugPrintRecordDiffs( _
rst1 As DAO.Recordset, _
rst2 As DAO.Recordset _
)
Dim colRst1FldNames As VBA.Collection
Dim colRst2FldsToCompare As VBA.Collection
Dim colMismatchIndxs As VBA.Collection
Dim colMismatchDetails As VBA.Collection
Dim colRst1MismatchFlds As VBA.Collection
Dim colRst2MismatchFlds As VBA.Collection
Dim colMismatch As VBA.Collection
Dim strMismatchResults As String

Set colRst1FldNames = PropertyValFromSetItems(rst1.Fields, "Name")
Set colRst2FldsToCompare = GetItemsForIndexes(rst2.Fields, colRst1FldNames)
Set colMismatchIndxs = VectorItemsNotSame(rst1.Fields, colRst2FldsToCompare)
Set colRst1MismatchFlds = GetItemsForIndexes(rst1.Flds, colMismatchIndxs)
Set colRst2MismatchFlds = GetItemsForIndexes(rst2.Flds, colMismatchIndxs)

Set colMismatch = PropertyValFromSetItems(colRst1MismatchFlds, "Name")
Set colMismatch = MapVectorConcat(colMismatch, colRst1MismatchFlds, ": ")
Set colMismatch = MapVectorConcat(colMismatch, colRst2MismatchFlds, " / ")
strMismatchResults = JoinVector(colMismatch, vbCrLf)

Debug.Print strMismatchResults

End Sub

We can do all this without any custom loops or nested logic because all the
loops and logic are encapsulated in our fairly simple, general-purpose set
processing procedure library. We pipe entire sets of items through atomic
processing steps pipe each item through those gyrations within nested, custom
control structures and procedure call stacks.

Since our set-processing procedure calls that do have the nested logic are
relatively small in number, simple by themselves, and heavily reused, we can
have high confidence in them, confidence that would be much harder to achieve
if we were writing custom code with unique, complex, deeply nested logic.

[Tony Toews]

Steve Jorgensen <no****@nospam.nospam> wrote:

Interesting article.

First, an example - today, I wanted to print out the parameter values in a
querydef object at a break point in the code for subsequent manual debugging
of the query. In the past, I would have had to either manually write down the
values from the watch window, or break the code, write a custom procedure to
print the values for me, then run the code to the breakpoint again.

After using querydefs for one of the first system I built back in A2.0 days I decided
to do things differenty.

What I always do when queries in VBA code get the slightest bit ugly I make a regular
query out of them. By ugly I mean one join to another table, a lot of fields or
whatever. I get this query working nicely showing all the records I want but I do
*NOT* put any selection criteriea in it.

I then create the select or action query in the QBE grid with dummy selection
criteria. I do NOT save it. Instead I click to the SQL code view and copy and paste
the code into the VBA. I then setup the strings and the proper WHERE clause criteria
from my calling form.

' increment the QOO MatItemSizeQty record, ie change 2 to 4
strSQL = "UPDATE [Mat Rcvg - Update MRR - Update Qtys] " & _
"SET miqQtyOnOrder = [miqQtyOnOrder]+" & sngInventoryQty & " " & _
"WHERE itdInvTransHeaderID=" & lngInvTransHeaderID & " AND " & _
"itdOriginalitdID=" & lngOriginalitdID & " AND itdQtyInQuarantine Is Null AND " & _
"itdOrderedQty<0;"
CurrentDb.Execute strSQL, dbFailOnError

where [Mat Rcvg - Update MRR - Update Qtys] is the ugly basic query. Mind you this
is ugly enough. <smile>

It looks like "SELECT InvTransactionsDetails.itdInvTransHeaderID,
InvTransactionsDetails.itdOriginalitdID, InvTransactionsDetails.itdOrderedQty,
InvTransactionsDetails.itdID, InvTransactionsDetails.itdQtyInQuarantine,
MaterialItemSizeQty.miqQtyOnOrder, MaterialItemSizeQty.miqQtyOnHand
FROM MaterialItemSizeQty INNER JOIN InvTransactionsDetails ON
MaterialItemSizeQty.miqID = InvTransactionsDetails.itdMateriaItemSizeQtyID;"

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

Steve Jorgensen <no****@nospam.nospam> wrote:

Interesting article.

First, an example - today, I wanted to print out the parameter values in a
querydef object at a break point in the code for subsequent manual debugging
of the query. In the past, I would have had to either manually write down the
values from the watch window, or break the code, write a custom procedure to
print the values for me, then run the code to the breakpoint again.

After using querydefs for one of the first system I built back in A2.0 days I decided
to do things differenty.

What I always do when queries in VBA code get the slightest bit ugly I make a regular
query out of them. By ugly I mean one join to another table, a lot of fields or
whatever. I get this query working nicely showing all the records I want but I do
*NOT* put any selection criteriea in it.

I then create the select or action query in the QBE grid with dummy selection
criteria. I do NOT save it. Instead I click to the SQL code view and copy and paste
the code into the VBA. I then setup the strings and the proper WHERE clause criteria
from my calling form.

' increment the QOO MatItemSizeQty record, ie change 2 to 4
strSQL = "UPDATE [Mat Rcvg - Update MRR - Update Qtys] " & _
"SET miqQtyOnOrder = [miqQtyOnOrder]+" & sngInventoryQty & " " & _
"WHERE itdInvTransHeaderID=" & lngInvTransHeaderID & " AND " & _
"itdOriginalitdID=" & lngOriginalitdID & " AND itdQtyInQuarantine Is Null AND " & _
"itdOrderedQty<0;"
CurrentDb.Execute strSQL, dbFailOnError

where [Mat Rcvg - Update MRR - Update Qtys] is the ugly basic query. Mind you this
is ugly enough. <smile>

It looks like "SELECT InvTransactionsDetails.itdInvTransHeaderID,
InvTransactionsDetails.itdOriginalitdID, InvTransactionsDetails.itdOrderedQty,
InvTransactionsDetails.itdID, InvTransactionsDetails.itdQtyInQuarantine,
MaterialItemSizeQty.miqQtyOnOrder, MaterialItemSizeQty.miqQtyOnHand
FROM MaterialItemSizeQty INNER JOIN InvTransactionsDetails ON
MaterialItemSizeQty.miqID = InvTransactionsDetails.itdMateriaItemSizeQtyID;"

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Steve Jorgensen]

Oops. There were a lot of strange editing errors in that last post, so here's
a cleaned up copy that might be more intelligible.

....

First, an example - today, I wanted to print out the parameter values in a
querydef object at a break point in the code for subsequent manual debugging
of the query. In the past, I would have had to either manually write down the
values from the watch window, or break the code, write a custom procedure to
print the values for me, then run the code to the breakpoint again. By now,
though, my library of collection-oriented tools makes such manual labor or
custom hacks a thing of the past. I just type...

? JoinVector(MapVectorConcat(PropertyValFromSetItems (qdf.Parameters, "Name"),
qdf.Parameters, "="), vbCrLf)

This means to make a collection of parameter name values from the collection
of parameters, then concatenate each item from the collection of names to the
value of each corresponding item in the collection of parameters, inserting
an equal sign between them, then concatenate together the resulting collection
of strings with line breaks inserted between each of those.

If I were doing the same thing in a procedure, I would deobfuscate that as
follows:

Dim colParamNames As VBA.Collection
Dim colNameEqValue As VBA.Collection
Dim strNameEqValueList As String

Set colParamNames = PropertyValFromSetItems(qdf.Parameters, "Name")
Set colNameEqValues = MapVectorConcat(colParamNames,qdf.Parameters, "=")
strNameEqValueList = JoinVector(colNameEqValues, vbCrLf)
Debug.Print strNameEqValueList

The key concept here is that we replace what would be a special-purpose
looping and conditional string concatenation operations with calls to any of a
fairly small number of generic, reusable functions that work with vectors
(either arrays or collection objects).

The down side is that creating and populating all these collection objects is
not as efficient as the custom code, but that will rarely matter. The
benefits, however, are substantial when dealing with small sets (a -very-
frequent occurrence).

Here's a more potent example:

' Proc. body unindented to mitigate Usenet word wrap issues.
Public Sub DebugPrintRecordDiffs( _
rst1 As DAO.Recordset, _
rst2 As DAO.Recordset _
)
Dim colRst1FldNames As VBA.Collection
Dim colRst2FldsToCompare As VBA.Collection
Dim colMismatchIndxs As VBA.Collection
Dim colMismatchDetails As VBA.Collection
Dim colRst1MismatchFlds As VBA.Collection
Dim colRst2MismatchFlds As VBA.Collection
Dim colMismatch As VBA.Collection
Dim strMismatchResults As String

Set colRst1FldNames = PropertyValFromSetItems(rst1.Fields, "Name")
Set colRst2FldsToCompare = GetItemsForIndexes(rst2.Fields, colRst1FldNames)
Set colMismatchIndxs = VectorItemsNotSame(rst1.Fields, colRst2FldsToCompare)
Set colRst1MismatchFlds = GetItemsForIndexes(rst1.Flds, colMismatchIndxs)
Set colRst2MismatchFlds = GetItemsForIndexes(rst2.Flds, colMismatchIndxs)

Set colMismatch = PropertyValFromSetItems(colRst1MismatchFlds, "Name")
Set colMismatch = MapVectorConcat(colMismatch, colRst1MismatchFlds, ": ")
Set colMismatch = MapVectorConcat(colMismatch, colRst2MismatchFlds, " / ")
strMismatchResults = JoinVector(colMismatch, vbCrLf)

Debug.Print strMismatchResults

End Sub

We can do all this without any custom loops or nested logic because all the
loops and logic are encapsulated in our fairly simple, general-purpose set
processing procedure library. We pipe entire sets of items through atomic
processing steps rather than pipe each item through those same gyrations using
nested, custom control structures and procedure call stacks.

Since our set-processing procedure calls that -do- contain some minor nested
logic are relatively small in number, simple by themselves, and heavily
reused, we can have high confidence in them, confidence that would be much
harder to achieve if we were writing custom code for each case with unique,
complex, deeply nested logic.

[Steve Jorgensen]

On Sat, 11 Dec 2004 22:51:49 GMT, Tony Toews <tt****@telusplanet.net> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote:

Interesting article.

First, an example - today, I wanted to print out the parameter values in a
querydef object at a break point in the code for subsequent manual debugging
of the query. In the past, I would have had to either manually write down the
values from the watch window, or break the code, write a custom procedure to
print the values for me, then run the code to the breakpoint again.

After using querydefs for one of the first system I built back in A2.0 days I decided
to do things differenty.

What I always do when queries in VBA code get the slightest bit ugly I make a regular
query out of them. By ugly I mean one join to another table, a lot of fields or
whatever. I get this query working nicely showing all the records I want but I do
*NOT* put any selection criteriea in it.

I then create the select or action query in the QBE grid with dummy selection
criteria. I do NOT save it. Instead I click to the SQL code view and copy and paste
the code into the VBA. I then setup the strings and the proper WHERE clause criteria
from my calling form.

' increment the QOO MatItemSizeQty record, ie change 2 to 4
strSQL = "UPDATE [Mat Rcvg - Update MRR - Update Qtys] " & _
"SET miqQtyOnOrder = [miqQtyOnOrder]+" & sngInventoryQty & " " & _
"WHERE itdInvTransHeaderID=" & lngInvTransHeaderID & " AND " & _
"itdOriginalitdID=" & lngOriginalitdID & " AND itdQtyInQuarantine Is Null AND " & _
"itdOrderedQty<0;"
CurrentDb.Execute strSQL, dbFailOnError

where [Mat Rcvg - Update MRR - Update Qtys] is the ugly basic query. Mind you this
is ugly enough. <smile>

It looks like "SELECT InvTransactionsDetails.itdInvTransHeaderID,
InvTransactionsDetails.itdOriginalitdID, InvTransactionsDetails.itdOrderedQty,
InvTransactionsDetails.itdID, InvTransactionsDetails.itdQtyInQuarantine,
MaterialItemSizeQty.miqQtyOnOrder, MaterialItemSizeQty.miqQtyOnHand
FROM MaterialItemSizeQty INNER JOIN InvTransactionsDetails ON
MaterialItemSizeQty.miqID = InvTransactionsDetails.itdMateriaItemSizeQtyID;"

I used to do the same thing, but I have found several good reasons for using
querydefs with parameters. Note that I do -not- mean that the querydef has to
be a saved querydef object in the database - it usually isn't. I just mean
that I create a temporary querydef object, write a static SQL string to it
that includes expressions using parameters, then set the parameters of the
querydef before executing it or using it to open a recordset.

It seems like we've had a similar discourse before, but here again are some
reasons (possibly some I didn't think of in the previous thread) that I find
querydef parameters useful).

1. The querydef need only be compiled once so long as the querydef object
remains in existence.
2. You no longer need to worry about what happens if a parameter is allowed to
be NULL (often happens with INSERT queries).
3. You don't need to worry about SQL insertion security holes and bugs, such
as what happens when the user enters text with ' or " in it.
4. The named parameter becomes documentation for what the parameter is -for-
in the query, not just how it is used in the query or where it comes from in
the code.
5. Without actually executing the code, you can copy the string constant into
the immediate window, print it, copy it again into the query editor, and do a
test run. You don't have to first replace all the infix expressions with
values or parameter names.
6. You don't have to format each date value before inserting it into SQL.
7. In a client-server application, Access will create a prepared statement
with the same parameters as the querydef. When the same query is run again,
the MS SQL Server back-end will see that it already has a plan for a statement
with that signature and use the existing plan.
8. You can create a standard procedure for reporting meaningful errors when
the SQL syntax is wrong such that DAO thinks it needs more query parameters.
Simpy pass the Querydef object and the number of parameters expected. If the
parameter count is wrong, the procedure raises an error message that includes
the names of all the parameters. Usually, from this, it's pretty obvious
which parameter is wierd and what's wrong with the SQL that caused that.

[Steve Jorgensen]

Oops. There were a lot of strange editing errors in that last post, so here's
a cleaned up copy that might be more intelligible.

....

First, an example - today, I wanted to print out the parameter values in a
querydef object at a break point in the code for subsequent manual debugging
of the query. In the past, I would have had to either manually write down the
values from the watch window, or break the code, write a custom procedure to
print the values for me, then run the code to the breakpoint again. By now,
though, my library of collection-oriented tools makes such manual labor or
custom hacks a thing of the past. I just type...

? JoinVector(MapVectorConcat(PropertyValFromSetItems (qdf.Parameters, "Name"),
qdf.Parameters, "="), vbCrLf)

This means to make a collection of parameter name values from the collection
of parameters, then concatenate each item from the collection of names to the
value of each corresponding item in the collection of parameters, inserting
an equal sign between them, then concatenate together the resulting collection
of strings with line breaks inserted between each of those.

If I were doing the same thing in a procedure, I would deobfuscate that as
follows:

Dim colParamNames As VBA.Collection
Dim colNameEqValue As VBA.Collection
Dim strNameEqValueList As String

Set colParamNames = PropertyValFromSetItems(qdf.Parameters, "Name")
Set colNameEqValues = MapVectorConcat(colParamNames,qdf.Parameters, "=")
strNameEqValueList = JoinVector(colNameEqValues, vbCrLf)
Debug.Print strNameEqValueList

The key concept here is that we replace what would be a special-purpose
looping and conditional string concatenation operations with calls to any of a
fairly small number of generic, reusable functions that work with vectors
(either arrays or collection objects).

The down side is that creating and populating all these collection objects is
not as efficient as the custom code, but that will rarely matter. The
benefits, however, are substantial when dealing with small sets (a -very-
frequent occurrence).

Here's a more potent example:

' Proc. body unindented to mitigate Usenet word wrap issues.
Public Sub DebugPrintRecordDiffs( _
rst1 As DAO.Recordset, _
rst2 As DAO.Recordset _
)
Dim colRst1FldNames As VBA.Collection
Dim colRst2FldsToCompare As VBA.Collection
Dim colMismatchIndxs As VBA.Collection
Dim colMismatchDetails As VBA.Collection
Dim colRst1MismatchFlds As VBA.Collection
Dim colRst2MismatchFlds As VBA.Collection
Dim colMismatch As VBA.Collection
Dim strMismatchResults As String

Set colRst1FldNames = PropertyValFromSetItems(rst1.Fields, "Name")
Set colRst2FldsToCompare = GetItemsForIndexes(rst2.Fields, colRst1FldNames)
Set colMismatchIndxs = VectorItemsNotSame(rst1.Fields, colRst2FldsToCompare)
Set colRst1MismatchFlds = GetItemsForIndexes(rst1.Flds, colMismatchIndxs)
Set colRst2MismatchFlds = GetItemsForIndexes(rst2.Flds, colMismatchIndxs)

Set colMismatch = PropertyValFromSetItems(colRst1MismatchFlds, "Name")
Set colMismatch = MapVectorConcat(colMismatch, colRst1MismatchFlds, ": ")
Set colMismatch = MapVectorConcat(colMismatch, colRst2MismatchFlds, " / ")
strMismatchResults = JoinVector(colMismatch, vbCrLf)

Debug.Print strMismatchResults

End Sub

We can do all this without any custom loops or nested logic because all the
loops and logic are encapsulated in our fairly simple, general-purpose set
processing procedure library. We pipe entire sets of items through atomic
processing steps rather than pipe each item through those same gyrations using
nested, custom control structures and procedure call stacks.

Since our set-processing procedure calls that -do- contain some minor nested
logic are relatively small in number, simple by themselves, and heavily
reused, we can have high confidence in them, confidence that would be much
harder to achieve if we were writing custom code for each case with unique,
complex, deeply nested logic.

[Steve Jorgensen]

On Sat, 11 Dec 2004 22:51:49 GMT, Tony Toews <tt****@telusplanet.net> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote:

Interesting article.

First, an example - today, I wanted to print out the parameter values in a
querydef object at a break point in the code for subsequent manual debugging
of the query. In the past, I would have had to either manually write down the
values from the watch window, or break the code, write a custom procedure to
print the values for me, then run the code to the breakpoint again.

After using querydefs for one of the first system I built back in A2.0 days I decided
to do things differenty.

What I always do when queries in VBA code get the slightest bit ugly I make a regular
query out of them. By ugly I mean one join to another table, a lot of fields or
whatever. I get this query working nicely showing all the records I want but I do
*NOT* put any selection criteriea in it.

I then create the select or action query in the QBE grid with dummy selection
criteria. I do NOT save it. Instead I click to the SQL code view and copy and paste
the code into the VBA. I then setup the strings and the proper WHERE clause criteria
from my calling form.

' increment the QOO MatItemSizeQty record, ie change 2 to 4
strSQL = "UPDATE [Mat Rcvg - Update MRR - Update Qtys] " & _
"SET miqQtyOnOrder = [miqQtyOnOrder]+" & sngInventoryQty & " " & _
"WHERE itdInvTransHeaderID=" & lngInvTransHeaderID & " AND " & _
"itdOriginalitdID=" & lngOriginalitdID & " AND itdQtyInQuarantine Is Null AND " & _
"itdOrderedQty<0;"
CurrentDb.Execute strSQL, dbFailOnError

where [Mat Rcvg - Update MRR - Update Qtys] is the ugly basic query. Mind you this
is ugly enough. <smile>

It looks like "SELECT InvTransactionsDetails.itdInvTransHeaderID,
InvTransactionsDetails.itdOriginalitdID, InvTransactionsDetails.itdOrderedQty,
InvTransactionsDetails.itdID, InvTransactionsDetails.itdQtyInQuarantine,
MaterialItemSizeQty.miqQtyOnOrder, MaterialItemSizeQty.miqQtyOnHand
FROM MaterialItemSizeQty INNER JOIN InvTransactionsDetails ON
MaterialItemSizeQty.miqID = InvTransactionsDetails.itdMateriaItemSizeQtyID;"

I used to do the same thing, but I have found several good reasons for using
querydefs with parameters. Note that I do -not- mean that the querydef has to
be a saved querydef object in the database - it usually isn't. I just mean
that I create a temporary querydef object, write a static SQL string to it
that includes expressions using parameters, then set the parameters of the
querydef before executing it or using it to open a recordset.

It seems like we've had a similar discourse before, but here again are some
reasons (possibly some I didn't think of in the previous thread) that I find
querydef parameters useful).

1. The querydef need only be compiled once so long as the querydef object
remains in existence.
2. You no longer need to worry about what happens if a parameter is allowed to
be NULL (often happens with INSERT queries).
3. You don't need to worry about SQL insertion security holes and bugs, such
as what happens when the user enters text with ' or " in it.
4. The named parameter becomes documentation for what the parameter is -for-
in the query, not just how it is used in the query or where it comes from in
the code.
5. Without actually executing the code, you can copy the string constant into
the immediate window, print it, copy it again into the query editor, and do a
test run. You don't have to first replace all the infix expressions with
values or parameter names.
6. You don't have to format each date value before inserting it into SQL.
7. In a client-server application, Access will create a prepared statement
with the same parameters as the querydef. When the same query is run again,
the MS SQL Server back-end will see that it already has a plan for a statement
with that signature and use the existing plan.
8. You can create a standard procedure for reporting meaningful errors when
the SQL syntax is wrong such that DAO thinks it needs more query parameters.
Simpy pass the Querydef object and the number of parameters expected. If the
parameter count is wrong, the procedure raises an error message that includes
the names of all the parameters. Usually, from this, it's pretty obvious
which parameter is wierd and what's wrong with the SQL that caused that.

[Steve Jorgensen]

On Sun, 12 Dec 2004 01:06:15 GMT, Chuck Grimsby
<c.*******@worldnet.att.net.invalid> wrote:

On Sat, 11 Dec 2004 22:51:49 GMT, Tony Toews <tt****@telusplanet.net>
wrote:

Steve Jorgensen <no****@nospam.nospam> wrote:
Interesting article.

First, an example - today, I wanted to print out the parameter values in a
querydef object at a break point in the code for subsequent manual debugging
of the query. In the past, I would have had to either manually write down the
values from the watch window, or break the code, write a custom procedure to
print the values for me, then run the code to the breakpoint again.

After using querydefs for one of the first system I built back in A2.0 days I decided
to do things differenty.
What I always do when queries in VBA code get the slightest bit ugly I make a regular
query out of them. By ugly I mean one join to another table, a lot of fields or
whatever. I get this query working nicely showing all the records I want but I do
*NOT* put any selection criteriea in it.
I then create the select or action query in the QBE grid with dummy selection
criteria. I do NOT save it. Instead I click to the SQL code view and copy and paste
the code into the VBA. I then setup the strings and the proper WHERE clause criteria
from my calling form.
' increment the QOO MatItemSizeQty record, ie change 2 to 4
strSQL = "UPDATE [Mat Rcvg - Update MRR - Update Qtys] " & _
"SET miqQtyOnOrder = [miqQtyOnOrder]+" & sngInventoryQty & " " & _
"WHERE itdInvTransHeaderID=" & lngInvTransHeaderID & " AND " & _
"itdOriginalitdID=" & lngOriginalitdID & " AND itdQtyInQuarantine Is Null AND " & _
"itdOrderedQty<0;"
CurrentDb.Execute strSQL, dbFailOnError
where [Mat Rcvg - Update MRR - Update Qtys] is the ugly basic query. Mind you this
is ugly enough. <smile>
It looks like "SELECT InvTransactionsDetails.itdInvTransHeaderID,
InvTransactionsDetails.itdOriginalitdID, InvTransactionsDetails.itdOrderedQty,
InvTransactionsDetails.itdID, InvTransactionsDetails.itdQtyInQuarantine,
MaterialItemSizeQty.miqQtyOnOrder, MaterialItemSizeQty.miqQtyOnHand
FROM MaterialItemSizeQty INNER JOIN InvTransactionsDetails ON
MaterialItemSizeQty.miqID = InvTransactionsDetails.itdMateriaItemSizeQtyID;"

This is where using Aliases come in handy, Tony. Mind you, I'm not
always happy using aliased tables since *I* feel it tends to confuse
things more then it helps, but NotePad's a great tool when you're
editing SQL. <Grin>

SELECT B.itdInvTransHeaderID,
B.itdOriginalitdID,
B.itdOrderedQty,
B.itdID,
B.itdQtyInQuarantine,
A.miqQtyOnOrder,
A.miqQtyOnHand
FROM MaterialItemSizeQty AS A
INNER JOIN InvTransactionsDetails AS B ON
A.miqID = B.itdMateriaItemSizeQtyID;

I've also discovered that (well, for me anyways), formatting the SQL
to something easy to read is a great way to figure out what is going
on (or going wrong). Once Aliases are used, and the SQL is formatted,
it's not so ugly, is it?

(Don't forget to set Notepad and Access to use a monospaced font, such
as courier new, for best effect.)

When defining SQL text in code (which I do a lot of), I like to format the
string expression itself, so for the example you gave above, it would be...

strSql = _
"SELECT B.itdInvTransHeaderID, " & _
"B.itdOriginalitdID, " & _
"B.itdOrderedQty, " & _
"B.itdID, " & _
"B.itdQtyInQuarantine, " & _
"A.miqQtyOnOrder, " & _
"A.miqQtyOnHand " & _
"FROM MaterialItemSizeQty AS A " & _
"INNER JOIN InvTransactionsDetails AS B " & _
"ON A.miqID = B.itdMateriaItemSizeQtyID;"

[Tony Toews]

Steve Jorgensen <no****@nospam.nospam> wrote:

It seems like we've had a similar discourse before,
Yes, now that you say that we have had this discussion a while back.
but here again are some
reasons (possibly some I didn't think of in the previous thread) that I find
querydef parameters useful).

But none of the below mentioned items are compelling enough for me to abandon my
method of doing things. <shrug>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote:

This is where using Aliases come in handy, Tony.

Yeah, I can see that. But that SELECT query is a query object anyhow so I can see
the data and such. It's the update/insert based on that query that I create in VBA
code.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm