Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Compare 2 tables and update field with a "change indicator" 1

Status
Not open for further replies.
jcw5107

jcw5107

Technical User
Jan 31, 2007
66
US
Below is code I messing with.
Works just fine....
What I need is a way to tell if the record is simply a "new" record, or if the current records was simply just "updated"
With the below piece of code... How can I get the field "ChangeIndicator" to be populated with an "A" for any new record that was added, an "U" for a any record that was updated/edited, and Null for any record that didn't change at all...??
Any examples or suggestions...?
Thanks in advance,
jcw5107


If rstShoes.NoMatch Then
'Add a new Record
rstShoes.AddNew
rstShoes!StockNumber = rstImport!StockNumber
rstShoes!ShoeName = rstImport!ShoeName
rstShoes!Color = rstImport!Color
rstShoes!Width = rstImport!Width
rstShoes!Gender = rstImport!Gender
rstShoes!Brand = rstImport!Brand
rstShoes!ChangeIndicator = ???????
rstShoes!CurrentDate = rstImport!CurrentDate
rstShoes!CurrentTime = rstImport!CurrentTime rstShoes.Update
Else

'Update Record
rstShoes.Edit
rstShoes!StockNumber = rstImport!StockNumber
rstShoes!ShoeName = rstImport!ShoeName
rstShoes!Color = rstImport!Color
rstShoes!Width = rstImport!Width
rstShoes!Gender = rstImport!Gender
rstShoes!Brand = rstImport!Brand
rstShoes!ChangeIndicator = ??????
rstShoes!CurrentDate = rstImport!CurrentDate
rstShoes!CurrentTime = rstImport!CurrentTime
rstShoes.Update
End If
 
Sort by date Sort by votes
Are you joking?

...
rstShoes!ChangeIndicator = "A"
rstShoes!CurrentDate = rstImport!CurrentDate
rstShoes!CurrentTime = rstImport!CurrentTime rstShoes.Update
Else

'Update Record
rstShoes.Edit
rstShoes!StockNumber = rstImport!StockNumber
rstShoes!ShoeName = rstImport!ShoeName
rstShoes!Color = rstImport!Color
rstShoes!Width = rstImport!Width
rstShoes!Gender = rstImport!Gender
rstShoes!Brand = rstImport!Brand
rstShoes!ChangeIndicator = "U"
...
 
Upvote 0 Downvote
But, I personally would rewrite code comme ça,

If rstShoes.NoMatch Then
rstShoes.AddNew
strEdit = "A"
Else
rstShoes.Edit
strEdit = "U"
End If


rstShoes!StockNumber = rstImport!StockNumber
rstShoes!ShoeName = rstImport!ShoeName
rstShoes!Color = rstImport!Color
rstShoes!Width = rstImport!Width
rstShoes!Gender = rstImport!Gender
rstShoes!Brand = rstImport!Brand
rstShoes!ChangeIndicator = strEdit rstShoes!CurrentDate = rstImport!CurrentDate
rstShoes!CurrentTime = rstImport!CurrentTime

rstShoes.Update
 
Upvote 0 Downvote
zion7,

What am I jokin' about..?? Are you jokin'...??!!
By doin' what you suggested, puts a "U" in the field "ChangeIndicator" for every record, even if it hasn't changed at all....

Don't fully understand your example/suggestion....
Break it down for me...!!!
jcw5107
 
Upvote 0 Downvote
Below is the complete code....
Again, What I'm tryin' to achieve is if a new record is added - then "ChangeIndicator" gets an "A", if a record is edited/updated - then "ChangeIndicator" gets an "U", and then if NO edits or NO updates are made - then "ChangeIndicator" would become Null.
What the code below does, is add an "U" to every record, unless its a new record, then it gets an "A". But for records that don't change, I would like for it to stay Null.
I know theres a way to make this work..!! Just got a 4th grade education when it comes to this stuff..!!!
Thanks for the help..!!

Public Function UpdateTable()
Dim dbs As DAO.Database
Dim rstSchdMxPlnMstr As DAO.Recordset
Dim rstNewSchdMxPln As DAO.Recordset


