BOM表查詢(xún)的VB實(shí)現(xiàn)方法

1、用VB代碼實(shí)現(xiàn)方法引用 : 無(wú) , 部件 : 無(wú)設(shè)計(jì)：在Forml中右下角加入一個(gè) CommandButton名稱(chēng)默認(rèn)為CommandUf體AutoRedraw 屬性設(shè)為 True窗體文件一:Form1Option ExplicitPrivate mBom As Collection 這是入口的集合Private mBomReturn As Collection 這是出口的集合,未經(jīng)處理Private mBomReturnLast As Collection 這是出口的集合,經(jīng)過(guò)處理Private Sub AddBomRecord()在這里往mBorm入數(shù)據(jù)庫(kù)里面的原內(nèi)容，為求簡(jiǎn)便，我不想連接

2、數(shù)據(jù)庫(kù) 直接往里面寫(xiě)入記錄了 , 如果需要 , 你就直接連接數(shù)據(jù)庫(kù), 分析一下里面的代碼，然后再往mBomt面寫(xiě)入記錄FG SA1 2.0000FG SA2 3.0000SA1 PT1 4.0000SA1 PT2 5.0000SA2 PT1 6.0000SA2 PT3 7.0000Dim mBomV alue As cBomV alueSet mBomV alue = New cBomV aluemBomV alue.AssBom = FGmBomV alue.BomPoint = SA1mBomV alue.Quantity = 2mBom.Add mBomV alueSet mBomV a

3、lue = New cBomV aluemBomV alue.AssBom = FGmBomV alue.BomPoint = SA2mBomV alue.Quantity = 3mBom.Add mBomV alueSet mBomV alue = New cBomV aluemBomV alue.AssBom = SA1mBomV alue.BomPoint = PT1mBomV alue.Quantity = 4mBom.Add mBomV alueSet mBomV alue = New cBomV aluemBomV alue.AssBom = SA1mBomV alue.BomPo

4、int = PT2mBomV alue.Quantity = 5mBom.Add mBomV alueSet mBomV alue = New cBomV aluemBomV alue.AssBom = SA2mBomV alue.BomPoint = PT1mBomV alue.Quantity = 6mBom.Add mBomV alueSet mBomV alue = New cBomV aluemBomV alue.AssBom = SA2mBomV alue.BomPoint = PT3mBomV alue.Quantity = 7mBom.Add mBomV alueEnd Sub

5、Private Sub Command1_Click()Dim i As IntegerDim m As cBomV alue 進(jìn)行計(jì)算 注意以下兩個(gè)新建實(shí)例 , 必須放置于 GetBomList 前 , 該操作也有清空現(xiàn)有數(shù)據(jù)的作用 , 否則會(huì)造成錯(cuò)誤 即第一次運(yùn)行后保存了數(shù)據(jù)于該兩個(gè)變量中 , 并未清除相關(guān)記錄 , 而下一次運(yùn) 行則在現(xiàn)有的基礎(chǔ)上再進(jìn)行加操作, 因此數(shù)據(jù)錯(cuò)誤了。Set mBomReturn = New CollectionSet mBomReturnLast = New CollectionCall GetBomList 計(jì)算后 ,mBomReturnLast 返回的就是最

6、終結(jié)果If mBomReturnLast.Count 0 ThenMsgBox 沒(méi)有記錄 !, vbInformation + vbOKOnly, BOM 表計(jì)算 Exit SubElse 在窗體中打印出列表的內(nèi)容Me.ClsPrint Assbom & vbTab & Point & vbTab & QuantityFor i = 1 To mBomReturnLast.CountSet m = mBomReturnLast.Item(i)Print m.AssBom & vbTab & m.BomPoint & vbTab & m.QuantityNext iEnd IfEnd SubPr

