update record

[chriske911]

maybe my previous post was too difficult to understand

I use a select case to add records to several tables by sequential file import
(open file)

only when a certain condition is true a new record is added to the 'primary'
table from inside the loop
all other conditions update the same record or records of related tables

how can I keep updating the same record of the 'primary' table while looping
thru the select case?
now the loop jumps to another record when I use rs.edit and rs.update

all recordset are obviously defined outside the loop and closed at the end
of the module

thnx

 

[Stefan Hoffmann]

hi Chris,

all recordset are obviously defined outside the loop and closed at the
end of the module

Post the code you have so far.


mfG
--> stefan <--

 

[chriske911]

Hello Stefan,

just a snip of the code,
all variables are declared and all recordsets are defined within the module
BLnumber is the join field between tables

Do While Not EOF(1)
Line Input #1, Filedata
'Debug.Print Filedata
Header = Mid(Filedata, 1, 3)
Select Case Header
Case "B01"
'New BL always starts with B01
With rsManifest
.AddNew
.Fields("Voyage").Value = VoyageNbr
BLnumber = Mid(Filedata, 4, 15)
.Fields("BL").Value = BLnumber
.Fields("Article").Value = Mid(Filedata, 79, 4)
End With
nbrBLImport = nbrBLImport + 1
Case "S01"
rsManifest.Fields("Shipper").Value = Mid(Filedata, 4, 32)
Case "U01"
rsManifest.Fields("Consignee").Value = Mid(Filedata, 4, 32)
Case "N01"
rsManifest.Fields("[Notify 1]").Value = Mid(Filedata, 4, 32)
rsManifest.Update
Case "C01"
' EDI Containerrecord
ContainerNumber = Mid(Filedata, 4, 14)
With rsContainers
.AddNew
.Fields("BL") = BLnumber
.Fields("Seal") = Mid(Filedata, 18, 15)
.Update
End With
Case "D01"
' Update goods description
With rsCargoDescription
.AddNew
.Fields("BL") = BLnumber
.Fields("Container") = ContainerNumber
.Update
End With
Case Else
' do nothing for now, a lot of headers are left alone
End Select
Loop

 

[Stefan Hoffmann]

hi Chris,

just a snip of the code, all variables are declared and all recordsets
are defined within the module
BLnumber is the join field between tables

[/QUOTE]
Is see. You need to address the records, which you like to update. You
either need a FindFirst or as I allways do:

db.Execute "INSERT INTO table ()..."
db.Execute "UPDATE table SET ... WHERE id = .."


mfG
--> stefan <--

 

[chriske911]

Hello Stefan,

that I can do
that way I am always sure I am editing the correct record
but as it is now I seem to be updating the same record just until the code
says "rs.update"
this is way shorter and maybe thus faster than going thru the recordset and
select the correct one to edit

the trouble is that this recordset is not yet updated
and when I edit a linked record in another table it gives me the error that
the linked field is missing in the joined table
is there any code to update the parent table without leaving or closing the
edited record?

grtz

hi Chris,

just a snip of the code, all variables are declared and all
recordsets
are defined within the module
BLnumber is the join field between tables

[/QUOTE]
Is see. You need to address the records, which you like to update. You
either need a FindFirst or as I allways do:

db.Execute "INSERT INTO table ()..."
db.Execute "UPDATE table SET ... WHERE id = .."
mfG
--> stefan <--[/QUOTE]