Prepare packing list in same excel workbook but in sheet2

[Ashfaque]

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