7、ivate Sub Form_Load() 窗體調(diào)用處新建實(shí)例 , 然后再裝入數(shù)據(jù)Set mBom = New CollectionAddBomRecordEnd Sub* 以下為進(jìn)行計(jì)算部分的代碼, 注意 Collection 里面的處理I*Private Sub GetBomList()Dim mBomTop As Collection 這里保存了頂級(jí)產(chǎn)成品Dim i As IntegerDim j As IntegerDim m As cBomReturnV alueDim mLast As cBomV alueDim bFind As BooleanSet mBomTop = New

8、Collection 裝入頂級(jí)產(chǎn)成品LoadBomTop mBomTop 對(duì)頂級(jí)產(chǎn)品進(jìn)行下級(jí)的判斷For i = 1 To mBomTop.Count 最后一個(gè)參數(shù)為 1, 表示一個(gè)單位的產(chǎn)成品Call CalcNextBom(mBomTop.Item(i), mBomTop.Item(i), 1)Next i最終得以mBomReturn這里面已初步形成了結(jié)果了再進(jìn)行表達(dá)式計(jì)算，得到的值返回到 mBomReturnLast中，注:mBomReturnLast 這個(gè)集合加入 cBomV alue 內(nèi)容For i = 1 To mBomReturn.Count處理一下最終結(jié)果，如果沒(méi)有在Colle

9、ction 里面發(fā)現(xiàn)相同的AssBomR BomPoint, 則新增加一個(gè), 如果已發(fā)現(xiàn), 僅只是數(shù)量相加Set m = mBomReturn(i) 查找是否已加入bFind = FalseFor j = 1 To mBomReturnLast.CountSet mLast = mBomReturnLast(j)If Trim(mLast.AssBom) = Trim(m.AssBom) And Trim(mLast.BomPoint) = Trim(m.BomPoint) Then 如果發(fā)現(xiàn)有相同的 , 則加入相關(guān)數(shù)字mLast.Quantity = mLast.Quantity + Cal

10、cExpression(m.Expression)bFind = TrueEnd IfNext j如果沒(méi)有找到Set mLast = New cBomV aluemLast.AssBom = Trim(m.AssBom)mLast.BomPoint = Trim(m.BomPoint)mLast.Quantity = CalcExpression(Trim(m.Expression)mBomReturnLast.Add mLastEnd IfNext i 所有操作完畢End SubPrivate Sub LoadBomTop(ByRef BomTop As Collection)裝入頂級(jí)產(chǎn)成品

11、,并返回到BomTo葉 即存儲(chǔ)過(guò)程中 GetBomList 中的第一個(gè)游標(biāo)的創(chuàng)建bomTopDim i As IntegerDim j As IntegerDim n As IntegerDim bMark As Boolean 這只是一個(gè)標(biāo)識(shí)符, 表明是否發(fā)現(xiàn)非頂級(jí)Dim bMarkAdd As Boolean 用于判斷是否已加入到 BomTo計(jì)的標(biāo)識(shí)判斷方法,如果AssBom在BomPoint中，那就是頂級(jí)了Dim sBomAssBom As StringFor i = 1 To mBom.CountsBomAssBom = Trim(mBom.Item(i).AssBom) 再進(jìn)行循環(huán)b

12、Mark = FalseFor j = 1 To mBom.CountIf sBomAssBom = Trim(mBom.Item(j).BomPoint) ThenbMark = TrueEnd IfNext jIf bMark = False Then如果沒(méi)有發(fā)現(xiàn)有相同的，則BomTopJ入 加入前需要進(jìn)行判斷是否已加入For n = 1 To BomTop.CountIf BomTop.Item(n) = sBomAssBom ThenbMarkAdd = TrueEnd IfNext nIf bMarkAdd = False Then 如果沒(méi)有加入過(guò), 則加入BomTop.Add sBomAssBomEnd IfEnd IfNext iEnd SubGetBomTrueList的存儲(chǔ)過(guò)程用VB來(lái)描述Private Sub CalcNextBom