Logo 
Search:

C Programming Forum

Ask Question   UnAnswered
Home » Forum » C Programming       RSS Feeds

Serial Port in Ms Access Restarting the program after processing the second invoice

  Asked By: Christopher    Date: Jun 30    Category: C Programming    Views: 12503
  

After resting for some weeks now, the serial port issues resurface again, I got a strong advice from this forum to separate all the five function so that the problem of the serial USB port hanging Ms Access can go, I’m using the Mod bas done by Mr David M Hickner, by the way is there anyone here who has ever made this same Mod bas and routine work perfectly without problems at all?

Background


Things started working okay but only yesterday the issue Ms Access restarting after processing the second invoice started again:

(1) What is happening is that whenever the first invoice is processed everything is okay, but the second invoice will force Ms Access to restart again.
(2) So to try and solve the problem I have put application. Quit code to force the people to log out after processing the first invoice, which many people do not like it, they want a continuous processing without logging out


Please the separated VBA codes below and advice exactly where issue are:


Private Sub CmdCOpen_Click()
Dim Cancel As Integer
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strError As String
If IsNull(Me.CboEsdInvoices) Then
Beep
MsgBox "Please select the invoice number to sign off now", vbOKOnly, "Invoice number not selected"
Cancel = True
Exit Sub
End If
intPortID = Forms!frmLogin!txtFinComPort.value
' Initialize Communications
lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), "baud=115200 parity=N data=8 stop=1")
If lngStatus <> 0 Then
' Handle error.
Application.Quit
ElseIf lngStatus = 0 Then
Beep
MsgBox "Please note that the port is open now", vbOKOnly, "Start processing the invoice details now"
End If
Call CmdCwrite_Click
Call SleepTest
Call CmdCread_Click
Call CmdClose_Click
Me.CboEsdInvoices.Requery
Application.Quit
Exit Sub
End Sub


