SQL Update - 2 tables

[Guest]

Hi all,
is it possible to run an update on an unbound form and have it update a
table from another table which was recently updated? My code is as follows:
DoCmd.RunSQL "UPDATE Production_Shipments " & _
"SET Production_Shipments.Ship_Order_ID = Ship_Order.Ship_Order_ID " & _
"WHERE Production_Shipments.Item_ID = Ship_Order.Item_ID"
I am trying to insert a field in one table with a value from the other,
based on the Item_ID being similar. When I run the command, it brings up a
text box entry screen for me to enter in the values for both Ship_Order
fields (Ship_Order_ID and Item_ID), as if the table is not being looked at.
Is SQL the wrong way to approach this? How can I get the other table to be
viewed so I can update the other?
Help!
-gary

 

[John Vinson]

Hi all,
is it possible to run an update on an unbound form and have it update a
table from another table which was recently updated? My code is as follows:
DoCmd.RunSQL "UPDATE Production_Shipments " & _
"SET Production_Shipments.Ship_Order_ID = Ship_Order.Ship_Order_ID " & _
"WHERE Production_Shipments.Item_ID = Ship_Order.Item_ID"
I am trying to insert a field in one table with a value from the other,
based on the Item_ID being similar. When I run the command, it brings up a
text box entry screen for me to enter in the values for both Ship_Order
fields (Ship_Order_ID and Item_ID), as if the table is not being looked at.
Is SQL the wrong way to approach this? How can I get the other table to be
viewed so I can update the other?
Help!
-gary

You need to include the table in the query, at least!

Try

DoCmd.RunSQL "UPDATE Production_Shipments INNER JOIN Ship_Order " _
& " ON Production_Shipments.Item_ID = Ship_Order.Item_ID" _
& " SET Production_Shipments.Ship_Order_ID = " _
& " Ship_Order.Ship_Order_ID "

I fear this won't do just what you want, thoug;h it will update EVERY
RECORD in the Production_Shipments table for that Item_ID, no matter
which shipment it was. What are you actually trying to accomplish with
this query? Is there already a record in Production_Shipments, or do
you perhaps want an Append query instead?

John W. Vinson[MVP]

 

[Guest]

Hi John,
Thanks for the reply. I will not be adding a new record to production
shipments, as the record already exists, I just want to add the Ship_Order_ID
from the Ship_Order table that corresponds to the Item_ID that is in both
tables. Would an APPEND query do the same, or would it add a new record?
I only want it to update the Ship_Order_ID (which already exists in
Ship_Order) where the Item_ID is the same between the two tables
Does that make sense? What are my options?
Thanks!
-gary

 

[John Vinson]

Hi John,
Thanks for the reply. I will not be adding a new record to production
shipments, as the record already exists, I just want to add the Ship_Order_ID
from the Ship_Order table that corresponds to the Item_ID that is in both
tables. Would an APPEND query do the same, or would it add a new record?

An Append query does just that: adds a new record. You clearly need an
Update query.

I only want it to update the Ship_Order_ID (which already exists in
Ship_Order) where the Item_ID is the same between the two tables
Does that make sense? What are my options?

So the Item_ID is going to be unique in the table? My concern (based
on total ignorance of your business model) was that a given Item_ID
might appear in many shipments. If that's not an issue, then the
update query that I posted should work. Did you try it?

John W. Vinson[MVP]

 

[Guest]

Hi John,
I just tried it, added a little final tweak, and it worked perfectly!!!
Thank you again and I appreciate your patience with me!!
-gary

DoCmd.RunSQL "UPDATE Production_Shipments INNER JOIN Ship_Order " _
& " ON Production_Shipments.Item_ID = Ship_Order.Item_ID" _
& " SET Production_Shipments.Ship_Order_ID = " _
& " Ship_Order.Ship_Order_ID " _
& " WHERE Production_Shipments.Ship_Order_ID = 0 "

 

[Guest]

