C
Chriske911
can anyone give me a working example of an export of related tables
for now I use the following code:
Function CreateFCPSfile(Voyage As String, UVInbr As String,
ExportFolder As String, Exportname As String) As String
' create export file for customs based onn the imported data
Dim qdfManifest As DAO.QueryDef
Dim SearchBL As String
Set qdfManifest = CurrentDb.QueryDefs("qryfcps_manif")
qdfManifest.Parameters![VOY] = Voyage
qdfManifest.Parameters![POD] = "LIV"
Set rsManifest = qdfManifest.OpenRecordset
Set rsContainers = CurrentDb.OpenRecordset("tblContainers",
dbOpenDynaset)
Set rsGoods = CurrentDb.OpenRecordset("tblCargoDescription",
dbOpenDynaset)
Open ExportFolder & Exportname For Output As #1
Do Until rsManifest.EOF
SearchBL = "BL = '" & rsManifest.Fields("BL").Value & "'"
Do While True
rsContainers.FindFirst SearchBL
If rsContainers.NoMatch Then
Exit Do
Else
Call GoodsInContainer(UVInbr) ' print line
' Searching for other containers on same BL number
Do While True
rsContainers.FindNext SearchBL
If rsContainers.NoMatch Then
Exit Do
Else
Call GoodsInContainer(UVInbr) ' print line
End If
Loop
Exit Do
End If
Loop
rsManifest.MoveNext
Loop
rsManifest.Close
Set rsManifest = Nothing
qdfManifest.Close
Set qdfManifest = Nothing
rsContainers.Close
Set rsContainers = Nothing
rsGoods.Close
Set rsGoods = Nothing
Close #1
CreateFCPSfile = "File created on " & ExportFolder & "drive "
End Function
it's code I inherited from an existing app
but it's terribly slow and I am wondering if there is a better way to
do this without breaking functionality
grtz
for now I use the following code:
Function CreateFCPSfile(Voyage As String, UVInbr As String,
ExportFolder As String, Exportname As String) As String
' create export file for customs based onn the imported data
Dim qdfManifest As DAO.QueryDef
Dim SearchBL As String
Set qdfManifest = CurrentDb.QueryDefs("qryfcps_manif")
qdfManifest.Parameters![VOY] = Voyage
qdfManifest.Parameters![POD] = "LIV"
Set rsManifest = qdfManifest.OpenRecordset
Set rsContainers = CurrentDb.OpenRecordset("tblContainers",
dbOpenDynaset)
Set rsGoods = CurrentDb.OpenRecordset("tblCargoDescription",
dbOpenDynaset)
Open ExportFolder & Exportname For Output As #1
Do Until rsManifest.EOF
SearchBL = "BL = '" & rsManifest.Fields("BL").Value & "'"
Do While True
rsContainers.FindFirst SearchBL
If rsContainers.NoMatch Then
Exit Do
Else
Call GoodsInContainer(UVInbr) ' print line
' Searching for other containers on same BL number
Do While True
rsContainers.FindNext SearchBL
If rsContainers.NoMatch Then
Exit Do
Else
Call GoodsInContainer(UVInbr) ' print line
End If
Loop
Exit Do
End If
Loop
rsManifest.MoveNext
Loop
rsManifest.Close
Set rsManifest = Nothing
qdfManifest.Close
Set qdfManifest = Nothing
rsContainers.Close
Set rsContainers = Nothing
rsGoods.Close
Set rsGoods = Nothing
Close #1
CreateFCPSfile = "File created on " & ExportFolder & "drive "
End Function
it's code I inherited from an existing app
but it's terribly slow and I am wondering if there is a better way to
do this without breaking functionality
grtz