请问vba引用sql查询是哪里错了？

欧阳空竹　发表于：2015-11-23 17:22:24 　

请问老师，下述sql到底错在哪了？是定义的变量格式不对？请指教。。

-------------------------------------------------------------------------------------------------------------------------------

Dim strhtmc1 As String

    Dim strhtmc2 As String
    Dim strhtmc3 As String
    Dim strhtmc4 As String

    htmc1 = " SELECT tblkh.khjc FROM tblkh WHERE (((tblkh.khID)= "me.htdx")); "
    htmc2 = " SELECT tblxm.xmjc FROM tblxm WHERE (((tblkh.khID)= "me.xmID")); "

htmc3 = " SELECT tblyt.ytmc FROM tblyt WHERE (((tblyt.ytmc)= "me.ytID")); "

htmc4 = " SELECT tblyw.ywnr FROM tblyw WHERE (((tblyt.ytmc)= "me.ytID")); "

htmc= htmc1 & htmc2 & htmc3 & htmc4

-------------------------------------------------------------------------------------------------------------------------------

Top

cspa 发表于：2015-11-23 18:56:46

如果要引用控件变量应这样写：

htmc1 = " SELECT tblkh.khjc FROM tblkh WHERE (((tblkh.khID)= '" & me.htdx & "')); " '当me.htdx为字符型变量时

htmc1 = " SELECT tblkh.khjc FROM tblkh WHERE (((tblkh.khID)= #" & me.htdx & "#)); " '当me.htdx为日期型变量时

htmc1 = " SELECT tblkh.khjc FROM tblkh WHERE (((tblkh.khID)= " & me.htdx & ")); " '当me.htdx为数字型变量时

另

htmc= htmc1 & htmc2 & htmc3 & htmc4 这样将4个SQL语句合在一起是不行的。

Top

欧阳空竹发表于：2015-11-23 22:49:43

cspa老师，非常感谢您的回复，按您提点的我已经修改，但您说的最后一点，我改成下面红色字体的样子，本以为可实现要求，但现实是残酷的

'-----------------------------下述定义变量----------------------------------

    Dim strhtmc1 As String
      Dim strhtmc2 As String
      Dim strhtmc3 As String
      Dim strhtmc4 As String

Dim strhtmc0 As String

   ‘-----------------------------上述定义变量-----------------------------------
    strhtmc1 = " SELECT tbl客户.客户简称 FROM tbl客户 WHERE (((tbl客户.客户ID)='" & Me.合同对象 & "')); "
    strhtmc2 = " SELECT tbl项目.项目简称 FROM tbl项目 WHERE (((tbl项目.项目ID)='" & Me.项目ID & "')); "
    strhtmc3 = " SELECT tbl业态.业态名称 FROM tbl业态 WHERE (((tbl业态.业态ID)='" & Me.业态ID & "')); "
    strhtmc4 = " SELECT tbl业务.业务内容 FROM tbl业务 WHERE (((tbl业务.业务ID)='" & Me.业务ID & "')); "

    strhtmc0 = strhtmc1 & strhtmc2 & strhtmc3 & strhtmc4

me.合同名称 = strhtmc0

这段代码想要实现的是：四个组合框字段的值【Me.合同对象、Me.项目ID、Me.业态ID、Me.业务ID，（这些字段是各相关表的编号）】，上述蓝色代码是根据各自的编号，通过查询取相应的字段值合同对象、项目简称、业态名称、业务内容（定义的变量strhtmc1、strhtmc2、strhtmc3、strhtmc4），把这些变量连起来形成【合同管理表】另一个字段的值（me.合同名称）。