Compare data from two tables

[Samantha]

I have a query that compares the OrderDate field from tables Temp and
Quantity. If they are the same, then I want to update the Qty field from
Temp table to Quantity table.
The problem is: I am getting a syntax error on referencing the two fields
(OrderDate) from both tables? Does anyone know what wrong with this code?

Private Sub Command0_Click()
Dim db As Database
Dim rsTemp As Recordset
Dim rsQuantity As Recordset
Dim strSQLqty As String
Dim strSQLtemp As String
Dim strPOID As String
Dim OrderDate As Date

Set db = CurrentDb

strSQLtemp = "SELECT Temp.POID, Temp.OrderDate, Temp.Qty, Quantity.Qty,
Quantity.OrderDate, Quantity.POID"
strSQLtemp = strSQLtemp & " FROM Temp LEFT JOIN Quantity"
strSQLtemp = strSQLtemp & " ON Temp.POID = Quantity.POID"

Set rsTemp = db.OpenRecordset(strSQLtemp, dbOpenDynaset)
With rsTemp
If .RecordCount > 0 Then
.MoveFirst 'move to first record
Do Until .EOF
If ![Temp]![OrderDate] = [Quantity]![OrderDate] Then
.Edit
![Quantity]![Qty] = ![Temp]![Qty]
.Update

Else
Debug.Print ![Temp]![POID]
End If
.MoveNext
Loop
End If
.Close
End With
End Sub

Any help is greatly appreciated! thanks in advance!

 

[Douglas J. Steele]

Each field name in the recordset must be unique. Try aliasing the fields
using the As modifier:

strSQLtemp = "SELECT Temp.POID AS Temp_POID, Temp.OrderDate AS
Temp_OrderDate, Temp.Qty AS Temp_Qty, Quantity.Qty AS Quantity_Qty,
Quantity.OrderDate AS Quantity_OrderDate, Quantity.POID AS Quantity_POID"
strSQLtemp = strSQLtemp & " FROM Temp LEFT JOIN Quantity"
strSQLtemp = strSQLtemp & " ON Temp.POID = Quantity.POID"

then

Do Until .EOF
If ![Temp_OrderDate] = [Quantity_OrderDate] Then
.Edit
![Quantity_Qty] = ![Temp_Qty]
.Update

Else
Debug.Print ![Temp_POID]
End If
.MoveNext
Loop

 

[Samantha]

Thanks Douglas for the quick reply.

Now, I got a new error message, after your suggested changes:
Run-time error '3027'
Cannot update. Database or object is read-only.

I checked the properties of the database, and it is NOT read-only.
What is causing the error message?

thank you so much for your help.

Each field name in the recordset must be unique. Try aliasing the fields
using the As modifier:

strSQLtemp = "SELECT Temp.POID AS Temp_POID, Temp.OrderDate AS
Temp_OrderDate, Temp.Qty AS Temp_Qty, Quantity.Qty AS Quantity_Qty,
Quantity.OrderDate AS Quantity_OrderDate, Quantity.POID AS Quantity_POID"
strSQLtemp = strSQLtemp & " FROM Temp LEFT JOIN Quantity"
strSQLtemp = strSQLtemp & " ON Temp.POID = Quantity.POID"

then

Do Until .EOF
If ![Temp_OrderDate] = [Quantity_OrderDate] Then
.Edit
![Quantity_Qty] = ![Temp_Qty]
.Update

Else
Debug.Print ![Temp_POID]
End If
.MoveNext
Loop



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query that compares the OrderDate field from tables Temp and
Quantity. If they are the same, then I want to update the Qty field from
Temp table to Quantity table.
The problem is: I am getting a syntax error on referencing the two fields
(OrderDate) from both tables? Does anyone know what wrong with this code?

Private Sub Command0_Click()
Dim db As Database
Dim rsTemp As Recordset
Dim rsQuantity As Recordset
Dim strSQLqty As String
Dim strSQLtemp As String
Dim strPOID As String
Dim OrderDate As Date

Set db = CurrentDb

strSQLtemp = "SELECT Temp.POID, Temp.OrderDate, Temp.Qty, Quantity.Qty,
Quantity.OrderDate, Quantity.POID"
strSQLtemp = strSQLtemp & " FROM Temp LEFT JOIN Quantity"
strSQLtemp = strSQLtemp & " ON Temp.POID = Quantity.POID"

Set rsTemp = db.OpenRecordset(strSQLtemp, dbOpenDynaset)
With rsTemp
If .RecordCount > 0 Then
.MoveFirst 'move to first record
Do Until .EOF
If ![Temp]![OrderDate] = [Quantity]![OrderDate] Then
.Edit
![Quantity]![Qty] = ![Temp]![Qty]
.Update

Else
Debug.Print ![Temp]![POID]
End If
.MoveNext
Loop
End If
.Close
End With
End Sub

Any help is greatly appreciated! thanks in advance!

 

[Douglas J. Steele]

It would appear that queries that use a Left Join like that aren't
updatable. However, since you only want to update records that exist in
both, using an Inner Join should suffice.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas for the quick reply.

Now, I got a new error message, after your suggested changes:
Run-time error '3027'
Cannot update. Database or object is read-only.

I checked the properties of the database, and it is NOT read-only.
What is causing the error message?

thank you so much for your help.

Each field name in the recordset must be unique. Try aliasing the fields
using the As modifier:

strSQLtemp = "SELECT Temp.POID AS Temp_POID, Temp.OrderDate AS
Temp_OrderDate, Temp.Qty AS Temp_Qty, Quantity.Qty AS Quantity_Qty,
Quantity.OrderDate AS Quantity_OrderDate, Quantity.POID AS
Quantity_POID"
strSQLtemp = strSQLtemp & " FROM Temp LEFT JOIN Quantity"
strSQLtemp = strSQLtemp & " ON Temp.POID = Quantity.POID"

then

Do Until .EOF
If ![Temp_OrderDate] = [Quantity_OrderDate] Then
.Edit
![Quantity_Qty] = ![Temp_Qty]
.Update

Else
Debug.Print ![Temp_POID]
End If
.MoveNext
Loop



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query that compares the OrderDate field from tables Temp and
Quantity. If they are the same, then I want to update the Qty field
from
Temp table to Quantity table.
The problem is: I am getting a syntax error on referencing the two
fields
(OrderDate) from both tables? Does anyone know what wrong with this
code?

Private Sub Command0_Click()
Dim db As Database
Dim rsTemp As Recordset
Dim rsQuantity As Recordset
Dim strSQLqty As String
Dim strSQLtemp As String
Dim strPOID As String
Dim OrderDate As Date

Set db = CurrentDb

strSQLtemp = "SELECT Temp.POID, Temp.OrderDate, Temp.Qty, Quantity.Qty,
Quantity.OrderDate, Quantity.POID"
strSQLtemp = strSQLtemp & " FROM Temp LEFT JOIN Quantity"
strSQLtemp = strSQLtemp & " ON Temp.POID = Quantity.POID"

Set rsTemp = db.OpenRecordset(strSQLtemp, dbOpenDynaset)
With rsTemp
If .RecordCount > 0 Then
.MoveFirst 'move to first record
Do Until .EOF
If ![Temp]![OrderDate] = [Quantity]![OrderDate] Then
.Edit
![Quantity]![Qty] = ![Temp]![Qty]
.Update

Else
Debug.Print ![Temp]![POID]
End If
.MoveNext
Loop
End If
.Close
End With
End Sub

Any help is greatly appreciated! thanks in advance!