INSERT INTO inserts a wrong record in Access 97

S

SusannaW

Hi Newsgroup

I have a problem with an INSERT INTO Statement:

In a recordset the actual record should be inserted into another
table. I use the insert into statement with a WHERE (refers to a
unique field) in the statement. The insert works (no error messages),
but it inserts a completly wrong record - only the referred field is
correct. When I delete the wrong one and run the code again, the
insert works properly.

The insert into should work from table to table, no forms, queries or
whatever are involved - just pure VBA-Code...

Any help would be appreciated!

Thanks in advance

Susanna

Dim DB As Database, myTable1 As Recordset, strSQL As String
Dim strConnrn As String, loAnzahl As Long
Set DB = CurrentDb
Set myTable1 = DB.OpenRecordset("new_consignments", DB_OPEN_DYNASET)
loAnzahl = myTable1.RecordCount
If loAnzahl > 0 Then
myTable1.MoveFirst
Do Until myTable1.EOF
strConnrn = myTable1!connrn
If Left(strConnrn, 1) = 0 Then
myTable1!connrn = Right(strConnrn, Len(strConnrn) - 1)
strConnrn = myTable1!connrn
End If
If Not IsNull(myTable1!rname) And myTable1!rname <> "" And _
Not IsNull(myTable1!radr2) And myTable1!radr2 <> "" And _
Not IsNull(myTable1!rpostal) And myTable1!rpostal <> "" And
_
Not IsNull(myTable1!rcity) And myTable1!rcity <> "" And _
Not IsNull(myTable1!rcountry) And myTable1!rcountry <> ""
And _
Not IsNull(myTable1!inv) And myTable1!inv <> 0 And _
Not IsNull(myTable1!curr) And myTable1!curr <> "" And _
Not IsNull(myTable1!desc1) And myTable1!desc1 <> "" And _
Not IsNull(myTable1!no1) And myTable1!no1 <> 0 And _
Not IsNull(myTable1!wg1) And myTable1!wg1 <> 0 And _
Not IsNull(myTable1!serv) And myTable1!serv <> "" Then
If myTable1!rcountry <> "SWITZERLAND" And _
myTable1!dcountry = "SWITZERLAND" Then
Else
strSQL = "INSERT INTO consignments SELECT
new_Consignments.* FROM new_consignments WHERE new_Consignments.connrn
= " & strConnrn
DB.Execute strSQL
Call Print_from_Automation(myTable1!totalno,
myTable1!serv, myTable1!connrn)
myTable1.Delete
End If
End If
myTable1.MoveNext
Loop
End If
myTable1.Close
 
Top