我才学VB两天,好多知识都不太懂,现在要做一个轴承查询软件,有5个文本框和1个组合框六个条件任意组合查询,下面是按下确定键的代码,不过一运行就出错了,希望大家能帮帮我,不胜感激Private Sub cmdOK_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sqlStr As String
Dim msgText As String
Dim i As Integer
Dim j As Integer
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;Persist Security Info=False;"
sqlStr = "Select * From Datafind Where 1=1"
If txt_xinghao = "" And txt_neijing = "" And txt_waijing = "" And txt_kuandu = "" And txt_zhongliang = "" Then
MsgBox "请输入查询条件!", vbInformation + vbOKOnly, "提示!"
Exit Sub
End IfIf Len(Trim(txt_xinghao)) > 0 Then
sqlStr = sqlStr & " And xinghao='" & Trim(txt_xinghao.Text) & "'"
End If
If Len(Trim(txt_neijing)) > 0 Then
sqlStr = sqlStr & " And neijing='" & Trim(txt_neijing.Text) & "'"
End If
If Len(Trim(txt_waijing)) > 0 Then
sqlStr = sqlStr & " And waijing='" & Trim(txt_waijing.Text) & "'"
End If
If Len(Trim(txt_kuandu)) > 0 Then
sqlStr = sqlStr & " And kuandu=" & Trim(txt_kuandu)
End If
If Len(Trim(txt_zhongliang)) > 0 Then
sqlStr = sqlStr & " And zhongliang=" & Trim(txt_zhongliang)
End If
sqlStr = "Datafind.xinghao as txt_xinghao, Datafind.neijing as txt_neijing," _
& "Datafind.waijing as txt_waijing,Datafind.kuandu as txt_kuandu" _
& "Datafind.zhongliang as txt_zhongliang,Datafind.leixing as txt_leixing" _
& "Datafind.laoxinghao as txt_laoxinghao,Datafind.dongzaihe as txt_dongzaihe" _
& "Datafind.jingzaihe as txt_jingzaihe,Datafind.zhirunhua as txt_zhirunhua" _
& "Datafind.yourunhua as txt_yourunhua" _
& "from Datafind WHERE "
Set rs = ExecuteSQL(sqlStr, msgText)
If rs.RecordCount = 0 Then
MsgBox "无相关记录", vbExclamation, "提示"
grdBearingdata.ClearElse
With grdBearingdata
.ScrollBars = flexScrollBarBoth
.FixedCols = 1
.Rows = rs.RecordCount + 1
.Cols = 12
.SelectionMode = flexSelectionByRow
'Print grdBearingdata.Rows
'设定行高
For i = 0 To grdBearingdata.Rows - 1
grdBearingdata.RowHeight(i) = 280
Next i
'设定列的属性
grdBearingdata.Row = 0
For i = 0 To grdBearingdata.Cols - 1
grdBearingdata.Col = i '指定当前列为第i列
grdBearingdata.FixedAlignment(i) = 4 '每列内容居中显示
Select Case i
Case 0
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "ID"
Case 1
grdBearingdata.ColWidth(i) = 2000 '设定列宽
grdBearingdata.Text = "轴承类型"
Case 2
grdBearingdata.ColWidth(i) = 3000 '设定列宽
grdBearingdata.Text = "轴承型号"
Case 3
grdBearingdata.ColWidth(i) = 3000 '设定列宽
grdBearingdata.Text = "轴承老型号"
Case 4
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "内径(mm)"
Case 5
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "外径(mm)"
Case 6
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "宽度(mm)"
Case 7
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "Cr(KN)"
Case 8
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "Cor(KN)"
Case 9
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "脂润滑转速(r/min)"
Case 10
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "油润滑转速(r/min)"
Case 11
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "重量(kg)"
End Select
Next i
'rs.MoveFirst
i = 1
While (Not rs.EOF)
grdBearingdata.Row = i
For j = 0 To grdBearingdata.Cols - 1
grdBearingdata.Col = j '设置当前为列为第j列
grdBearingdata.CellAlignment = 4 '每列内容居中显示
Select Case j
Case 0
grdBearingdata.Text = "" & i
Case 1
grdBearingdata.Text = rs.Fields("leixing")
Case 2
grdBearingdata.Text = rs.Fields("xinghao")
Case 3
grdBearingdata.Text = rs.Fields("laoxinghao")
Case 4
grdBearingdata.Text = rs.Fields("neijing")
Case 5
grdBearingdata.Text = rs.Fields("waijing")
Case 6
grdBearingdata.Text = rs.Fields("kuandu")
Case 7
grdBearingdata.Text = rs.Fields("dongzaihe")
Case 8
grdBearingdata.Text = rs.Fields("jingzaihe")
Case 9
grdBearingdata.Text = rs.Fields("zhirunhua")
Case 10
grdBearingdata.Text = rs.Fields("yourunhua")
Case 11
grdBearingdata.Text = rs.Fields("zhongliang")
End Select
Next j
rs.MoveNext
i = i + 1
Wend
grdBearingdata.Visible = Truers.Close
End With
End If
End Sub
提示错误3704 错误的行是If rs.RecordCount = 0 then
我知道代码肯定错误百出,大家都帮忙看看。我现在还不太懂怎么实现5个文本框+combo的任意组合查询,希望知道的朋友能不吝赐教
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sqlStr As String
Dim msgText As String
Dim i As Integer
Dim j As Integer
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;Persist Security Info=False;"
sqlStr = "Select * From Datafind Where 1=1"
If txt_xinghao = "" And txt_neijing = "" And txt_waijing = "" And txt_kuandu = "" And txt_zhongliang = "" Then
MsgBox "请输入查询条件!", vbInformation + vbOKOnly, "提示!"
Exit Sub
End IfIf Len(Trim(txt_xinghao)) > 0 Then
sqlStr = sqlStr & " And xinghao='" & Trim(txt_xinghao.Text) & "'"
End If
If Len(Trim(txt_neijing)) > 0 Then
sqlStr = sqlStr & " And neijing='" & Trim(txt_neijing.Text) & "'"
End If
If Len(Trim(txt_waijing)) > 0 Then
sqlStr = sqlStr & " And waijing='" & Trim(txt_waijing.Text) & "'"
End If
If Len(Trim(txt_kuandu)) > 0 Then
sqlStr = sqlStr & " And kuandu=" & Trim(txt_kuandu)
End If
If Len(Trim(txt_zhongliang)) > 0 Then
sqlStr = sqlStr & " And zhongliang=" & Trim(txt_zhongliang)
End If
sqlStr = "Datafind.xinghao as txt_xinghao, Datafind.neijing as txt_neijing," _
& "Datafind.waijing as txt_waijing,Datafind.kuandu as txt_kuandu" _
& "Datafind.zhongliang as txt_zhongliang,Datafind.leixing as txt_leixing" _
& "Datafind.laoxinghao as txt_laoxinghao,Datafind.dongzaihe as txt_dongzaihe" _
& "Datafind.jingzaihe as txt_jingzaihe,Datafind.zhirunhua as txt_zhirunhua" _
& "Datafind.yourunhua as txt_yourunhua" _
& "from Datafind WHERE "
Set rs = ExecuteSQL(sqlStr, msgText)
If rs.RecordCount = 0 Then
MsgBox "无相关记录", vbExclamation, "提示"
grdBearingdata.ClearElse
With grdBearingdata
.ScrollBars = flexScrollBarBoth
.FixedCols = 1
.Rows = rs.RecordCount + 1
.Cols = 12
.SelectionMode = flexSelectionByRow
'Print grdBearingdata.Rows
'设定行高
For i = 0 To grdBearingdata.Rows - 1
grdBearingdata.RowHeight(i) = 280
Next i
'设定列的属性
grdBearingdata.Row = 0
For i = 0 To grdBearingdata.Cols - 1
grdBearingdata.Col = i '指定当前列为第i列
grdBearingdata.FixedAlignment(i) = 4 '每列内容居中显示
Select Case i
Case 0
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "ID"
Case 1
grdBearingdata.ColWidth(i) = 2000 '设定列宽
grdBearingdata.Text = "轴承类型"
Case 2
grdBearingdata.ColWidth(i) = 3000 '设定列宽
grdBearingdata.Text = "轴承型号"
Case 3
grdBearingdata.ColWidth(i) = 3000 '设定列宽
grdBearingdata.Text = "轴承老型号"
Case 4
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "内径(mm)"
Case 5
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "外径(mm)"
Case 6
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "宽度(mm)"
Case 7
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "Cr(KN)"
Case 8
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "Cor(KN)"
Case 9
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "脂润滑转速(r/min)"
Case 10
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "油润滑转速(r/min)"
Case 11
grdBearingdata.ColWidth(i) = 1500 '设定列宽
grdBearingdata.Text = "重量(kg)"
End Select
Next i
'rs.MoveFirst
i = 1
While (Not rs.EOF)
grdBearingdata.Row = i
For j = 0 To grdBearingdata.Cols - 1
grdBearingdata.Col = j '设置当前为列为第j列
grdBearingdata.CellAlignment = 4 '每列内容居中显示
Select Case j
Case 0
grdBearingdata.Text = "" & i
Case 1
grdBearingdata.Text = rs.Fields("leixing")
Case 2
grdBearingdata.Text = rs.Fields("xinghao")
Case 3
grdBearingdata.Text = rs.Fields("laoxinghao")
Case 4
grdBearingdata.Text = rs.Fields("neijing")
Case 5
grdBearingdata.Text = rs.Fields("waijing")
Case 6
grdBearingdata.Text = rs.Fields("kuandu")
Case 7
grdBearingdata.Text = rs.Fields("dongzaihe")
Case 8
grdBearingdata.Text = rs.Fields("jingzaihe")
Case 9
grdBearingdata.Text = rs.Fields("zhirunhua")
Case 10
grdBearingdata.Text = rs.Fields("yourunhua")
Case 11
grdBearingdata.Text = rs.Fields("zhongliang")
End Select
Next j
rs.MoveNext
i = i + 1
Wend
grdBearingdata.Visible = Truers.Close
End With
End If
End Sub
提示错误3704 错误的行是If rs.RecordCount = 0 then
我知道代码肯定错误百出,大家都帮忙看看。我现在还不太懂怎么实现5个文本框+combo的任意组合查询,希望知道的朋友能不吝赐教
要使用recordcount属性是有前提的
Set rs = ExecuteSQL(sqlStr, msgText)
改为
Set rs = ExecuteSQL(sqlStr, 1)
试一下