Link to home
Start Free TrialLog in
Avatar of davecocks
davecocks

asked on

Variables in an Update query

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

Open in new window

SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The code structure you have doesn't ever move back to the first record of rs1 (which reads from your Data_Intermediate table).

This means that the update will only be performed for the FIRST record of your Determinats table!
Is there any particular reason you're not just using a single UPDATE query (with a JOIN)?

Please don't take offense, but your approach (as well as the design flaw I've just highlighted) isn't very efficient.
I'm still trying to get my head round what you're trying to achieve, but won't this do what you want?


Public Sub XXXCheckFieldsXXX()
    DoCmd.RunSQL "UPDATE Data_Intermediate INNER JOIN Determinants " & _
                 "ON Determinants.DeterminantID = Data_Intermediate.DeterminantID " & _
                 "SET Data_Intermediate.DeterminantID = Determinants.DeterminantID " & _
                 "WHERE Determinants.[EA Code] = Data_Intermediate.DeterminantID"
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of davecocks
davecocks

ASKER

Hi Jez no offense taken, its good to know where my design is going wrong. Thanks for the second update on the correction of the code!!! It worked brilliantly.

Hi matthewspatrick, I think your solution worked for my original question, but it was my code that wasn't upto scratch - thanks for the numeric and text difference. And apologies as Jez's adaptation fixed my code so had to have most of the points.

Thanks guys!
If you want to go the record set route, you should have something like this:
Public Sub XXXCheckFieldsXXX()
    Dim rstDeterminants As Recordset
    Dim rstDataIntermediate As Recordset
 
    Set rstDeterminants = CurrentDb.OpenRecordset("Determinants", dbOpenSnapshot, dbReadOnly)  ' Change name of source table
    If rstDeterminants.EOF Then
        MsgBox "No records!"
        Exit Sub
    End If
    Set rstDataIntermediate = CurrentDb.OpenRecordset("Data_Intermediate", dbOpenTable)        ' Change name of destination table
 
    ' For each Data_Intermediate record
    Do Until rstDataIntermediate.EOF
 
        ' For each Determinants record
        rstDeterminants.MoveFirst
        Do Until rstDeterminants.EOF
            If rstDeterminants![EA Code] = rstDataIntermediate!DeterminantID Then
                rstDataIntermediate.Edit
                rstDataIntermediate!DeterminantID = rstDeterminants!DeterminantID
                rstDataIntermediate.Update
            End If
            rstDeterminants.MoveNext
        Loop
        rstDataIntermediate.MoveNext
    Loop
End Sub

Open in new window

The problem with the code in your question (apart from it not working!) is that you've got the overhead of compiling an SQL statement for EACH combination of records in your Determinants and Data_Intermediate tables.

Since you're looping through the Data_Intermediate table records anyway, it makes sense to edit each record as you go by (so to speak).
Once you add in the missing MoveFirst invocation (see ID 21324051 above), you can see that even the corrected record set strategy is rather inefficient, since it repeatedly goes back to the first record of the Determinants table for each record of the Data_Intermediate table.

By paring down the entire algorithm so a single UPDATE query Access only has to read each table once, which not only makes the whole process much faster but also gives you less code to maintain.

Less is more, as they say!  :-)
Just for future reference, you might like to bear in mind the other little tweaks I've made as well:

1. Removed unused variables
2. Reordered code to ONLY open Determinants record set when no records
3. Opened record sets as read-only where appropriate
4. Removed repeated MoveNext code
5. Relaced variables with direct record set field references
6. Used more consise field referencing

Hope that helps explain what I changed!  :-)
Jez that is fantastic!!

Thanks for taking both the time to correct my code and explain it. I've been punching above my weight with VBA code trying to learn it so this really helps a great deal.

Its all much appreciated, keep up the good work!!
Have a knockout time!  :-)