Identifying new data

[manicamaniac]

Hello,

So, inside my db, there are 2 tables, "T_Data" and "T_Analyst". The first one, is the result of imported data. The second contains data that are used to compare with the first one. At the T_Analyst, there is a option button, named as FlagNew. The idea is that, when someone imports new data, this code identifies data that are not listed at T_Analyst and then, Flag showing to the user that there's new data to be updated.

But, I couldn't do it. If someone here knows how to solve it, please help me.
Thanks:



Code:
Function ImportDATA()
On Error GoTo ERR_ImportINFO

Dim DB As Database
Dim rst As Recordset
Dim Acc199 As Recordset
Dim sSQL As String
Dim nLin As Long
Dim sLinha As String
Dim nRec As Long
Dim txtFile As String
Dim FlagNew As Integer


'Para importar corretamente, salve um arquivo nomeado DATA_AGING, no formato .txt e no destino a seguir:
txtFile = "H:\Data\DATA_AGING.txt"
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM T_Data;"

DoCmd.SetWarnings True

Set DB = CurrentDb()
Set rst = DB.OpenRecordset("T_Data")
Set Acc199 = DB.OpenRecordset("T_Analyst")
FlagNew = False

Close #1
Open txtFile For Input As #1
nLin = 0
nRec = 0
Do While Not EOF(1)
Line Input #1, sLinha
nLin = nLin + 1
DoCmd.Echo True, "Processing file " & txtFile & " Line " & Str(nLin) & "..."

rst.AddNew
rst("Company") = Val((Mid(sLinha, 1, 4)))
rst("Account") = Val(Mid(sLinha, 11, 9))
rst("Aging") = Mid(sLinha, 26, 13)
rst("Period") = Mid(sLinha, 41, 2)
rst("Number of Items") = Val(Mid(sLinha, 46, 4))
rst("Value") = Mid(sLinha, 56, 14)
rst.Update
nRec = nRec + 1

Loop
Close #1
rst.Close
DB.Close

Dim strCriteria As String
strCriteria = "[Company] = " & Company & " AND [Account] = " & Account
'Acc199.Seek "=", Company, Account
Acc199.FindFirst strCriteria
Do While EOF(2)
If Acc199.NoMatch Then
With Acc199
.AddNew
!FlagNew = True
!Company = Company
!Account = Account
!Aging = "-"
!Period = "-"
!Group1 = "-"
!Group2 = "-"
!Group3 = "-"
FlagNew = True
.Update
End With
End If

Line Input #1, sLinha
nLin = nLin + 1
Loop
Beep
Dim Over As String
Dim Warn As String

Over = DCount("[Aging]", "T_Data", "[Aging] = 'Over360'")
Warn = "New data loaded. The number of items over 360 days is " + [Over]

If FlagNew = Yes Then
MsgBox Warn
MsgBox "New accouts were encountered! Check Accounts Table to fill in their information."

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "F_UpdateAccts"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox Warn
End If


Exit_ImportINFO:
Exit Function

ERR_ImportINFO:
If Err.Number = 3012 Then
Resume Next
Else
DoCmd.Hourglass False
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ImportINFO
End If

End Function

[Rawb]

What data type is the FlagNew field? Is it a Yes/No field or something else?

If it's Yes/No, then I'm not sure why it's not working. In fact, we do something similar where I work and I've never had a problem with it.

[manicamaniac]

It's a Yes/No box. I have no idea why it's not workink properly.

F_UpdateAccts is a form that contains the T_Analyst data, whick aims to update new data that this code should identify..

[Rawb]

Found it!

You've set up a variable called FlagNew that you're assigning True/False to. You need to have a field in the table that you assign as True/False and then just have your Box checked if that field is True and blank if it's False.

[manicamaniac]

There's already a True/False field in T_Analyst..

[Rawb]

Well then, all you need to do - assuming the Yes/No field is called "FlagNew" - is to put an exclamation point in front of the line "FlagNew = True" like the following:

  Dim strCriteria As String

  strCriteria = "[Company] = " & Company & " AND [Account] = " & Account

  Acc199.Seek "=", Company, Account
  Acc199.FindFirst strCriteria

  Do While EOF(2)
    If Acc199.NoMatch Then
      With Acc199
        .AddNew
        !FlagNew = True
        !Company = Company
        !Account = Account
        !Aging = "-"
        !Period = "-"
        !Group1 = "-"
        !Group2 = "-"
        !Group3 = "-"
        !FlagNew = True
        .Update
       End With
     End If

     Line Input #1, sLinha
     nLin = nLin + 1
   Loop

   Beep

   Dim Over As String
   Dim Warn As String

   Over = DCount("[Aging]", "T_Data", "[Aging] = 'Over360'")
   Warn = "New data loaded. The number of items over 360 days is " + [Over]

   If FlagNew = Yes Then
     MsgBox Warn
     MsgBox "New accouts were encountered! Check Accounts Table to fill in their information."

     Dim stDocName As String
     Dim stLinkCriteria As String

     stDocName = "F_UpdateAccts"

     DoCmd.OpenForm stDocName, , , stLinkCriteria
   Else
     MsgBox Warn
   End If