DoCmd.RunSQL question 1

[Storyteller]

Hello All,
The following codes does work.

Private Sub cmdUpdateProductsTable_Click()
DoCmd.RunSQL " UPDATE (qryWorkCompleted INNER JOIN tblProducts " & _
" ON qryWorkCompleted.BarcodeNumber = tblProducts.BarCodeNumber) " & _
" INNER JOIN tblWorkOrder ON qryWorkCompleted.WorkOrderID = tblWorkOrder.WorkOrderID " & _
" SET tblProducts.UnitsInStock = [tblProducts]![UnitsInStock]-[qryWorkCompleted].[Quantity] " & _
" WHERE (([qryContactInfo].[WorkOrderID] =[qryWorkCompleted].[WorkOrderID]));"
End Sub

However I get a Parameter prompt for [qryContactInfo].[WorkOrderID]
What I would like to have happen is the current records value of [qryContactInfo].[WorkOrderID] be used.

Any suggestions?

Thanks for all your help.
Michael

 

[Darrylles]

Hi,

You get this because WorkOrderId is not defined (asked for) in your qryContactInfo query.

(Check the spelling). If it is, then run the query - do you get a positive result from that?

It might help a little if I knew which MS application you are using (you are in VB for Applications).

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[krinid]

The problem is that you have not included the query qryContactInfo in your UPDATE clause. You need to put in another join to include qryContactInfo.

 

[Darrylles]

Hi krinid,

In short, do you mean the parentheses aren't correct?

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Storyteller]

Hello All,

Thanks for your advice. I am creating a Work Order/Inventory management program in Access 2000 and I am attempting to use as much VBA as possible. I realize that this question probably has more to do with SQL than VBA, but any and all advice is welcomed and very much appreciated.

A little context. The purpose of the code is to subtract the number of units used in repairing a radio from the products table. So, if I use two switches in the repair I want to have my units in stock level reduced by two.

I copied this SQL code from an Update Action Query created in Access 2000 and pasted it into the DoCmd.

The main form's datasource is qryContactInfo and the subform's datasource is qryWorkCompleted linked by WorkOrderID.

The observation about no reference to qryContactInfo in the JOIN statement is correct. I will rework the query and code and see if it works.

Regards,
Michael

 

[krinid]

No, rather the content is incorrect. For example:

(in this example 'C' is the equivalent of your 'qryContactInfo')

SELECT *
FROM A JOIN B ON A.X = B.Y
WHERE C.Z = A.W

This is invalid because the table (or query) C is not included in the FROM clause anywhere. ie: The DB has no idea how to match the records of C to neither A nor B. Since C appears nowhere in the FROM clause, it's treated as a parameter and you must enter a value.

In this simple case, you could do this to fix it:

SELECT *
FROM (A JOIN B ON A.X = B.Y) JOIN C ON A.L = C.K
WHERE C.Z = A.W

Now C is included in the FROM clause and the DB knows that the L field of A can be joined to the K field of C, thus references to C in the WHERE clause is valid.

(On a tangent, an even simpler example is SELECT * FROM A WHERE B.Q = {someValue} --> B isn't in the FROM clause, so it's invalid).

As for your query, I don't know exactly how 'qryContactInfo' relates to the other tables/queries, so I'll make a general suggestion:

(this is just the SQL code itself)

#1
UPDATE ((qryWorkCompleted INNER JOIN tblProducts ON qryWorkCompleted.BarcodeNumber = tblProducts.BarCodeNumber)
INNER JOIN tblWorkOrder ON qryWorkCompleted.WorkOrderID = tblWorkOrder.WorkOrderID) INNER JOIN qryContactInfo ON qryContactInfo.[FieldA] = [Query/Table].[FieldB]
SET tblProducts.UnitsInStock = [tblProducts]![UnitsInStock]-[qryWorkCompleted].[Quantity]
WHERE ([qryContactInfo].[WorkOrderID] =[qryWorkCompleted].[WorkOrderID]);

where:
[Query/Table] is a query or table ALREADY REFERENCED in your UPDATE clause (if it's a new table/query, you'll have the same problem
[FieldA] is the field in qryContactInfo that relates to [FieldB] in [Query/Table]

However, looking at your SQL statement, it appears that qryContactInfo relates to qryWorkCompleted by the commonly named WorkOrderID field, and thus this could be put into the UPDATE clause itself and you wouldn't need a WHERE clause at all, as follows:

#2
UPDATE ((qryWorkCompleted INNER JOIN tblProducts ON qryWorkCompleted.BarcodeNumber = tblProducts.BarCodeNumber)
INNER JOIN tblWorkOrder ON qryWorkCompleted.WorkOrderID = tblWorkOrder.WorkOrderID) INNER JOIN qryContactInfo ON [qryContactInfo].[WorkOrderID] =[qryWorkCompleted].[WorkOrderID]
SET tblProducts.UnitsInStock = [tblProducts]![UnitsInStock]-[qryWorkCompleted].[Quantity];

Note that in this case, as compared to above:
[Query/Table] = [qryWorkCompleted] which is OK because it already exists in the UPDATE clause
[FieldA] & [FieldB] are both WorkOrderID in the corresponding queries

In general, you want to use a WHERE clause to limit the range of a given field. eg: qryContactInfo.WorkOrderID = 0, qryContactInfo.WorkOrderID > 50000 etc --this restricts the accepted range of a field from a given table.

And, in general, you want to use a JOIN clause when a field from one table is related (or the same data) in another table. eg: qryContactInfo.WorkOrderID = qryWorkCompleted.WorkOrderID, although you don't necessarily have to use '='. The important part is that the fields of different tables have a relation.

Sorry, this kind of got to long-winded. Hope it helps...
krinid

 

[Storyteller]

Hello krinid,
Thanks for the advice. I'll follow up on it once I've digested it all.

Here is a 'Star' for your help.

Regards,
Michael