Question

为什么rs.Open "select * from WSPdrawing, instore where " & Trim(sql), cn, adOpenDynamic 是错的

Private Sub Command1_Click()
sql = "drawingnumber='" & Trim(Text1.Text) & "'"
Call insearch(sql)
End Sub

Private Sub insearch(ByVal sql As String)
Set cn = New Connection
cn.CursorLocation = adUseClient
cn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\jxc.mdb;"
Set rs = New adodb.Recordset
rs.LockType = adLockOptimistic
If Len(Trim(sql)) <= 0 Then
rs.Open "select * from WSPdrawing,instore ", cn, adOpenDynamic
Else
rs.Open "select * from WSPdrawing, instore where " & Trim(sql), cn, adOpenDynamic
End If

Set DataGrid1.DataSource = rs
End Sub

如上
rs.Open "select * from WSPdrawing, instore where " & Trim(sql), cn, adOpenDynamic
这句话出现"特定字段drawingnumber可以参考SQL语句中FROM子句列表中的多个表,但是我本来就是想要drawingnumber可以参考这两张表,应该怎么办

Answer 1

两张表可以，没 问题。看问题应该是你的where后面的有错误，你把WHERE后面的重新处理一下。

Answer 2

该两个地方试试：

sql = "'" & Trim(Text1.Text) & "'"

rs.Open "select * from WSPdrawing, instore where WSPdrawing.drawingnumber=instore.drawingnumber and WSPdrawing.drawingnumber=" & sql,cn, adOpenDynamic

追问

我的表如下,比如我在Text1.Text中输入WS10000000,就把这两张表中的所有WS1000000都找出来,包括后面的信息,信息不需要合并 为一条,有多少就显示多少条,刚刚你的那个我也试过了,达不到这个效果,

 

 

追答

不合并为一条？上下显示？
那麼：
sql = "'" & Trim(Text1.Text) & "'"

rs.Open "select * from WSPdrawing where WSPdrawing.drawingnumber=" & sql & " union all select * from instore where instore.drawingnumber=" & sql ,cn, adOpenDynamic

用union将两个查询结果合起来，注意两个表结构应该一样才行，不一样的话就不能select *，需要写成select field1,field2...
希望你能成功！