Next Chapter 60 Stock Register

PROJECT SOURCE CODE

SPONSORED LINKS

Stock Register

'******************************************************************

'******************************************************************

'** Author : Samee Ullah Siddiqui

'** Subject : Creating Stock Register

'** Date : Tuesday, September, 09, 2003

'** Modified : Friday, September, 19, 2003

'******************************************************************

'******************************************************************

Private Sub CancelCmd_Click()

Unload Me

End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

If KeyCode = vbKeyReturn Then SendKeys "{TAB}"

End Sub

Private Sub Form_Load()

Dim SQL As String

Dim rst As New ADODB.Recordset

rst.CursorLocation = adUseClient

manufCB.AddItem "(All)"

manufCB.ListIndex = 0

GroupCB.AddItem "(All)"

GroupCB.ListIndex = 0

' SubGroupCB.AddItem "(All)"

' SubGroupCB.ListIndex = 0

' SizeRollCB.AddItem "(All)"

' SizeRollCB.ListIndex = 0

' ClrCB.AddItem "(All)"

' ClrCB.ListIndex = 0

'Populate Manufacturer Combo

With manufCB

SQL = "SELECT ManufCode, Manuf_Desc FROM MANUFACTURER ORDER BY Manuf_Desc"

rst.Open SQL, Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

Do Until rst.EOF

.AddItem rst!MANUF_DESC

.ItemData(.NewIndex) = rst!MANUFCODE

rst.MoveNext

Loop

End With

'Populate Item Group Combo

With GroupCB

SQL = "SELECT IGCode, IGDesc FROM ITEMGROUP ORDER BY IGDesc"

If rst.State = adStateOpen Then rst.Close

rst.Open SQL, Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

Do Until rst.EOF

.AddItem rst!IGDesc

.ItemData(.NewIndex) = rst!IGCode

rst.MoveNext

Loop

End With

' 'Populate Item Sub Group Combo

' With SubGroupCB

' SQL = "SELECT ISGCode, ISGDesc FROM ITEMSUBGROUP ORDER BY ISGDesc"

' If Rst.State = adStateOpen Then Rst.Close

' Rst.Open SQL, Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

' Do Until Rst.EOF

' .AddItem Rst!ISGDesc

' .ItemData(.NewIndex) = Rst!ISGCode

' Rst.MoveNext

' Loop

' End With

'

' 'Populate SizeRoll Combo

' With SizeRollCB

' SQL = "SELECT SizeRollCode, Sizeroll FROM SIZEROLL ORDER BY Sizeroll"

' If Rst.State = adStateOpen Then Rst.Close

' Rst.Open SQL, Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

' Do Until Rst.EOF

' .AddItem Rst!SizeRoll

' .ItemData(.NewIndex) = Rst!SizeRollCode

' Rst.MoveNext

' Loop

' End With

'

' 'Populate Color Combo

' With ClrCB

' SQL = "SELECT ColorCode, Color FROM COLOR ORDER BY Color"

' If Rst.State = adStateOpen Then Rst.Close

' Rst.Open SQL, Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

' Do Until Rst.EOF

' .AddItem Rst!Color

' .ItemData(.NewIndex) = Rst!ColorCode

' Rst.MoveNext

' Loop

' End With

End Sub

Private Sub Form_Unload(Cancel As Integer)

On Error Resume Next

End Sub

Private Sub HeelTxt_KeyPress(KeyAscii As Integer)

ValidateNumber KeyAscii

End Sub

Private Sub OKCmd_Click()

On Error GoTo ErHand

Dim SQL, Clr, Manuf, SizeRoll, IGroup, ISGroup, WhereClouse, Heel As String

Manuf = IIf(manufCB.Text = "(All)", "", " AND ItemDetails.ManufCode ='" & GetProperCode(CStr(manufCB.ItemData(manufCB.ListIndex)), 4) & "'")

IGroup = IIf(GroupCB.Text = "(All)", "", " AND ItemDetails.IGCode ='" & GetProperCode(CStr(GroupCB.ItemData(GroupCB.ListIndex)), 4) & "'")

'ISGroup = IIf(SubGroupCB.Text = "(All)", "", " AND ItemDetails.ISGCode ='" & GetProperCode(CStr(SubGroupCB.ItemData(SubGroupCB.ListIndex)), 4) & "'")

'SizeRoll = IIf(SizeRollCB.Text = "(All)", "", " AND ItemDetails.SizeRollCode ='" & GetProperCode(CStr(SizeRollCB.ItemData(SizeRollCB.ListIndex)), 4) & "'")

'Clr = IIf(ClrCB.Text = "(All)", "", " AND ItemDetails.ColorCode ='" & GetProperCode(CStr(ClrCB.ItemData(ClrCB.ListIndex)), 4) & "'")

ISGroup = ""

SizeRoll = ""

Clr = ""

Heel = ""

SQL = " SHAPE {SELECT ItemDetails.ItemCode, ItemDetails.[Item Name], ITEMSIZE.ISIZE , ITEMSIZE.STOCK, ItemDetails.IGCode, ItemDetails.[Group], ItemDetails.ManufCode, ItemDetails.Manufacturer, ItemDetails.[Purchase Rate], ItemDetails.[Sale Rate] " & _

" FROM ItemDetails LEFT JOIN ITEMSIZE ON ItemDetails.ItemCode = ITEMSIZE.ICODE " & IIf(Manuf & IGroup = "", Null, " WHERE " & Manuf & IGroup) & _

" ORDER BY ItemDetails.Manufacturer, ItemDetails.[Group], ItemDetails.[Item Name], ITEMSIZE.ISIZE " & _

" ;} AS StockCmd COMPUTE StockCmd, SUM(StockCmd.'STOCK') AS TotalItem, COUNT(StockCmd.'ItemCode') AS ItemCount BY 'Manufacturer'"

If DE.StockConn.State = adStateOpen Then DE.StockConn.Close

DE.StockConn.Open ShapeConnectString

If DE.rsStockCmd_Grouping.State = adStateOpen Then DE.rsStockCmd_Grouping.Close

DE.rsStockCmd_Grouping.Open SQL, DE.StockConn, adOpenStatic, adLockOptimistic, adCmdText

Set StockRpt.DataSource = DE

If Option1(0).Value Then StockRpt.Show Else StockRpt.PrintReport

ErHand:

ErrHandler "SaleFrm.CmdPrint"

End Sub