Updating records

[TheRiver]

I have a master table that I import data into each month. Once i have imported the data I change or add certain fields within the master table.

When I run the module that changes/adds data to the master table it edits all the existing data. I want it to just edit the newly added data, how can I go about doing this.

The existing code is as follows:

Option Compare Database 'Use database order for string comparisons
Option Explicit

Function wl_update_nonres1()

Dim wait As Database, opwl As Recordset
Dim dnadate As String
Set wait = DBEngine.Workspaces(0).Databases(0)
Set opwl = wait.OpenRecordset("OP WL&quot

opwl.MoveFirst
While Not opwl.EOF
opwl.Edit

If Not IsNull(opwl![LastDNAdate]) Then
dnadate = Right$(opwl![LastDNAdate], 2) & "/" & Mid$(opwl![LastDNAdate], 5, 2) & "/" & Mid$(opwl![LastDNAdate], 3, 2)
End If
If Not IsNull(opwl![LastDNAdate]) Then
opwl![U_DNA] = DateValue(dnadate)
End If

opwl![WAIT_DUR] = DateDiff("d", IIf(opwl![U_DNA] <= opwl![U_Census_Date], opwl![U_DNA], opwl![U_Ref_Date]), opwl![U_Census_Date])

Select Case opwl![WAIT_DUR]
Case 0 To 6: opwl![u_low] = 0
Case 7 To 13: opwl![u_low] = 1
Case 14 To 20: opwl![u_low] = 2
Case 21 To 27: opwl![u_low] = 3
Case 28 To 34: opwl![u_low] = 4
Case 35 To 41: opwl![u_low] = 5
Case 42 To 48: opwl![u_low] = 6
Case 49 To 55: opwl![u_low] = 7
Case 56 To 62: opwl![u_low] = 8
Case 63 To 69: opwl![u_low] = 9
Case 70 To 76: opwl![u_low] = 10
Case 77 To 83: opwl![u_low] = 11
Case 84 To 90: opwl![u_low] = 12
Case 91 To 97: opwl![u_low] = 13
Case 98 To 104: opwl![u_low] = 14
Case 105 To 111: opwl![u_low] = 15
Case 112 To 118: opwl![u_low] = 16
Case 119 To 125: opwl![u_low] = 17
Case 126 To 132: opwl![u_low] = 18
Case 133 To 139: opwl![u_low] = 19
Case 140 To 146: opwl![u_low] = 20
Case 147 To 153: opwl![u_low] = 21
Case 154 To 160: opwl![u_low] = 22
Case 161 To 167: opwl![u_low] = 23
Case 168 To 174: opwl![u_low] = 24
Case 175 To 181: opwl![u_low] = 25
Case Is >= 182: opwl![u_low] = 26
End Select

opwl.update
opwl.MoveNext



Wend


End Function

 

[Steve101]

Change the line:
[tt]
Set opwl = wait.OpenRecordset("OP WL&quot
[/tt]
to:
Set opwl = wait.OpenRecordset("SELECT * FROM [OP WL] " & _
"WHERE SomeExpression&quot
where SomeExpression represents a condition in the data which identifies only the newly added date; eg. a DateAdded timestamp.

You may have to build the Where clause of the SQL dynamically.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[TheRiver]

Thats great thanks.