SQL Joins Problemo

[samonwalkabout]

SQL
I have his code running in the database no worries, but I want to Change it so it takes the "adjustment" value from a qry not from the table "newstock" As im having to update this before i run the code and it causing trouble. The code reads...

Dim rst As Recordset
Dim dt As Date
Dim lngAmnt As Long
Dim db As DAO.Database
Dim strSQL As String, strSQL2 As String
Dim rstProChem As Recordset
Dim intChemicalID As Integer, intProcessID As Integer

Set db = CurrentDb

intProcessID = [Forms]![update]![cmbProcess]

strSQL2 = "SELECT prochem.[Chemical ID], Process.[Process ID] " _
& "FROM Process INNER JOIN prochem ON Process.[Process ID] = prochem.[Process ID] " _
& "GROUP BY prochem.[Chemical ID], Process.[Process ID] " _
& "HAVING (((Process.[Process ID])=" & intProcessID & "));"

Set rstProChem = db.OpenRecordset(strSQL2, dbOpenDynaset)

With rstProChem
.MoveFirst
Do Until .EOF

intChemicalID = ![Chemical ID]

strSQL = "SELECT [Chemical ID], [Week End Date],Startamt, endamt, adjustment " _
& "FROM newstock " _
& "WHERE [Chemical ID]=" & intChemicalID & ";"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

lngAmnt = rst!Startamt

With rst
.MoveFirst

Do Until .EOF
.Edit
!Startamt = lngAmnt
!endamt = !Startamt + !adjustment
'!Adjustment = !Adjustment
.update
lngAmnt = !endamt
.MoveNext
Loop
End With
.MoveNext
Loop
End With

End Sub

I have a qry called "Adjustment" with [Chemical ID], [Week End Date] and [Adjustment] but cant seem to get the code right to slot it in. I think its my poor grasp of sql joins. Anyone good at em??

 

[pdx_man]

Depending on how you have the Adjustment query set up, joining it to the newstock query may yield a recordset that is not updateable. So, we'll try two things. First:

strSQL = "SELECT newstock.[Chemical ID], newstock.[Week End Date],newstock.Startamt, newstock.endamt, Qry_Adjustment.adjustment " _
& "FROM newstock INNER JOIN Qry_Adjustment ON newstock.[Chemical ID] = Qry_Adjustment.[Chemical ID] " _
& "WHERE [Chemical ID]=" & intChemicalID
When you're using code you do not need the ending ;


Another way that I'm thinking holds a better chance of working is keeping what you have, but changing the !adjustment to:
DLookup("Adjustment","Qry_Adjustment","[Chemical ID]=" & intChemicalID & " And [Week End Date] = #" rst![Week End Date] & "#")

Of course, you could also just look at doing an UPDATE query ...

Notice I changed the name of the reference to your query. My advice ... adopt a naming convention. Preface tables with Tbl, Queries with Qry ... Never use spaces, use underscores ... There are many references our on the Internet and many veteran users here will agree ... start using naming conventions and be consistant. As you see now, you have a query named Adjustment containing a field called Adjustment. Access is a great program, but it does get confused ... as will you.

 

[samonwalkabout]

Thanks, i always start out meaning to use naming conventions but end up with things in a state, i will have to be more careful in the future.

Why would the record set become non updateable? I have had this a few times when tring to join the table and the qry, can you explain why?

Also im not sure how the Dlook-up will work and where? (in the final bit of code after edit?

Thanks