Set dbs = CurrentDb
Set rstNewSchdMxPln = dbs.OpenRecordset("SchdMaintNew", dbOpenDynaset)
Set rstSchdMxPlnMstr = dbs.OpenRecordset("SchdMxPlanMstr", dbOpenDynaset)

rstNewSchdMxPln.MoveFirst
Do Until rstNewSchdMxPln.EOF
rstSchdMxPlnMstr.FindFirst "[WanNo] = " & "" & rstNewSchdMxPln!WanNo & ""
If rstSchdMxPlnMstr.NoMatch Then
'Add a new Record
rstSchdMxPlnMstr.AddNew
rstSchdMxPlnMstr!SchdDate = rstNewSchdMxPln!SchdDate
rstSchdMxPlnMstr!Aircraft = rstNewSchdMxPln!TAIL
rstSchdMxPlnMstr!AMPDocNo = rstNewSchdMxPln!DocNo
rstSchdMxPlnMstr!DocDescription = rstNewSchdMxPln!DocDescription
rstSchdMxPlnMstr!Location = Right(rstNewSchdMxPln!Loc, 3)
rstSchdMxPlnMstr!WanNo = rstNewSchdMxPln!WanNo
rstSchdMxPlnMstr!Priority = rstNewSchdMxPln!Priority
rstSchdMxPlnMstr!EstHrs = rstNewSchdMxPln!EstHrs
rstSchdMxPlnMstr!Void = rstNewSchdMxPln!Void
rstSchdMxPlnMstr!PlanStatus = "A" 'New record added
rstSchdMxPlnMstr.Update
Else
'Update Record
rstSchdMxPlnMstr.Edit
rstSchdMxPlnMstr!SchdDate = rstNewSchdMxPln!SchdDate
rstSchdMxPlnMstr!Aircraft = rstNewSchdMxPln!TAIL
rstSchdMxPlnMstr!AMPDocNo = rstNewSchdMxPln!DocNo
rstSchdMxPlnMstr!DocDescription = rstNewSchdMxPln!DocDescription
rstSchdMxPlnMstr!Location = Right(rstNewSchdMxPln!Loc, 3)
rstSchdMxPlnMstr!WanNo = rstNewSchdMxPln!WanNo
rstSchdMxPlnMstr!Priority = rstNewSchdMxPln!Priority
rstSchdMxPlnMstr!EstHrs = rstNewSchdMxPln!EstHrs
rstSchdMxPlnMstr!Void = rstNewSchdMxPln!Void
rstSchdMxPlnMstr!PlanStatus = "U" 'Record updated
rstSchdMxPlnMstr.Update
End If
rstNewSchdMxPln.MoveNext
Loop
rstNewSchdMxPln.Close
rstSchdMxPlnMstr.Close
Set dbs = Nothing
DisplayMessage "Data is done updating/refreshing"

End Function
 
Upvote 0 Downvote
Any help out there..???
Just checkin' in to see if anybody has a fix...??
jcw5107
 
Upvote 0 Downvote
You wanted this ?
...
Else
'Update Record
rstSchdMxPlnMstr.Edit
rstSchdMxPlnMstr!SchdDate = rstNewSchdMxPln!SchdDate
rstSchdMxPlnMstr!Aircraft = rstNewSchdMxPln!TAIL
rstSchdMxPlnMstr!AMPDocNo = rstNewSchdMxPln!DocNo
rstSchdMxPlnMstr!DocDescription = rstNewSchdMxPln!DocDescription
rstSchdMxPlnMstr!Location = Right(rstNewSchdMxPln!Loc, 3)
rstSchdMxPlnMstr!WanNo = rstNewSchdMxPln!WanNo
rstSchdMxPlnMstr!Priority = rstNewSchdMxPln!Priority
rstSchdMxPlnMstr!EstHrs = rstNewSchdMxPln!EstHrs
rstSchdMxPlnMstr!Void = rstNewSchdMxPln!Void
If rstSchdMxPlnMstr!SchdDate <> rstNewSchdMxPln!SchdDate _
Or rstSchdMxPlnMstr!Aircraft <> rstNewSchdMxPln!TAIL _
Or rstSchdMxPlnMstr!AMPDocNo <> rstNewSchdMxPln!DocNo _
Or rstSchdMxPlnMstr!DocDescription <> rstNewSchdMxPln!DocDescription _
Or rstSchdMxPlnMstr!Location <> Right(rstNewSchdMxPln!Loc, 3) _
Or rstSchdMxPlnMstr!WanNo <> rstNewSchdMxPln!WanNo _
Or rstSchdMxPlnMstr!Priority <> rstNewSchdMxPln!Priority _
Or rstSchdMxPlnMstr!EstHrs <> rstNewSchdMxPln!EstHrs _
Or rstSchdMxPlnMstr!Void <> rstNewSchdMxPln!Void Then
rstSchdMxPlnMstr!PlanStatus = "U" 'Record updated
Else
rstSchdMxPlnMstr!PlanStatus = Null
End If
rstSchdMxPlnMstr.Update
End If
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Sorry jcw, I think I get your question now,
(oh, the irony of sarcasm).

