|
本帖最后由 sizhen5101 于 2011-10-16 19:55 编辑
老师帮忙看看这个查询宏,我把单据录入的物品内容增加到了11行,在查询的时候,出库单没有问题,但入库单老是不能调用入库数量,并且采购人会变成领用人,
Sub chaxun()
Dim hm, cel As Range
If Range("j3").Value = "" Then MsgBox "请输入需要查询的单据号码": Exit Sub
Application.EnableEvents = False
hm = Range("j3").Value
ActiveSheet.Unprotect
Range("d3,d4,f3,d19,f19,j19").Value = ""
For Each cel In Sheet6.Range("e2:e" & Sheet6.[f65536].End(xlUp).Row)
If cel.Value = hm Then
If cel.Offset(0, -4).Value = "入库单" Then
Range("b2") = "物 资 入 库 单"
Else
Range("b2") = "物 资 出 库 单"
End If
Range("d3") = cel.Offset(0, -3).Value
Range("d4") = cel.Offset(0, -1).Value
Range("f3") = cel.Offset(0, -2).Value
Range("d19") = cel.Offset(0, 12).Value
Range("j19") = cel.Offset(0, 15).Value
If Range("b2") = "入库单" Then
Range("e19") = "采购员"
Range("f19") = cel.Offset(0, 13).Value
Else
Range("e19") = "领用人"
Range("f19") = cel.Offset(0, 14).Value
End If
End If
Next
Set X = CreateObject("adodb.connection")
X.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
If Range("b2") = "物 资 入 库 单" Then
Range("c6:g16").Value = ""
Range("i6:j16").Value = ""
Range("c6").CopyFromRecordset X.Execute("select 库别,物品名称,规格型号,单位,入库数量 from [数据库$] WHERE 单据号码 ='" & hm & "'")
Range("i6").CopyFromRecordset X.Execute("select 入库金额 from [数据库$] WHERE 单据号码 ='" & hm & "'")
Else
Range("c6:h16").Value = ""
Range("j6:j16").Value = ""
Range("c6").CopyFromRecordset X.Execute("select 库别,物品名称,规格型号,单位,出库数量,出库单价 from [数据库$] WHERE 单据号码 ='" & hm & "'")
End If
Range("j6").CopyFromRecordset X.Execute("select 备注 from [数据库$] WHERE 单据号码 ='" & hm & "'")
ActiveSheet.Protect
X.Close
Set X = Nothing
If Range("C6") = "" Then MsgBox "没有该单据号的记录!"
Application.EnableEvents = True
End Sub |
|