Private Sub CmdCwrite_Click()
Dim Cancel As Integer
If IsNull(Me.txtEsDFinInvoice) Then
Beep
MsgBox "Please select the invoice to sign on", vbOKOnly, "Data is required here"
Cancel = True
Exit Sub
End If
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
Dim transaction As Dictionary
Dim transactions As Collection
Dim item As Dictionary
Dim items As Collection
Dim Tax As Collection
Dim i As Long
Dim j As Long
Dim t As Long
Dim itemCount As Long
Dim taxCount As Long
Dim strTaxes As Boolean
Dim invoiceCount As Long
Dim json As Object
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strError As String
Dim strData As String
Dim Details As Variant
Dim n As Integer
Dim s As String
Dim Z As Long
Set root = New Dictionary
Set transactions = New Collection
Set db = CurrentDb
Set qdf = db.QueryDefs("QryJson")
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbSeeChanges)
Set qdf = Nothing
rs.MoveFirst
Do While Not rs.EOF
Set transaction = New Dictionary
transaction.Add "PosVendor", "Nector Prime Accounting Solutions"
transaction.Add "PosSoftVersion", "2.0.0.1"
transaction.Add "PosModel", "Cap-2017"
transaction.Add "PosSerialNumber", DLookup("PosSerialNumber", "tblEFDs", "ID = 1")
transaction.Add "IssueTime", DateAdd("h", 0, Now())
transaction.Add "TransactionType", DLookup("ReceiptType", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "PaymentMode", DLookup("Cashremit", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "SaleType", DLookup("RevenueType", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "LocalPurchaseOrder", DLookup("LocalPurchaseOrder", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "Cashier", DLookup("Cashier", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "BuyerTPIN", DLookup("BuyerTPIN", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "BuyerName", DLookup("BuyerName", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "BuyerTaxAccountName", DLookup("BuyerTaxAccountName", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "BuyerAddress", DLookup("BuyerAddress", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "BuyerTel", DLookup("BuyerTel", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "OriginalInvoiceCode", DLookup("OrignalInvoiceCode", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "OriginalInvoiceNumber", DLookup("OrignalInvoiceNumber", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
transaction.Add "Memo", DLookup("TheNotes", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
'--- loop over all the items
itemCount = Me.txtinternalaudit
Set items = New Collection
For i = 1 To itemCount
Set item = New Dictionary
item.Add "ItemId", i
item.Add "Description", DLookup("ProductName", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
item.Add "BarCode", DLookup("BarCode", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
item.Add "Quantity", DLookup("Quantity", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
item.Add "UnitPrice", DLookup("ESDPrice", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
item.Add "Discount", DLookup("Discount", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
'--- loop over all the taxes
taxCount = 1
Set Tax = New Collection
strTaxes = DLookup("CGControl", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
'--- loop over all the invoices
invoiceCount = 1
For j = 1 To invoiceCount
For t = 1 To taxCount
Next t
item.Add "TaxLabels", Tax

Tax.Add DLookup("TaxClassA", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
If Len(Trim(Nz(DLookup("TourismClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), ""))) > 0 Then
Tax.Add Nz(DLookup("TourismClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), "")
End If
If Len(Trim(Nz(DLookup("InsuranceClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), ""))) > 0 Then
Tax.Add Nz(DLookup("InsuranceClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), "")
End If
item.Add "TotalAmount", DLookup("TotalAmount", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
item.Add "IsTaxInclusive", strTaxes
item.Add "RRP", DLookup("RRP", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))

Next j

items.Add item
Next i
transaction.Add "Items", items

rs.MoveNext
Loop

root.Add "", transaction
On Error Resume Next
intPortID = Forms!frmLogin!txtFinComPort.value
Call CommFlush(intPortID)
If lngStatus <> 0 Then
Application.Quit
ElseIf lngStatus = 0 Then
End If
' Write data to serial port.
' Build data packet to transmit (passing command code, and data to package)
strData = BuildData(JsonConverter.ConvertToJson(transaction, Whitespace:=3))

' Send the data packet and check for error
lngStatus = CommWrite(intPortID, strData)
If lngStatus <> Len(strData) Then
Beep
MsgBox "There is no data to write", vbOKOnly, "Data is required here"
Application.Quit

' Handle error.
lngStatus = CommGetError(strError)
MsgBox "COM Error: " & strError
End If

rs.Close
Set rs = Nothing
Set db = Nothing
Set json = Nothing
Set transaction = Nothing
Set transactions = Nothing
Set item = Nothing
Set items = Nothing
Set Tax = Nothing
Set fld = Nothing
Set root = Nothing
Set qdf = Nothing
Set prm = Nothing
Set Details = Nothing
Beep
MsgBox "Please note that the invoice details are ready to be processed by ESD", vbOKOnly, "Hit ComRead button to start processing now"

Exit Sub
End Sub


Private Sub CmdCread_Click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strError As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim json As Object
Dim strData As String
Dim Details As Variant
Dim Z As Long
Dim n As Integer
intPortID = Forms!frmLogin!txtFinComPort.value
If lngStatus <> 0 Then
' Initialize Communications
lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), "baud=115200 parity=N data=8 stop=1")
ElseIf lngStatus = 0 Then
End If
' Read maximum of 2034 bytes from serial port.
lngStatus = CommRead(intPortID, strData, 2048)

SleepTest

If lngStatus > 0 Then

Beep
MsgBox "Please note that the tax invoice is now signed off", vbOKOnly, "Now you can proceed"

ElseIf lngStatus < 0 Then

Beep
MsgBox "Please note that there is no data to process", vbOKOnly, "The System will close now"
' Handle error.
lngStatus = CommGetError(strError)
MsgBox "COM Error: " & strError
End If
'Processing data from the string above
Set db = CurrentDb
Set rs = db.OpenRecordset("tblEfdReceipts", dbOpenDynaset, dbSeeChanges)
Set json = JsonConverter.ParseJson(Chr(91) & Mid(strData, 8) & Chr(34) & "}" & Chr(93))

'Process data.
Z = 1
For Each Details In json
rs.AddNew
rs![ESDTime] = CDate(Format$(Details("ESDTime"), "00/00/00 00:00:00"))
rs![TerminalID] = Details("TerminalID")
rs![InvoiceCode] = Details("InvoiceCode")
rs![InvoiceNumber] = Details("InvoiceNumber")
rs![FiscalCode] = Details("FiscalCode")
rs![INVID] = Me.txtEsDFinInvoice
rs.Update
Z = 1 + 1
Next

rs.Close
Set rs = Nothing
Set db = Nothing
Set json = Nothing
Set Details = Nothing

Call CommFlush(intPortID)
Beep
MsgBox "Please note that the invoice details is posted to the sales day book", vbOKOnly, "Hit close button to close the port now"
Exit Sub
End Sub



Private Sub CmdClose_Click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
intPortID = Forms!frmLogin!txtFinComPort.value
If lngStatus <> 0 Then
ElseIf lngStatus = 0 Then
End If

Call CommFlush(intPortID)
' Close communications.
Call CommClose(intPortID)
Beep
MsgBox "Please note that the port is closed now", vbOKOnly, "Hit add button to clear the screan"
Exit Sub
End Sub




Option Compare Database

Option Explicit
#If VBA7 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If
Sub SleepTest()
Beep
MsgBox "Please note that the system is waiting for ESD to complete excution", vbOKOnly, "Internal Audit Manager"

Sleep 2000 'delay in milliseconds
Beep
MsgBox "Please note that the ESD has completed excution", vbOKOnly, "Internal Audit Manager"
End Sub

Share: 

 

No Answers Found. Be the First, To Post Answer.

 




Tagged: