tranhai_vn > 29-09-18, 05:57 PM
Private Sub YuChai_Click()
' First check whether data is filled or not
If Nz(lsInvoice, "Null") = "Null" Then Exit Sub
' Access will try to pass selected data on a predefined word form by using this code snipset.
' Now initialize a Query builder so that Data corresponding to Listbox selection will be taken from database
' the query qry_paymentDetailLC contains all details of a valid request form for B/L endorsement... not do it.
Dim rs As New ADODB.Recordset
Dim SqlStr As String
SqlStr = "Select * from Q_PhieuRO where RO='" & ro & "';"
rs.Open SqlStr, CurrentProject.AccessConnection
If rs.EOF Then
MsgBox "There is no data for selected invoice..."
GoTo ExitSub
End If
' Now put data to word
'initialize word
Dim WordApp As Object
Dim WordDoc As Object
Set WordDoc = CreateWordDocument(WordApp)
' hide word away
WordApp.Visible = True
' open the file now
Set WordDoc = WordApp.Documents.Open(CurrentProject.path & "\WORD\YUCHAI\YUCHAI.doc")
' now prepare for replacing// remember the field name in word form
' these are example of using finding functions of word, such keywords must be unique
'[DATE_DAY]/[DATE_MONTH]/[DATE_YEAR]
'[LC_NUMBER]/[INVOICE_NUMBER]/[BILLED_CURRENTCY] [INVOICE_AMOUNT]/[COMMODITY]/[QUANTITY] [M_UNIT]
' For better and more systematic work, it is recommended that you should creat a table for
' cross-matching field name and word keyword.. but for this testing purpose - I just make this manually
' Field Name we will use for searching are:
'NoLC, Goods, tbl_LC.HoldAmount, NoInvoice, Quanlity, AmountUSD,
' Declaration of variable
Dim KeyWords As String, FieldName As String, i As Long
KeyWords = "[SOMAY]/[LOAIXE]/[TENKH]/[DIACHI]/[SDT]/[KM]"
FieldName = "SoMay/loaixe/TENKH/DIACHI/SDT/KM"
'KeyWords = "[LC_NUMBER]/[INVOICE_NUMBER]/[BILLED_CURRENTCY]/[INVOICE_AMOUNT]/[COMMODITY]/[QUANTITY]/[M_UNIT]"
'FieldName = "NoLC/NoInvoice/USD/AmountUSD/Goods/Quanlity/Unknown"
Dim ArrayWord As Variant
Dim ArrayField As Variant
ArrayWord = Split(KeyWords, "/")
ArrayField = Split(FieldName, "/")
For i = LBound(ArrayWord) To UBound(ArrayWord)
ReplaceField ArrayWord(i), Nz(rs.Fields(ArrayField(i)), ""), WordDoc
Next
' post the date
ReplaceField "[NGAYBAN]", Format(ngayban, "DD/MM/YYYY"), WordDoc
ReplaceField "[NGAYNHAN]", Format(ngaynhan, "DD/MM/YYYY"), WordDoc
ReplaceField "[MODEL]", Left(somay, 7), WordDoc
' save the word file and exit
If Dir(CurrentProject.path & "\WORD\YUCHAI\" & "BDYUCHAI-TUHO-LAN00-" & Replace(somay, "*", "") & ".doc") <> "" Then Kill CurrentProject.path & "\WORD\YUCHAI\" & "BDYUCHAI-TUHO-LAN00-" & Replace(somay, "*", "") & ".doc"
WordDoc.SaveAs CurrentProject.path & "\WORD\YUCHAI\" & "BDYUCHAI-TUHO-LAN00-" & Replace(somay, "*", "") & ".doc"
MsgBox "A document was successfully created at: " & CurrentProject.path & "\WORD\YUCHAI\" & "BDYUCHAI-TUHO-LAN00-" & Replace(somay, "*", "") & ".doc", vbInformation
'WordDoc.Close
WordApp.Activate
' clean up memory
Set WordDoc = Nothing
Set WordApp = Nothing
ExitSub:
rs.Close
End Subcpucloi > 29-09-18, 07:18 PM
tranhai_vn > 29-09-18, 09:16 PM
tranhai_vn > 29-09-18, 10:18 PM