Hi again John,
I am hoping you might be able to help me with another SQL issue I am having.
I am attempting to create a search form where I will collect certain fields,
ie, last name, zip code, phone number... and have currently the following
code:
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName=" & "'" &
Me.LastName.Value & "'"
This works great for the search of one field, however, I would like to
search on last name AND zip code (for example, and also a combination of
other fields)
I have attempted to start with looking up Last Name and Zip (each have a
destination text field, Zip needs to be as we will be using Canadian zip
codes, and also Zip codes that begin with 0) and use the following:
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName=" & "'" &
Me.LastName.Value & "'" And "ShipTo_ZIP5=" & "'" & Me.ZipCode.Value & "'"
I get a Type Mismatch error (Runtime 13) which leads me to believe the
format is incorrect. However, if I only look up Zip as:
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_ZIP5=" & "'" &
Me.ZipCode.Value & "'"
It works fine. Any thoughts on this?
Thanks again!
-gary

 

[John Vinson]

Hi again John,
I am hoping you might be able to help me with another SQL issue I am having.
I am attempting to create a search form where I will collect certain fields,
ie, last name, zip code, phone number... and have currently the following
code:
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName=" & "'" &
Me.LastName.Value & "'"
This works great for the search of one field, however, I would like to
search on last name AND zip code (for example, and also a combination of
other fields)
I have attempted to start with looking up Last Name and Zip (each have a
destination text field, Zip needs to be as we will be using Canadian zip
codes, and also Zip codes that begin with 0) and use the following:
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName=" & "'" &
Me.LastName.Value & "'" And "ShipTo_ZIP5=" & "'" & Me.ZipCode.Value & "'"
I get a Type Mismatch error (Runtime 13) which leads me to believe the
format is incorrect. However, if I only look up Zip as:
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_ZIP5=" & "'" &
Me.ZipCode.Value & "'"

The quotes are tricky... and you've got more than you need. It's not
necessary to have & "'" & in the code - the singlequote is just a
character like any other character.

HOWEVER - using ' to delimit a LastName search will cause errors if
you should ever have a LastName value like O'Hara or d'Angelo. It's
safer to use " to delimit text strings if that's possible. You can
include " in a string delimited by " by using doubled-up doublequotes
(how's that for doubletalk!) You don't need the Value property, it's
the default; and the keyword AND needs to be within the quotes.

Try

Dim strWhere As String
strWhere = "ShipTo_LName=""" & Me.LastName _
& """ And ShipTo_ZIP5='" & Me.ZipCode & "'"
DoCmd.OpenForm "INQUIRY_MAIN_View", , , strWhere

strWhere will be a string like


ShipTo_LName="O'Hara" And ShipTo_ZIP5='02138'

John W. Vinson[MVP]

 

[Guest]

Awesome! Thanks again! That really helped me out!
One final question, maybe...
I now have a working search form if they input both a last name and zip,
however, if they only put in one or the other, it does not work as I am only
meeting one of the searches. Is there a way I can have it do both? Either
look up only a last name, or only a zip code, or a combination of the zip
code and last name? However, if I do put in a last name and zip code, I only
want it to return values that have both the last name and zip, not ones that
only have one or the other. Therefore, I do not think the OR will work.
Sorry, I realize this is getting ridiculous... Thanks again!
-gary

 

[John Vinson]

Is there a way I can have it do both? Either
look up only a last name, or only a zip code, or a combination of the zip
code and last name?

Since you're building up the WHERE clause in code, just do it a piece
at a time - if the control is null, just don't include it! e.g.

strWhere = "True"
If Not IsNull(Me.LastName) Then
strWhere = strWhere & " AND ShipTo_LName=""" & Me.LastName & """"
End If
If Not IsNull(Me.ZipCode) Then
strWhere = strWhere & " And ShipTo_ZIP5='" & Me.ZipCode & "'"
End If

This can go on for dozens of fields if you wish. The TRUE at the
beginning lets you start each field's criterion with AND without
generating an error; the disadvantage - if there is one - is that the
user can open the form without entering any criteria at all, and see
all the records.

John W. Vinson[MVP]

 

[Guest]

My goodness, thanks again, and again, and again... this is great! even the
"disadvantage" is just what I wanted!!
Thank you John!
-gary