...and apparently, so does PHV.
 
Upvote 0 Downvote
PHV,

Awesome...!!! I ended up doing something a little different, but none the less - you were able to get me rockin' & rollin'..!!
Below is what I am not working with...
Is there a way to delete the record from rstSchdMxplnMstr when a "WanNo" isn't found
in rstNewSchdMxPln...??
Thanks for the help..!!
Star for ya..!!
jcw5107

Else
'Update Record
rstSchdMxPlnMstr.Edit
If rstSchdMxPlnMstr!SchdDate <> rstNewSchdMxPln!SchdDate _
Or rstSchdMxPlnMstr!Aircraft <> rstNewSchdMxPln!TAIL _
Or rstSchdMxPlnMstr!AMPDocNo <> rstNewSchdMxPln!DocNo _
Or rstSchdMxPlnMstr!DocDescription <> rstNewSchdMxPln!DocDescription _
Or rstSchdMxPlnMstr!Location <> Right(rstNewSchdMxPln!Loc, 3) _
Or rstSchdMxPlnMstr!WanNo <> rstNewSchdMxPln!WanNo _
Or rstSchdMxPlnMstr!Priority <> rstNewSchdMxPln!Priority _
Or rstSchdMxPlnMstr!EstHrs <> rstNewSchdMxPln!EstHrs _
Or rstSchdMxPlnMstr!Void <> rstNewSchdMxPln!Void Then
rstSchdMxPlnMstr!PlanStatus = "U" 'Record updated
rstSchdMxPlnMstr!SchdDate = rstNewSchdMxPln!SchdDate
rstSchdMxPlnMstr!Aircraft = rstNewSchdMxPln!TAIL
rstSchdMxPlnMstr!AMPDocNo = rstNewSchdMxPln!DocNo
rstSchdMxPlnMstr!DocDescription = rstNewSchdMxPln!DocDescription
rstSchdMxPlnMstr!Location = Right(rstNewSchdMxPln!Loc, 3)
rstSchdMxPlnMstr!WanNo = rstNewSchdMxPln!WanNo
rstSchdMxPlnMstr!Priority = rstNewSchdMxPln!Priority
rstSchdMxPlnMstr!EstHrs = rstNewSchdMxPln!EstHrs
rstSchdMxPlnMstr!Void = rstNewSchdMxPln!Void
Else
rstSchdMxPlnMstr!PlanStatus = Null
End If
rstSchdMxPlnMstr.Update
End If
rstNewSchdMxPln.MoveNext
Loop
 
Upvote 0 Downvote

....
Do Until rstNewSchdMxPln.EOF

If IsNull(rstNewSchdMxPln!WanNo) Then
rstNewSchdMxPln.Delete
Else

rstSchdMxPlnMstr.FindFirst "[WanNo] = " & "" & rstNewSchdMxPln!WanNo & ""
If rstSchdMxPlnMstr.NoMatch Then
'Add a new Record
rstSchdMxPlnMstr.AddNew
rstSchdMxPlnMstr!SchdDate = rstNewSchdMxPln!SchdDate
....
rstSchdMxPlnMstr!PlanStatus = "U" 'Record updated
rstSchdMxPlnMstr.Update
End If

