Data Import Programming and data distribution

[MrDummy]

Hello,



I am pretty far now and taking the last steps. In the attachement I have put a sample DB with my issue. Problem is that after distributing the data the ID in the last table is not correct is not correct.

Trust the db will give you some explanation.

[MrDummy]

After reading my question again, it may need some additional info.

If you start with clean tables (except tblImEx) then after clicking the button on form1, the tblVoyageData is added the V-record of tblImEx with VoyageDataID=1. The C-records are added to tblContainerData with ContainerDataID=1 and ContainerDataID=2. Both have the same VoyageDataID (=1). Both are related to the same Voyage. This is going OK even if you click the button several times.
Both records from the tblContainerData must be related to resp 3 and 4 U-records in tblUNComplete. Which records from the U-records related to which ContainerDataID can be found in tblImEx field ImEx1 where you can find a 1 or a 2.

I was thinking for some kind of loop: You select one C-records (from tblImEx) find the related U records (in tblImEx) and add these to the tblContainerData and tblUNComplete.

Hope this clarifies it a little bit.

[MrDummy]

I am moving ahead. I have inserted a loop and it does go at least once. But it should run for every C record in tblImEx. Somebody has a suggestion:

With Rst


Rst.MoveFirst
Do Until Rst.EOF

If Rst.Fields("ImEx0").Value = "C" Then

lng2ID = Rst.Fields("ImEx3").Value
lng3ID = Rst.Fields("ImEx4").Value

If Rst1.Fields("ContainerNumber").Value = lng3ID And Rst1.Fields("BookingRef") = lng2ID Then

CdID = Rst1.Fields("ContainerDataID").Value

strSql2 = "INSERT INTO [tblUNComplete] (ContainerDataID, VoyageDataID, UNComplLetter, UNVar, TechnicalName, SuppPSNID, AddSegGrp, LTDQTY ) " & _
"SELECT " & CdID & " As NewID, ImEx2, ImEx0, ImEx3, ImEx4, ImEx5, ImEx6, ImEx7 " & _
"FROM [tblImEx] WHERE ImEx1 = " & "'" & lng3ID & "'" & ";" 
DoCmd.RunSQL strSql2
End If
End If


Rst.MoveNext

Loop

End With

[orange]

Somewhere in your set up you have referenced a table called tblAddInfo, and it isn't in your database.
I don't use subdatasheets, so am not sure what you have done, are trying to do, or have left some remnants of old code???

You have defined 3 sql strings to modify your Container, voyage and Uncomplete tables.
Since you only have 1 import layout, you could set up a loop to

- Read a record from ImEx
- select case ImEx0
Case "V" USE SQLVoyage
Case "C" Use SQLContainer
Case "U" Use SQLUnComplete
-Loop


Good luck.