Results 1 to 3 of 3

Getting controls from a recordset in ms Access for Json string

  1. #1
    nector is offline Novice
    Windows 8 Access 2016
    Join Date
    Sep 2019
    Posts
    3

    Exclamation Getting controls from a recordset in ms Access for Json string

    I have a VBA code which feeds the Json string perfect well after using GITHUB convertor.Below is my actual Ms Access select query which supply data to my :

    SELECT tblInvoice.INV, tblInvoice.Customer, tblCustomers.TaxID, tblCustomers.Address, DateAdd("d",1,[InvoiceDate]) AS SalesDate, tblCustomers.ItmesID, tblInvoicedetails.Product, tblInvoicedetails.Qty, tblInvoicedetails.Price, tblInvoicedetails.VAT, (([Qty]*[Price])*(1+[VAT])) AS TotalPrice
    FROM tblProducts INNER JOIN ((tblCustomers INNER JOIN tblInvoice ON tblCustomers.ID = tblInvoice.Customer) INNER JOIN tblInvoicedetails ON tblInvoice.INV = tblInvoicedetails.INV) ON tblProducts.PDID = tblInvoicedetails.Product
    WHERE (((tblInvoice.INV)=[Forms]![frmInvoice]![CboInv]));





    I want my Json to appear like below:

    {
    "Customer":"Customer"
    "TaxID": "TaxID"
    "Address":"Address"
    "InvoiceDate":"InvoiceDate",
    "Items:[
    "ItemId":"ItemId"
    "Product":"Product"
    "Qty":"Qty"
    "UnitPrice":"UnitPrice"
    "TotalPrice":"TotalPrice",
    ]
    {
    [
    "T":"T"


    "B": "B"
    ]
    }
    }


    The issue here how to insert the follwong in Json:

    (1) Object
    (2) Nested Object

    Regards

    Chris

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,738
    something like:

    Code:
    
    Public Sub ExportJson()
    Dim vQry, vFile
    Dim rst
    Const Q = """"
    
    
    vFile = "c:\Json.txt"
    vQry = "qsMyQuery"
    Open vFile For Output As #1
    Print #1, "{"
    
    
    Set rst = currentdb.openrecordset(vQry)
    With rst
       While Not .EOF
            Debug.Print Q & "Customer:" & Q & .fields("Customer").Value
            Debug.Print Q & "TaxID:" & Q & .fields("TaxID").Value
            Debug.Print Q & "Address:" & Q & .fields("Address").Value
            Debug.Print Q & "InvoiceDate:" & Q & .fields("InvoiceDate").Value
            Debug.Print Q & "Items:["
            Debug.Print Q & "ItemId:" & Q & .fields("ItemId").Value
            Debug.Print Q & "Product:" & Q & .fields("Product").Value
            Debug.Print Q & "Qty:" & Q & .fields("Qty").Value
            Debug.Print Q & "UnitPrice:" & Q & .fields("UnitPrice").Value
            Debug.Print Q & "TotalPrice:" & Q & .fields("TotalPrice").Value
            
            .movenext   'goto next record
       Wend
    End With
    
    
    Print #1, "]"
    Print #1, "{"
    Print #1, "["
    Print #1, Q & "T" & Q & ":" & Q & "T" & Q
    Print #1, Q & "B" & Q & ":" & Q & "B" & Q
    Print #1, "]"
    Print #1, "}"
    Print #1, "}"
    
    
    Close 1
    Set rst = Nothing
    End Sub

  3. #3
    nector is offline Novice
    Windows 8 Access 2016
    Join Date
    Sep 2019
    Posts
    3
    I have tried that but still no luck, kindly work on the working example below as long as you have VBA/Json converter from GITHUB the example will work along the line what is required is just allocating these {} & [ ] correcting:

    (1) {} is for dictionary (Objects)
    (2) [ ] is Arrays (Collection)


    Option Compare Database
    Option Explicit


    Private Sub CmdChris1_Click()
    Dim c As Collection
    Dim e As Dictionary
    Dim f As Dictionary
    Dim json As String

    Set c = New Collection
    Set e = New Dictionary
    Set f = New Dictionary

    e.Add "Description", "Orange"
    e.Add "Barcode", "7520000360"
    e.Add "Quantity", "8"
    e.Add "UnitPrice", "USD2.00"
    e.Add "Discount", 0#
    c.Add e
    f.Add "Customer", c

    json = JsonConverter.ConvertToJson(ByVal f)

    Debug.Print json
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-16-2017, 12:19 PM
  2. System DSN (MS Access) to REST API / JSON
    By Hitesh in forum Queries
    Replies: 1
    Last Post: 03-15-2016, 11:37 AM
  3. Replies: 4
    Last Post: 01-06-2016, 12:47 PM
  4. form controls & recordset
    By slow&steady in forum Forms
    Replies: 2
    Last Post: 06-25-2010, 02:49 PM
  5. Replies: 1
    Last Post: 07-31-2009, 03:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums