*.* FINDFIRST + ERROR 3464 (updating tables)

@

@ndy

Hello everybody,

i have transfered excel columns into an access table. Now the
transfered data must update the excisting data in other tables. For
example:
Imptable has the unique file_num and the Lines_num

example of Imported table:
File_Num Ligne_num Capital
457 25 100
26 80
27 360
478 45 4000
498 50 1254
51 452
and so on...

the excisting tables
tbl_File has the unique file_num
tbl_Line has the same file_num with one or more Lines_num with
capital, ...

NOW!

I'm writing a routine to update the rows. But i'm getting crazy, maybe
you can help me out. Here is the code

Dim dbs As Database
Dim rstDossiers As Recordset
Dim rstImpdata As Recordset
Dim rstLignes As Recordset
Dim strSeek, strSeek1, strVgl, strInr, strHulp As String
Dim blnFsearch As Boolean
Dim varBookmark As Variant

strVgl = "Dossier_Num = ''"
'MsgBox strVgl
Set dbs = CurrentDb
Set rstDossiers = dbs.OpenRecordset("tbl_Dossiers", dbOpenDynaset)
Set rstLignes = dbs.OpenRecordset("tbl_Lignes", dbOpenDynaset)
Set rstImpdata = dbs.OpenRecordset("ImpTable", dbOpenTable)

With rstImpdata

Do While Not rstImpdata.EOF

strSeek = rstImpdata![IFRS File number]
strSeek = "Dossier_Num = '" & strSeek & "'"
'If in the Imported table the row empty is then update the
lines
If strSeek = strVgl Then
'I'am in a blanco row in the imptable en need to update the
Line table
'update the Lines
With rstLignes
'must cut the file_num for searching the same file in
tbl_Line
If blnsearch = false Then
strInr = Right(strHulp, 14)
strSeek1 = "Dossier_id = " & strInr
'HERE COMES THE ERROR
.FindFirst strSeek1
Else
blnFsearch = True
.Edit
rstDossiers![Ligne_Intérêt_Réservé] =
rstImpdata![BGAAP / Intérets réservés]
.Update
.MoveNext
End If
End With
Else
strHulp = strSeek
With rstDossiers
.MoveLast
.FindFirst strSeek
If .NoMatch Then
MsgBox "No similar IFRS nr found in tbl_Dossier
for " & _
strSeek & "."
'logfile updaten dossiernr niet gevonden
Else
'Here we update the File_num
.Edit
rstDossiers![Dossier_BGAAP/CAP] =
rstImpdata![BGAAP / Capital]
rstDossiers![Dossier_Intérêts_Réservés] =
rstImpdata![BGAAP / Intérets réservés]
.Update
End If
.Close
End With
End If
.MoveNext
Loop
.Close
End With
dbs.Close

!!!If you know a better or faster way please let me know.
 
M

MacDermott

I think you're making life way too hard on yourself.
If you'll run through your file once, like this (pseudo code):
Open RST on ImpTbl
ThisFile=RST!File_num
do until RST.EOF
if rst!File_num="" (or if isnull(rst!File_num) - depends on what you've
really got in those "empty" records)
rst!File_num=ThisFile
Else
ThisFile=rst!File_num
endif
rst.movenext
loop

Now you've got a file_num on each line, and can run normal SQL Update
queries against them.

HTH

@ndy said:
Hello everybody,

i have transfered excel columns into an access table. Now the
transfered data must update the excisting data in other tables. For
example:
Imptable has the unique file_num and the Lines_num

example of Imported table:
File_Num Ligne_num Capital
457 25 100
26 80
27 360
478 45 4000
498 50 1254
51 452
and so on...

the excisting tables
tbl_File has the unique file_num
tbl_Line has the same file_num with one or more Lines_num with
capital, ...

NOW!

I'm writing a routine to update the rows. But i'm getting crazy, maybe
you can help me out. Here is the code

Dim dbs As Database
Dim rstDossiers As Recordset
Dim rstImpdata As Recordset
Dim rstLignes As Recordset
Dim strSeek, strSeek1, strVgl, strInr, strHulp As String
Dim blnFsearch As Boolean
Dim varBookmark As Variant

strVgl = "Dossier_Num = ''"
'MsgBox strVgl
Set dbs = CurrentDb
Set rstDossiers = dbs.OpenRecordset("tbl_Dossiers", dbOpenDynaset)
Set rstLignes = dbs.OpenRecordset("tbl_Lignes", dbOpenDynaset)
Set rstImpdata = dbs.OpenRecordset("ImpTable", dbOpenTable)

With rstImpdata

Do While Not rstImpdata.EOF

strSeek = rstImpdata![IFRS File number]
strSeek = "Dossier_Num = '" & strSeek & "'"
'If in the Imported table the row empty is then update the
lines
If strSeek = strVgl Then
'I'am in a blanco row in the imptable en need to update the
Line table
'update the Lines
With rstLignes
'must cut the file_num for searching the same file in
tbl_Line
If blnsearch = false Then
strInr = Right(strHulp, 14)
strSeek1 = "Dossier_id = " & strInr
'HERE COMES THE ERROR
.FindFirst strSeek1
Else
blnFsearch = True
.Edit
rstDossiers![Ligne_Intérêt_Réservé] =
rstImpdata![BGAAP / Intérets réservés]
.Update
.MoveNext
End If
End With
Else
strHulp = strSeek
With rstDossiers
.MoveLast
.FindFirst strSeek
If .NoMatch Then
MsgBox "No similar IFRS nr found in tbl_Dossier
for " & _
strSeek & "."
'logfile updaten dossiernr niet gevonden
Else
'Here we update the File_num
.Edit
rstDossiers![Dossier_BGAAP/CAP] =
rstImpdata![BGAAP / Capital]
rstDossiers![Dossier_Intérêts_Réservés] =
rstImpdata![BGAAP / Intérets réservés]
.Update
End If
.Close
End With
End If
.MoveNext
Loop
.Close
End With
dbs.Close

!!!If you know a better or faster way please let me know.
Click to expand...
 
Top