关于“为什么VBA无法加载所有发票详细信息"的先前问题的参考 [英] Reference to the Previous Question on Why VBA is not loading all the Invoice details
本文介绍了关于“为什么VBA无法加载所有发票详细信息"的先前问题的参考的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
除了前面的问题外，我们在销售发票上仍然存在相同的加载失败问题:
Addition to the prior question, we still have the same loading failure issue on the sales invoices:
下面的VBA/Json仍然仅加载一行或第一条产品详细信息行，而不是与该表中的销售发票合作的所有产品行详细信息
The VBA/Json below still load only one row or the first product details line instead of all products lines details partnering to that sales invoice in the table
我们希望下面的VBA能够按参数加载发票明细: 例如，如果我们的发票编号为0001，其中包含以下详细信息:
We want The VBA below to be able to load the invoice detail as per parameter: Example if we have invoice number 0001 with the following details:
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中，而不仅仅是第一项
The above detail must all show up in Json NOT only the first item
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
}
]
}
]
}
]
}
所有发票明细都必须按照参数查询显示
All Invoice detail must be appearing as per Parameter query
推荐答案
尝试如下操作:
(为清晰起见，多行显示)
(multiple lines for clarity)
item.Add
"Description",
DLookup(
"Description",
"Qry4",
"Inv =" & Me.CboInv & " AND LineItemID = " & CStr(i)
)
请注意，过滤器参数还包含i
Note the filter argument also includes i
"Inv =" & Me.CboInv & " AND LineItemID = " & CStr(i)
CStr将数字转换为字符串.
CStr converts a number to a string.
我假设您的订单项ID列称为LineItemID.从您的问题尚不清楚，您是否还有lineitemid列
I'm assuming your line item id column is called LineItemID. It's not clear from your question whether you even have a lineitemid column
这篇关于关于“为什么VBA无法加载所有发票详细信息"的先前问题的参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
