troubleshooting Question

Variables in an Update query

Avatar of davecocks
davecocks asked on
Microsoft AccessVisual Basic ClassicSQL
12 Comments2 Solutions343 ViewsLast Modified:
I've adapted this piece of code below, I think its working, but the NewID and OldID strings are not operating in the update query SQL i.e.  Access comes up with 'enter parameter values' for both  NewID and OldID

Any ideas? I've experimented with a few & and " in places but can't get it to work
Public Sub XXXCheckFieldsXXX()
 
 
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer, j As Integer, fldArr()
 
Set rs = CurrentDb.OpenRecordset("Determinants")  ' change the name of the source table
Set rs1 = CurrentDb.OpenRecordset("Data_Intermediate") 'change name of destination table
 
    Dim NewID As String
    Dim OldID As String
 
  Dim strDataUpdate As String
    strDataUpdate = "UPDATE Data_Intermediate " & _
                    "SET DeterminantID = NewID " & _
                    "WHERE DeterminantID = OldID;"
            
If rs.EOF Or rs.BOF Then
    MsgBox "no records"
    Exit Sub
End If
 
'For each Data_intermediate value
Do While Not rs1.EOF
OldID = rs1.Fields("DeterminantID").Value
    'For each Determinant tbl value
    Do While Not rs.EOF
        NewID = rs.Fields("DeterminantID").Value
        If rs1.Fields("DeterminantID").Value = rs.Fields("EA Code").Value Then
        'rs1.Fields("DeterminantID").Value = rs.Fields("DeterminantID").Value
        DoCmd.RunSQL strDataUpdate
        rs.MoveNext
        Else
        rs.MoveNext
       End If
    Loop
rs1.MoveNext
Loop
 
   
End Sub
ASKER CERTIFIED SOLUTION
Jez Walters
Business Data Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros