Help with Update Query

[mattc66 via AccessMonster.com]

How do I setup my code so that the following query will fire only on the
items on form match the order number?

UPDATE tblItemSerShipLog INNER JOIN tblLocationDataC2 ON (tblItemSerShipLog.
SerialNum = tblLocationDataC2.SerialNum) AND (tblItemSerShipLog.Item =
tblLocationDataC2.Item) SET tblLocationDataC2.OrderNum = [tblItemSerShipLog].
[OrderNum], tblItemSerShipLog.CustNum = [tblItemSerShipLog].[CustNum],
tblLocationDataC2.Shipped = Yes, tblLocationDataC2.DateShipped =
[tblItemSerShipLog].[TransDate];

 

[Stefan Hoffmann]

hi Matt,

How do I setup my code so that the following query will fire only on the
items on form match the order number?

Place an If clause around it... btw, what code are you talking about?

*Perhaps* you mean something different: How should you rewrite the query
that it only updates the matching orders?

Using table aliases:

UPDATE tblItemSerShipLog L
INNER JOIN tblLocationDataC2 D
ON (L.SerialNum = D.SerialNum) AND (L.Item = D.Item)
SET D.OrderNum = L.[OrderNum],
L.CustNum = [L].[CustNum],
D.Shipped = Yes,
D.DateShipped = [L].[TransDate]
WHERE D.OrderNum = Forms!yourForm![OrderNum]

hmm, L.CustNum = [L].[CustNum] makes no sense as it does not change
anything.



mfG
--> stefan <--

 

[mattc66 via AccessMonster.com]

How could I just run this code in my form on a click event?

hi Matt,

How do I setup my code so that the following query will fire only on the
items on form match the order number?

Place an If clause around it... btw, what code are you talking about?

*Perhaps* you mean something different: How should you rewrite the query
that it only updates the matching orders?

Using table aliases:

UPDATE tblItemSerShipLog L
INNER JOIN tblLocationDataC2 D
ON (L.SerialNum = D.SerialNum) AND (L.Item = D.Item)
SET D.OrderNum = L.[OrderNum],
L.CustNum = [L].[CustNum],
D.Shipped = Yes,
D.DateShipped = [L].[TransDate]
WHERE D.OrderNum = Forms!yourForm![OrderNum]

hmm, L.CustNum = [L].[CustNum] makes no sense as it does not change
anything.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Matt,

How could I just run this code in my form on a click event?

Could you please post a concise and complete example of you have already
and rephrase your question to be more precise than that?

Otherwise save this query and use

DoCmd.OpenQuery "yourNameOfThisQuery"

to execute it.


mfG
--> stefan <--

 

[mattc66 via AccessMonster.com]

I think you are right on target with what you have told me.

I am trying to update the data in tblLocationDataC2 from Data in
tblItemSerShipLog. The feilds to update are as follows:
OrderNum
CustNum
TransDate
DateShipped set to YES

Do this on the data that match the Order Number found on form frmShipMain and
match the tblItemSerShipLog.

When I run the below query with a valid order number it comes up blank.

UPDATE tblItemSerShipLog AS L INNER JOIN tblLocationDataC2 AS D ON (L.Item =
D.Item) AND (L.SerialNum = D.SerialNum) SET D.OrderNum = L.[OrderNum], D.
CustNum = [L].[CustNum], D.Shipped = Yes, D.DateShipped = [L].[TransDate]
WHERE (((D.OrderNum)=[Forms]![frmShipMain]![OrderNum]));

 

[mattc66 via AccessMonster.com]

Correction

I think you are right on target with what you have told me.

I am trying to update the data in tblLocationDataC2 from Data in
tblItemSerShipLog. The feilds to update are as follows:
OrderNum
CustNum
TransDate = DateShipped
Shipped set to YES

Do this on the data that match the Order Number found on form frmShipMain and
match the tblItemSerShipLog.

When I run the below query with a valid order number it comes up blank.

UPDATE tblItemSerShipLog AS L INNER JOIN tblLocationDataC2 AS D ON (L.Item =
D.Item) AND (L.SerialNum = D.SerialNum) SET D.OrderNum = L.[OrderNum], D.
CustNum = [L].[CustNum], D.Shipped = Yes, D.DateShipped = [L].[TransDate]
WHERE (((D.OrderNum)=[Forms]![frmShipMain]![OrderNum]));

hi Matt,

[quoted text clipped - 10 lines]

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Matt,

Do this on the data that match the Order Number found on form frmShipMain and
match the tblItemSerShipLog.

When I run the below query with a valid order number it comes up blank.

When you're switching the queries view to 'View' then this may be
correct, as it only shows the existing data, not the updated one. You
need to press the exclamation mark.


mfG
--> stefan <--

 

[mattc66 via AccessMonster.com]

It works now.

hi Matt,

When you're switching the queries view to 'View' then this may be
correct, as it only shows the existing data, not the updated one. You
need to press the exclamation mark.

mfG
--> stefan <--