除了上一个问题之外，我们在销售发票上仍然有相同的加载失败问题：
下面的 VBA/Json 仍然只加载一行或第一个产品详细信息行，而不是与表中该销售发票匹配的所有产品行详细信息
我们希望下面的VBA能够根据参数加载发票详细信息： 例如，如果我们有发票编号 0001，其中包含以下详细信息：
Invoice Header
--------------------------
Inv Number 0001
Date : 2019-10-10
Customer Name: Lukas
Address : USA
Line Details
--------------------------------------------------
(1) Apple Qty (20) Unit cost(5) Total (100)
(2) Orange Qty (30) Unit cost(5) Total (600)
(3) Lemonade Qty (40) Unit cost(5) Total (800)
以上细节必须全部显示在 Json 中，而不仅仅是第一项
Private Sub CmdSales_Click()
' Const SQL_SELECT As String = "SELECT * FROM Qry3;"
Dim coll As VBA.Collection
Dim dict As Scripting.Dictionary
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim root As Dictionary
Set root = New Dictionary
Dim transaction As Dictionary
Dim transactions As Collection
Dim item As Dictionary
Dim items As Collection
Dim invoice As Dictionary
Dim invoices As Collection
Dim i As Long
Dim j As Long
Set transactions = New Collection
Set db = CurrentDb
Set qdf = db.QueryDefs("Qry4")
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset()
Set qdf = Nothing
rs.MoveFirst
Do While Not rs.EOF
Set transaction = New Dictionary
transaction.Add "PosSerialNumber", DLookup("PosSerialNumber", "Qry4", "Inv =" & Me.CboInv)
transaction.Add "IssueTime", DLookup("IssueTime", "Qry4", "Inv =" & Me.CboInv)
transaction.Add "Customer", DLookup("CustomerName", "Qry4", "Inv =" & Me.CboInv)
transaction.Add "TransactionTyp", 0
transaction.Add "PaymentMode", 0
transaction.Add "SaleType", 0
'--- loop over all the items
Dim itemCount As Long
itemCount = 2
Set items = New Collection
For i = 1 To itemCount
Set item = New Dictionary
item.Add "ItemID", i
item.Add "Description", DLookup("Description", "Qry4", "Inv =" & Me.CboInv)
item.Add "BarCode", DLookup("BarCode", "Qry4", "Inv =" & Me.CboInv)
item.Add "Quantity", DLookup("Qty", "Qry4", "Inv =" & Me.CboInv)
item.Add "UnitPrice", DLookup("unitPrice", "Qry4", "Inv =" & Me.CboInv)
item.Add "Discount", DLookup("Discount", "Qry4", "Inv =" & Me.CboInv)
'--- loop over all the invoices
Dim invoiceCount As Long
invoiceCount = 3
Set invoices = New Collection
For j = 1 To invoiceCount
Set invoice = New Dictionary
invoice.Add "Total", DLookup("TotalAmount", "Qry4", "Inv =" & Me.CboInv) + j
invoice.Add "IsTaxInclusive", DLookup("Inclusive", "Qry4", "Inv =" & Me.CboInv)
invoice.Add "RRP", DLookup("RRP", "Qry4", "Inv =" & Me.CboInv)
invoices.Add invoice
Next j
item.Add "Taxable", invoices
items.Add item
Next i
transaction.Add "Items", items
transactions.Add transaction
rs.MoveNext
Loop
root.Add "JSON Created", Now()
root.Add "Transactions", transactions
Dim json As String
json = JsonConverter.ConvertToJson(root, Whitespace:=3)
Debug.Print json
End Sub
上述代码的当前结果：
{
"JSON Created": "2019-10-10",
"Transactions": [
{
"PosSerialNumber": "102010",
"IssueTime": "2019-09-15",
"Customer": "J J Zingalume",
"TransactionTyp": 0,
"PaymentMode": 0,
"SaleType": 0,
"Items": [
{
"ItemID": 1,
"Description": "Apple (Rgb 350 ML)",
"BarCode": "6009803227328",
"Quantity": 15,
"UnitPrice": 41,
"Discount": 0,
"Taxable": [
{
"Total": 616,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 617,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 618,
"IsTaxInclusive": "True",
"RRP": 52.8
}
]
},
{
"ItemID": 2,
"Description": "Apple (Rgb 350 ML)",
"BarCode": "6009803227328",
"Quantity": 15,
"UnitPrice": 41,
"Discount": 0,
"Taxable": [
{
"Total": 616,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 617,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 618,
"IsTaxInclusive": "True",
"RRP": 52.8
}
]
}
]
},
{
"PosSerialNumber": "102010",
"IssueTime": "2019-09-15",
"Customer": "J J Zingalume",
"TransactionTyp": 0,
"PaymentMode": 0,
"SaleType": 0,
"Items": [
{
"ItemID": 1,
"Description": "Apple (Rgb 350 ML)",
"BarCode": "6009803227328",
"Quantity": 15,
"UnitPrice": 41,
"Discount": 0,
"Taxable": [
{
"Total": 616,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 617,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 618,
"IsTaxInclusive": "True",
"RRP": 52.8
}
]
},
{
"ItemID": 2,
"Description": "Apple (Rgb 350 ML)",
"BarCode": "6009803227328",
"Quantity": 15,
"UnitPrice": 41,
"Discount": 0,
"Taxable": [
{
"Total": 616,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 617,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 618,
"IsTaxInclusive": "True",
"RRP": 52.8
}
]
}
]
},
{
"PosSerialNumber": "102010",
"IssueTime": "2019-09-15",
"Customer": "J J Zingalume",
"TransactionTyp": 0,
"PaymentMode": 0,
"SaleType": 0,
"Items": [
{
"ItemID": 1,
"Description": "Apple (Rgb 350 ML)",
"BarCode": "6009803227328",
"Quantity": 15,
"UnitPrice": 41,
"Discount": 0,
"Taxable": [
{
"Total": 616,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 617,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 618,
"IsTaxInclusive": "True",
"RRP": 52.8
}
]
},
{
"ItemID": 2,
"Description": "Apple (Rgb 350 ML)",
"BarCode": "6009803227328",
"Quantity": 15,
"UnitPrice": 41,
"Discount": 0,
"Taxable": [
{
"Total": 616,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 617,
"IsTaxInclusive": "True",
"RRP": 52.8
},
{
"Total": 618,
"IsTaxInclusive": "True",
"RRP": 52.8
}
]
}
]
}
]
}
所有发票详细信息必须按照参数查询显示
尝试这样的事情：
(为清楚起见，多行(
item.Add
"Description",
DLookup(
"Description",
"Qry4",
"Inv =" & Me.CboInv & " AND LineItemID = " & CStr(i)
)
请注意，筛选器参数还包括i
"Inv =" & Me.CboInv & " AND LineItemID = " & CStr(i)
CStr将数字转换为字符串。
我假设您的行项目 ID 列称为LineItemID.从您的问题中不清楚您是否甚至有一个行项