vb_BQ常用脚本

1、刷新数据之前清空数据：
Public Sub Query_BeforeInputParams(bHideDefUI As Boolean, bCancel As Boolean) With Query
.EnvVar("?公司")=""
.EnvVar ("?客户")=""
End With
End Sub
2、关联上一个数据查询：
2.1、客商关联公司
Public Sub Input1_SelChanged()
input2.DataSource ="select distinct t1.custname from" & vbCrLf & _
"{hxnc}.NC.BD_CUBASDOC t1 join {hxnc}.NC.BD_CUMANDOC t2 on " & vbCrLf & _
"t1.PK_CUBASDOC=t2.PK_CUBASDOC " & vbCrLf & _
"join {hxnc}.NC.BD_CORP t3 on t3.pk_corp=t2.pk_corp and t3.unitname ='" & (Input1.Text) & "'"
End Sub
2.2、模糊查找功能
Public Sub Text1_Change()
Input4.DataSource ="select distinct t1.custname from {hxnc}.NC.bd_cubasdoc t1" & vbCrLf & _
"where t1.custname like '%"&( Text1.Text) & "%'"
End Sub
2.3、部门关联公司
Public Sub Input1_SelChanged()
Input2.DataSource ="select distinct t1.deptname from" & vbCrLf & _
"{hxnc}.NC.bd_deptdoc t1 join {hxnc}.NC.bd_corp t2 on " & vbCrLf & _
"t1.pk_corp=t2.pk_corp " & vbCrLf & _
" and t1.deptattr=3 and t2.unitname ='" & (Input1.Text) & "'"
3、设置参数显示报表位置
Public Sub FReport_AfterRefreshData()
FReport.ActiveSheet.Cells(3,"C") = "业务员：" & FReport.EnvVar("?业务员")
FReport.ActiveSheet.Cells(3,"J") ="日期：" & FReport.EnvVar ("?年度") & "年" & FReport.EnvVar ("?月份") & "月"
End Sub
4、创建表
Public Sub CreateTable_Click()
On Error GoTo ERR_P
Prepare
sql = "CREATE TABLE Demo (PID int, PName char(20), Price double, Stock int, PDate date)"
CmdText.Text = sql
ds.Execute sql, result
Exit Sub
ERR_P:
MsgBox "Error occur: " & Err.Description
End Sub
5、插入一条数据
Public Sub InsertRecord_Click()
On Error GoTo ERR_P
Prepare
sql = "INSERT INTO Demo V ALUES(" & PID.Text & ", '" & PName.Text & "', " & Price.Text & ", " & Stock.Text & ", '" & PDate.Text & "')"
CmdText.Text = sql
ds.Execute sql, result
Exit Sub
ERR_P:
MsgBox "Error occur: " & Err.Description
End Sub
6、更新当前记录
Public Sub UpdateRecord_Click()
On Error GoTo ERR_P
Prepare
sql = "UPDATE Demo SET PName = '" & PName.Text & "', Price = " & Price.Text & ", Stock = " & Stock.Text & ", PDate = '" & PDate.Text & "' WHERE PID = " & PID.Text CmdText.Text = sql
ds.Execute sql, result
Exit Sub
ERR_P:
MsgBox "Error occur: " & Err.Description
End Sub
7、删除当前记录
Public Sub DeleteRecord_Click()
On Error GoTo ERR_P
Prepare
sql = "DELETE FROM Demo WHERE PID = " & PID.Text
CmdText.Text = sql
ds.Execute sql, result
Exit Sub
ERR_P:
MsgBox "Error occur: " & Err.Description
End Sub
8、查询数据
Public Sub Query_Click()
On Error GoTo ERR_P
sql = "CREATE DA TASET FROM {Northwind} BY SELECT * FROM Demo WHERE PID = " & PID.Text
CmdText.Text = sql
Dim dset As New SunDataset
dset.CreateDataset sql, -1
PName.Text = dset.CellData(1,2)
Price.Text = dset.CellData(1,3)
Stock.Text = dset.CellData(1,4)
PDate.Text = dset.CellData(1,5)
Exit Sub
ERR_P:
PName.Text = ""
Price.Text = ""
Stock.Text = ""
PDate.Text = ""
End Sub。