End If

rstNewSchdMxPln.MoveNext
Loop
 
Upvote 0 Downvote
Zion7,
I tried your example (or different variations of it), and I'm strikin' out...
The field "WanNo" is a primary key field and cannot not contain a Null value.
What I'm tryin' to do is delete the record(s) from rstSchdMxPlnMstr when a WanNo is NOT in rstNewSchdMxPln..

Thanks for the help..!!
jcw5107
 
Upvote 0 Downvote
Again jcw, I misunderstood.

Maybe this. (don't let my personal formatting, freak you out)...

Code: 
Public Function UpdateTable()
    Dim dbs As DAO.Database
    Dim rstSchdMxPlnMstr As DAO.Recordset
    Dim rstNewSchdMxPln As DAO.Recordset
    Dim varPlanStatus As Variant
    Dim blnDelete As Boolean
   
    Set dbs = CurrentDb
    Set rstNewSchdMxPln = dbs.OpenRecordset("SchdMaintNew", dbOpenDynaset)
    Set rstSchdMxPlnMstr = dbs.OpenRecordset("SchdMxPlanMstr", dbOpenDynaset)
    
    With rstNewSchdMxPln

    .MoveFirst
    
    Do Until .EOF
        
        rstSchdMxPlnMstr.FindFirst "[WanNo] = " & "" & !WanNo & ""
            
            If rstSchdMxPlnMstr.NoMatch Then
                'Add a new Record
                rstSchdMxPlnMstr.AddNew
                varPlanStatus = "A"
            Else
                If rstSchdMxPlnMstr!WanNo <> !WanNo Then
                    blnDelete = True
                Else
                    'Update Record
                     blnDelete = False
                    rstSchdMxPlnMstr.edit
                        If rstSchdMxPlnMstr!SchdDate <> !SchdDate _
                            Or rstSchdMxPlnMstr!Aircraft <> !TAIL _
                                Or rstSchdMxPlnMstr!AMPDocNo <> !DocNo _
                                    Or rstSchdMxPlnMstr!DocDescription <> !DocDescription _
                                        Or rstSchdMxPlnMstr!Location <> Right(!Loc, 3) _
                                            Or rstSchdMxPlnMstr!WanNo <> !WanNo _
                                                Or rstSchdMxPlnMstr!Priority <> !Priority _
                                                    Or rstSchdMxPlnMstr!EstHrs <> !EstHrs _
                                                        Or rstSchdMxPlnMstr!Void <> !Void Then
                            varPlanStatus = "U" 'Record updated
                        Else
                            varPlanStatus = Null
                        End If
                End If
            End If
            
    If blnDelete Then
        rstSchdMxPlnMstr.Delete
    Else
        rstSchdMxPlnMstr!SchdDate = !SchdDate
        rstSchdMxPlnMstr!Aircraft = !TAIL
        rstSchdMxPlnMstr!AMPDocNo = !DocNo
        rstSchdMxPlnMstr!DocDescription = !DocDescription
        rstSchdMxPlnMstr!Location = Right(!Loc, 3)
        rstSchdMxPlnMstr!WanNo = !WanNo
        rstSchdMxPlnMstr!Priority = !Priority
        rstSchdMxPlnMstr!EstHrs = !EstHrs
        rstSchdMxPlnMstr!Void = !Void
        rstSchdMxPlnMstr!PlanStatus = varPlanStatus
        rstSchdMxPlnMstr.Update
    End If
    
    End With
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Dan Stoica
  • Locked
  • Question
Problems with Foxpro dsnless linked tables
Replies
0
Views
363
Dan Stoica
Dan Stoica
M
  • Locked
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
2K
SoftwareRT
SoftwareRT
Moss100
  • Locked
  • Question
Access prompts &quot;open or save&quot; when I preview an excel file 1
Replies
1
Views
238
combo
combo
aldi07
  • Locked
  • Question
update a front-end database with a new release using vba
Replies
5
Views
299
aldi07
aldi07
Moss100
  • Locked
  • Question
Open filtered for and set cursor at particular record
Replies
1
Views
438
Moss100
Moss100

Part and Inventory Search

Sponsor

Back
Top