Hi,
I know that my question is a bit lengthy but there must be a solution to it.
I have 2 tables which is made to generate Invoice. Both tables are with one-to-many relationship where InvNum is primary key in T_SOHeader and secondary key in T_SOFooter1. These two table are represented on F_SOHeader and Subform F_SOFooter1 wherein user enters the data to make invoices.
I have placed a button on header form F_SOHeader behind which below vba code opens Excel sheet and prepare custom invoice in a particular required design automatically.
Sub CustomInv()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim objSht2 As Excel.Worksheet
Dim db As DAO.Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim iRow As Integer
Dim dRow As Integer
Dim PT
Set rst = CurrentDb.OpenRecordset("SELECT T_SOHeader.InvNum, T_SOHeader.PORef, T_SOHeader.PODate, T_SOHeader.CustCode, T_SOHeader.CustomerName, " & _
"T_SOHeader.ToAgency, T_SOHeader.Shipto, T_SOHeader.PaymentTerms,T_SOHeader.CustomInvNum, T_SOHeader.CustomDNNum, T_SOFooter1.ProductName, T_SOFooter1.ProductCode, T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty as PromoQty, " & _
"WeightInKgPerBag, T_SOFooter1.SalesItemPrice, (NoOfBags + FreeQty) As TotQty, ((NoOfBags + FreeQty) * WeightInKgPerBag /1000) As " & _
"MT, T_SOFooter1.RecordId " & _
"FROM T_SOHeader LEFT JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum " & _
"WHERE T_SOHeader.InvNum = " & Forms!F_SOHeader!InvNum & " " & _
"Group By T_SOHeader.InvNum, T_SOHeader.PORef, T_SOHeader.PODate, T_SOHeader.CustCode, T_SOHeader.CustomerName,T_SOHeader.ToAgency, T_SOHeader.Shipto, T_SOHeader.PaymentTerms, T_SOHeader.CustomInvNum, T_SOHeader.CustomDNNum, " & _
"T_SOFooter1.ProductName, T_SOFooter1.ProductCode,T_SOFooter1.NoOfBags , T_SOFooter1.FreeQty, T_SOFooter1.WeightInKgPerBag, T_SOFooter1.SalesItemPrice," & _
"T_SOFooter1.RecordId ORDER BY T_SOHeader.PODate asc;")
If rst.EOF And rst.BOF Then
MsgBox ("No Records During These Period"), vbInformation, "Null Records Inf."
Exit Sub
Else
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("D:\SLS DB\CUSTOMINV.xls")
Set objSht = objWkb.Worksheets("Sheet1")
objSht.Cells(7, 6).Value = Format(Now(), "dd/mm/yyyy")
objSht.Cells(8, 6).Value = "E00" + rst!CustomINVNum
objSht.Cells(11, 2).Value = rst!CustCode
objSht.Cells(11, 2).HorizontalAlignment = xlRight
objSht.Cells(12, 2).Value = rst!CustomerName
objSht.Cells(12, 4).Value = rst!CustomerName
objSht.Cells(13, 4).Value = rst!ShipTo
objSht.Cells(13, 2).Value = rst!ToAgency
objSht.Cells(16, 2).Value = rst!PORef
objSht.Cells(16, 5).Value = "D00" + rst!CustomDNNum
objSht.Cells(17, 2).Value = rst!PODate
PT = rst!PaymentTerms
objXL.ActiveSheet.Name = "E00" + Forms!F_SOHeader!CustomINVNum
Set objSht2 = objWkb.Worksheets("Sheet2")
objSht2.Cells(12, 2).Value = rst!CustomerName
objXL.ActiveSheet.Name = "D00" + Forms!F_SOHeader!CustomDNNum
End If
iRow = 24
dRow = 20
Dim RowCount As Double
Dim RowCount2 As Double
RowCount = 1
RowCount2 = 1
Dim X As String
rst.MoveFirst
Do While Not rst.EOF
objSht.Cells(iRow, 1).Value = rst!ProductCode
objSht.Cells(iRow, 2).Value = rst!ProductName
objSht.Cells(iRow, 3).Value = rst!WeightInKgPerBag
objSht.Cells(iRow, 5).Value = rst!TotQty
objSht.Cells(iRow, 4).Value = (rst!WeightInKgPerBag * rst!NoOfBags) / 1000
objSht.Cells(iRow, 6).Value = rst!SalesItemPrice
objSht.Cells(iRow, 7).Value = rst!SalesItemPrice * rst!TotQty
objSht2.Cells(20, 1).Value = rst!ProductCode
objSht2.Cells(20, 2).Value = rst!ProductName
objSht2.Cells(20, 4).Value = rst!WeightInKgPerBag
If rst!WeightInKgPerBag = 20 And rst!TotQty > 64 Then
objSht2.Cells(20, 5).Value = 64
End If
iRow = iRow + 1
dRow = dRow + 1
RowCount = RowCount + 1
RowCount2 = RowCount2 + 1
rst.MoveNext
Loop
objSht.Cells(36, 2).Value = PT
Set PT = Nothing
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
rst.Close
End Sub
Up to here it is ok and no problem at all.
I have one other table that displays fix values like product code, product name, weight, palletQty as follows. This new table name is T_PalletMgt.
ProdCode ProdName WeightInKg PerPalletQty
109 FF-2 Tile Adhesive 20 64
110 SS-1 Tile Adhesive 20 64
901 STG Tile Grout 521 05 192
905 STG Tile Grout 320 10 128
Now I need to add extra sheet in same Excel workbook which I added successfully thru adding below vba code to above module. In which I want my code to place data to prepare Packing List using table T_PalletMgt palletizing structure.
For Example:
If total qty in the invoice for 109 FF-2 Tile Adhesive are 150 bags (20 kg each) and for 901 STG Tile Grout 521 are 700 bags (5kg each) then it should calculate the pallets like below
109 109 FF-2 Tile Adhesive 2 64 128
109 109 FF-2 Tile Adhesive 1 22 22
901 STG Tile Grout 521 3 192 576
901 STG Tile Grout 521 1 124 124
And so on…
To do this, I started to work on and included below extra codes line (Blue lines) to above vba module and they started to work up Red line and stuck up. I don’t know if rest of Blue line code will work or no.
AND WHAT WILL BE THE FURTHER CODE TO GET THE PACKING LIST USING THE T_PalletMgt STRUCTURE
Can someone help me out please?
Waiting for your valuable repsponse.
Thanks,
Ashfaque
I know that my question is a bit lengthy but there must be a solution to it.
I have 2 tables which is made to generate Invoice. Both tables are with one-to-many relationship where InvNum is primary key in T_SOHeader and secondary key in T_SOFooter1. These two table are represented on F_SOHeader and Subform F_SOFooter1 wherein user enters the data to make invoices.
I have placed a button on header form F_SOHeader behind which below vba code opens Excel sheet and prepare custom invoice in a particular required design automatically.
Sub CustomInv()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim objSht2 As Excel.Worksheet
Dim db As DAO.Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim iRow As Integer
Dim dRow As Integer
Dim PT
Set rst = CurrentDb.OpenRecordset("SELECT T_SOHeader.InvNum, T_SOHeader.PORef, T_SOHeader.PODate, T_SOHeader.CustCode, T_SOHeader.CustomerName, " & _
"T_SOHeader.ToAgency, T_SOHeader.Shipto, T_SOHeader.PaymentTerms,T_SOHeader.CustomInvNum, T_SOHeader.CustomDNNum, T_SOFooter1.ProductName, T_SOFooter1.ProductCode, T_SOFooter1.NoOfBags, T_SOFooter1.FreeQty as PromoQty, " & _
"WeightInKgPerBag, T_SOFooter1.SalesItemPrice, (NoOfBags + FreeQty) As TotQty, ((NoOfBags + FreeQty) * WeightInKgPerBag /1000) As " & _
"MT, T_SOFooter1.RecordId " & _
"FROM T_SOHeader LEFT JOIN T_SOFooter1 ON T_SOHeader.InvNum=T_SOFooter1.InvNum " & _
"WHERE T_SOHeader.InvNum = " & Forms!F_SOHeader!InvNum & " " & _
"Group By T_SOHeader.InvNum, T_SOHeader.PORef, T_SOHeader.PODate, T_SOHeader.CustCode, T_SOHeader.CustomerName,T_SOHeader.ToAgency, T_SOHeader.Shipto, T_SOHeader.PaymentTerms, T_SOHeader.CustomInvNum, T_SOHeader.CustomDNNum, " & _
"T_SOFooter1.ProductName, T_SOFooter1.ProductCode,T_SOFooter1.NoOfBags , T_SOFooter1.FreeQty, T_SOFooter1.WeightInKgPerBag, T_SOFooter1.SalesItemPrice," & _
"T_SOFooter1.RecordId ORDER BY T_SOHeader.PODate asc;")
If rst.EOF And rst.BOF Then
MsgBox ("No Records During These Period"), vbInformation, "Null Records Inf."
Exit Sub
Else
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("D:\SLS DB\CUSTOMINV.xls")
Set objSht = objWkb.Worksheets("Sheet1")
objSht.Cells(7, 6).Value = Format(Now(), "dd/mm/yyyy")
objSht.Cells(8, 6).Value = "E00" + rst!CustomINVNum
objSht.Cells(11, 2).Value = rst!CustCode
objSht.Cells(11, 2).HorizontalAlignment = xlRight
objSht.Cells(12, 2).Value = rst!CustomerName
objSht.Cells(12, 4).Value = rst!CustomerName
objSht.Cells(13, 4).Value = rst!ShipTo
objSht.Cells(13, 2).Value = rst!ToAgency
objSht.Cells(16, 2).Value = rst!PORef
objSht.Cells(16, 5).Value = "D00" + rst!CustomDNNum
objSht.Cells(17, 2).Value = rst!PODate
PT = rst!PaymentTerms
objXL.ActiveSheet.Name = "E00" + Forms!F_SOHeader!CustomINVNum
Set objSht2 = objWkb.Worksheets("Sheet2")
objSht2.Cells(12, 2).Value = rst!CustomerName
objXL.ActiveSheet.Name = "D00" + Forms!F_SOHeader!CustomDNNum
End If
iRow = 24
dRow = 20
Dim RowCount As Double
Dim RowCount2 As Double
RowCount = 1
RowCount2 = 1
Dim X As String
rst.MoveFirst
Do While Not rst.EOF
objSht.Cells(iRow, 1).Value = rst!ProductCode
objSht.Cells(iRow, 2).Value = rst!ProductName
objSht.Cells(iRow, 3).Value = rst!WeightInKgPerBag
objSht.Cells(iRow, 5).Value = rst!TotQty
objSht.Cells(iRow, 4).Value = (rst!WeightInKgPerBag * rst!NoOfBags) / 1000
objSht.Cells(iRow, 6).Value = rst!SalesItemPrice
objSht.Cells(iRow, 7).Value = rst!SalesItemPrice * rst!TotQty
objSht2.Cells(20, 1).Value = rst!ProductCode
objSht2.Cells(20, 2).Value = rst!ProductName
objSht2.Cells(20, 4).Value = rst!WeightInKgPerBag
If rst!WeightInKgPerBag = 20 And rst!TotQty > 64 Then
objSht2.Cells(20, 5).Value = 64
End If
iRow = iRow + 1
dRow = dRow + 1
RowCount = RowCount + 1
RowCount2 = RowCount2 + 1
rst.MoveNext
Loop
objSht.Cells(36, 2).Value = PT
Set PT = Nothing
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
rst.Close
End Sub
Up to here it is ok and no problem at all.
I have one other table that displays fix values like product code, product name, weight, palletQty as follows. This new table name is T_PalletMgt.
ProdCode ProdName WeightInKg PerPalletQty
109 FF-2 Tile Adhesive 20 64
110 SS-1 Tile Adhesive 20 64
901 STG Tile Grout 521 05 192
905 STG Tile Grout 320 10 128
Now I need to add extra sheet in same Excel workbook which I added successfully thru adding below vba code to above module. In which I want my code to place data to prepare Packing List using table T_PalletMgt palletizing structure.
For Example:
If total qty in the invoice for 109 FF-2 Tile Adhesive are 150 bags (20 kg each) and for 901 STG Tile Grout 521 are 700 bags (5kg each) then it should calculate the pallets like below
109 109 FF-2 Tile Adhesive 2 64 128
109 109 FF-2 Tile Adhesive 1 22 22
901 STG Tile Grout 521 3 192 576
901 STG Tile Grout 521 1 124 124
And so on…
To do this, I started to work on and included below extra codes line (Blue lines) to above vba module and they started to work up Red line and stuck up. I don’t know if rest of Blue line code will work or no.
AND WHAT WILL BE THE FURTHER CODE TO GET THE PACKING LIST USING THE T_PalletMgt STRUCTURE
Can someone help me out please?
Waiting for your valuable repsponse.
Thanks,
Ashfaque
Last edited: