Thanks for the feedback Mike. Super appreciative.
I have sussed out my breakout code & almost have it nailed now.
In general, the code to get other database values work when a single value is returned i.e
- Code: Select all
SELECT InvoiceNo WHERE InvoiceID = InvoiceID
In my fairly big SELECT statement, I am getting multiple cols which also may return multiple rows. I have done this before & am using similar code but the breakout is failing on the line
- Code: Select all
Do While .FetchRow(CInt(rHwnd)) = True
Here is my full code. The suss line is down around line 90-95ish. The goal is to dump out a file showing the items to pick.
I need to show a single line for the items which are not serialised as all they need is the QtyThisDelivery. For those serialised items where the QtyThisDelivery > 1, there may be multiple LineDetails, so for those I need to show a separate row with the LineDetails.SerialNo & LineDetails.Quantity. And the expiry date.
For each line returned, it will dump a line to the CSV file.
- Code: Select all
Dim rhwnd
Dim SQL
Dim InvoiceNo
Dim ExpectedDeliveryDate
Dim HistoryNo
Dim DelAddress1
Dim DelAddress2
Dim DelAddress3
Dim DelAddress4
Dim PostCode
Dim Country
Dim Contents
Dim PartNo
Dim QuantityThisDel
Dim Quantity
Dim SerialNo
Dim ExpiryDate
Dim UseSerial
Dim DebtorAccountNo
Dim DebtorName
Dim FileName
Dim fso, f
Dim TextOut
Dim LineNo
LineNo = 0
With JiwaDatabaseObject
' Open connection to database
rHwnd = .StatementOpen(.ConnectionRead1, False, "", 1)
SQL = "SELECT InvoiceNo from SO_Main WHERE InvoiceID = '" & InvoiceID & "'"
If .ExecuteSelect(CInt(rHwnd),CStr(SQL)) Then
.BindMem CInt(rHwnd), 1, vbString
If .FetchRow(CInt(rHwnd)) = True Then
InvoiceNo = .GetData(CInt(rHwnd), 1)
End If
Else
MsgBox "Error in SQL : " & SQL
End If
.StatementClose CInt(rHwnd)
End With
FileName = "\\sssql03\TOSEQOS$\AU\SO\SO_" & InvoiceNo & ".CSV"
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.CreateTextFile(FileName, True)
TextOut = "InvoiceNo|CustomerCode|CustomerName|LineNo|PartNo|QtyOrdered|DelDate|DelAddr1|DelAddr2|City|State|DelPostcode|Country|CALLTAG|ID|SubQty|SerialNo|ExpiryDate"
f.WriteLine TextOut
With JiwaDatabaseObject
rhwnd = .StatementOpen(.ConnectionRead1, False, "", 1000)
SQL = "SELECT SO_Main.InvoiceNo, SO_Main.ExpectedDeliveryDate, SO_History.HistoryNo, SO_History.DelAddress1, SO_History.DelAddress2, "
SQL = SQL & " SO_History.DelAddress3, SO_History.DelAddress4, SO_History.PostCode, SO_History.DeliveryAddressCountry, "
SQL = SQL & " SO_CustomSettingValues.Contents, SO_Lines.PartNo, SO_Lines.QuantityThisDel, SO_LineDetails.Quantity, "
SQL = SQL & " SO_LineDetails.SerialNo, SO_LineDetails.ExpiryDate,SO_Lines.UseSerialNo,"
SQL = SQL & " DB_Main.AccountNo, DB_Main.Name"
SQL = SQL & " FROM SO_Main INNER Join"
SQL = SQL & " DB_Main ON SO_Main.DebtorID = DB_Main.DebtorID INNER Join"
SQL = SQL & " SO_History ON SO_Main.InvoiceID = SO_History.InvoiceID And SO_Main.CurrentHistoryNo = SO_History.HistoryNo INNER Join"
SQL = SQL & " SO_Lines ON SO_History.InvoiceHistoryID = SO_Lines.InvoiceHistoryID INNER Join"
SQL = SQL & " SO_LineDetails ON SO_Lines.InvoiceLineID = SO_LineDetails.InvoiceLineID INNER Join"
SQL = SQL & " SO_CustomSettingValues ON SO_Main.InvoiceID = SO_CustomSettingValues.InvoiceID INNER Join"
SQL = SQL & " SO_CustomSetting ON SO_CustomSettingValues.SettingID = SO_CustomSetting.SettingID"
SQL = SQL & " WHERE (SO_CustomSetting.SettingName = 'CALLTAG') AND (SO_History.InvoiceHistoryID = '" & HistoryID & "')"
If .ExecuteSelect(CInt(rhwnd), SQL) = True Then
.BindMem CInt(rhwnd), 1, vbString
.BindMem CInt(rhwnd), 2, vbDate
.BindMem CInt(rhwnd), 3, vbString
.BindMem CInt(rhwnd), 4, vbString 'deladdr1
.BindMem CInt(rhwnd), 5, vbString
.BindMem CInt(rhwnd), 6, vbString
.BindMem CInt(rhwnd), 7, vbString
.BindMem CInt(rhwnd), 8, vbString
.BindMem CInt(rhwnd), 9, vbString 'country
.BindMem CInt(rhwnd), 10, vbString
.BindMem CInt(rhwnd), 11, vbString
.BindMem CInt(rhwnd), 12, vbDouble
.BindMem CInt(rhwnd), 13, vbDouble
.BindMem CInt(rhwnd), 14, vbString
.BindMem CInt(rhwnd), 15, vbDate
.BindMem CInt(rhwnd), 16, vbDouble
.BindMem CInt(rhwnd), 17, vbString
.BindMem CInt(rhwnd), 18, vbString
MsgBox "here 1"
Do While .FetchRow(CInt(rHwnd)) = True
InvoiceNo = .GetData(CInt(rhwnd), 1)
ExpectedDeliveryDate = .GetData(CInt(rhwnd), 2)
HistoryNo = .GetData(CInt(rhwnd), 3)
DelAddress1 = .GetData(CInt(rhwnd), 4)
DelAddress2 = .GetData(CInt(rhwnd), 5)
DelAddress3 = .GetData(CInt(rhwnd), 6)
DelAddress4 = .GetData(CInt(rhwnd), 7)
PostCode = .GetData(CInt(rhwnd), 8)
Country = .GetData(CInt(rhwnd), 9)
Contents = .GetData(CInt(rhwnd), 10)
PartNo = .GetData(CInt(rhwnd), 11)
QuantityThisDel = CSng(.GetData(CInt(rhwnd), 12))
Quantity = CSng(.GetData(CInt(rhwnd), 13))
SerialNo = .GetData(CInt(rhwnd), 14)
ExpiryDate = .GetData(CInt(rhwnd), 15)
UseSerial = CBool(.GetData(CInt(rhwnd),16))
DebtorAccountNo = .GetData(CInt(rhwnd), 17)
DebtorName = .GetData(CInt(rhwnd), 18)
MsgBox "here 2"
''''''''''''''''''''''''''''' Do the file output inside this loop
HistNo = Right("0" & HistoryNo,2)
MsgBox "here 3"
MsgBox "serial?: " & UseSerial
If UseSerial = False Then
LineNo = LineNo + 1
TextOut = InvoiceNo & "-" & HistNo & "|"
TextOut = TextOut & DebtorAccountNo & "|"
TextOut = TextOut & DebtorName & "|"
TextOut = TextOut & LineNo & "|"
TextOut = TextOut & PartNo & "|"
TextOut = TextOut & QuantityThisDel & "|"
TextOut = TextOut & ExpectedDeliveryDate & "|"
TextOut = TextOut & DelAddress1 & "|"
TextOut = TextOut & DelAddress2 & "|"
TextOut = TextOut & DelAddress3 & "|"
TextOut = TextOut & DelAddress4 & "|"
TextOut = TextOut & PostCode & "|"
TextOut = TextOut & Country & "|"
Select Case Contents
Case "1" TextOut = TextOut & "Urgent"
Case "2" TextOut = TextOut & "Pickup"
Case Else TextOut = TextOut & "Normal"
End Select
TextOut = TextOut & "|" & HistoryID & "|"
TextOut = TextOut & Quantity & "|"
TextOut = TextOut & "|"
f.WriteLine TextOut
Else
' Use serials = True
LineNo = LineNo + 1
TextOut = InvoiceNo & "-" & HistNo & "|"
TextOut = TextOut & DebtorAccountNo & "|"
TextOut = TextOut & DebtorName & "|"
TextOut = TextOut & LineNo & "|"
TextOut = TextOut & PartNo & "|"
TextOut = TextOut & QuantityThisDel & "|"
TextOut = TextOut & ExpectedDeliveryDate & "|"
TextOut = TextOut & DelAddress1 & "|"
TextOut = TextOut & DelAddress2 & "|"
TextOut = TextOut & DelAddress3 & "|"
TextOut = TextOut & DelAddress4 & "|"
TextOut = TextOut & PostCode & "|"
TextOut = TextOut & Country & "|"
Select Case Contents
Case "1" TextOut = TextOut & "Urgent"
Case "2" TextOut = TextOut & "Pickup"
Case Else TextOut = TextOut & "Normal"
End Select
TextOut = TextOut & "|" & HistoryID & "|"
TextOut = TextOut & Quantity & "|"
TextOut = TextOut & SerialNo & "|"
TextOut = TextOut & ExpiryDate & "|"
f.WriteLine TextOut
End If
''''''''''''''''''''''''''''' END File Output loop
Loop
Else
MsgBox "Can't get delivery data " & .ErrorMessage
End If
.StatementClose CInt(rhwnd)
End With
End Sub
Cheers