编译错误的MS Access SQL [英] Compile error MS Access SQL

问题描述

我得到一个编译错误：参数不可选 基本上，在这code，我试图做一个四周的表格样式自动分配。当code被调用，它开始于 AssignNullProjects（），当我得到的错误， GetNextAssignee 是亮点部分＆放大器; GetNextAssignee＆安培;

我真的不知道如何解决它，这里是我下面的全code：

 公共功能AssignNullProjects（）只要

    昏暗的分贝作为DAO.DATABASE
    昏暗的RS作为dao.Recordset
    昏暗STRSQL作为字符串

    设置DB = CurrentDb
    STRSQL =SELECT CFRRRID，[程序]，[语言] FROM CFRRR WHERE assignedto IS NULL
    设置RS = db.OpenRecordset（STRSQL，dbOpenDynaset）
    如果没有rs.BOF而不是RS.EOF然后
        虽然没有RS.EOF
            STRSQL =UPDATE CFRRR SET assignedto =＆放大器; GetNextAssignee和放大器; ，assignedby =与＆amp; [表格] [CFRRR] [assignedby]放大器;！ ，Dateassigned =＃与＆amp;现在和放大器; ＃，actiondate =＃与＆amp;现在和放大器; ＃，Workername =与＆amp; _
                              [表格] [CFRRR] [assignedto]放大器;！ ，WorkerID =与＆amp; [表格] [CFRRR] [assignedto]放大器;！ WHE​​RE CFRRRID =＆放大器; RS！CFRRRID

            db.Execute STRSQL，dbFailOnError
            rs.MoveNext
        蜿蜒
    结束如果

    rs.Close
    db.Close
    设置RS =什么
    设置分贝=什么

端功能

公共功能GetNextAssignee（程序作为字符串，语言作为字符串）只要
返回用户名作为长整型的最低[TS]值，
'和更新一样[TS]递增1。

    昏暗的分贝作为DAO.DATABASE
    昏暗的RS作为dao.Recordset
    昏暗STRSQL作为字符串

    设置DB = CurrentDb
    STRSQL =SELECT TOP 1 WorkerID从考勤WHERE [程序] LIKE*＆放大器;计划和放大器; *和[语言] ='＆放大器;语言和放大器; '和[状态] ='＆放大器;可与放大器; 'ORDER BY TS ASC
    设置RS = db.OpenRecordset（STRSQL，dbOpenDynaset）
    如果没有rs.BOF而不是RS.EOF然后
        发现旁边受让人，更新日期/时间戳记
'STRSQL =UPDATE tblUser SET TS =＆放大器; DMAX（[TS]，tblUser）+ 1安培; WHE​​RE [WorkerID] =安培; RS！workerid
        STRSQL =UPDATE出席SET TS =＆放大器; DMAX（[TS]，出席）+ 1和; WHE​​RE [WorkerID] =安培; RS！workerid
        db.Execute STRSQL，dbFailOnError
        GetNextAssignee = RS！workerid
    其他
        现场TS没有值的所有记录！
        code调用这个函数应该检查返回0指示错误。
        GetNextAssignee = 0
    结束如果

    rs.Close
    db.Close
    设置RS =什么
    设置分贝=什么

端功能
 

解决方案

在调用此函数时，必须提供2个参数（字符串值）：

公共功能GetNextAssignee（程序作为字符串，语言作为字符串）只要

但是，建设你的更新语句时，你调用该函数，而无需提供必需的参数：

STRSQL =UPDATE CFRRR SET assignedto =＆放大器; GetNextAssignee和放大器; assignedby =

所以访问被抱怨说，那些（字符串）参数是不可选的 - 你必须提供给他们。您的code应该编译当你给函数2串这样的...

STRSQL =UPDATE CFRRR SET assignedto =＆放大器; GetNextAssignee（富，巴）及assignedby =

虽然code将与这些字符串编译，该功能可能不会返回你需要的结果。因此，在富的替代现实的价值和栏的。

I'm getting a compile error: "Argument not optional" Basically in this code, I'm trying to do a around the table style auto-assigning. When the code is called, it starts at AssignNullProjects() and when I get the error, GetNextAssignee is highlight in the part " & GetNextAssignee & "

I'm not really sure how to fix it, here's my full code below:

Public Function AssignNullProjects() As Long

    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim strSQL As String

    Set db = CurrentDb
    strSQL = "SELECT CFRRRID, [program], [language] FROM CFRRR WHERE assignedto Is Null"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rs.BOF And Not rs.EOF Then
        While Not rs.EOF
            strSQL = "UPDATE CFRRR SET assignedto = " & GetNextAssignee & ", assignedby = " & [Forms]![CFRRR]![assignedby] & ", Dateassigned = #" & Now & "#, actiondate = #" & Now & "#, Workername = " & _
                              [Forms]![CFRRR]![assignedto] & ", WorkerID = " & [Forms]![CFRRR]![assignedto] & " WHERE CFRRRID = " & rs!CFRRRID

            db.Execute strSQL, dbFailOnError
            rs.MoveNext
        Wend
    End If

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing

End Function

Public Function GetNextAssignee(program As String, Language As String) As Long
'   Returns UserID as a Long Integer with the lowest [TS] value,
'   and updates same [TS] by incremented with 1.

    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim strSQL As String

    Set db = CurrentDb
    strSQL = "SELECT TOP 1 WorkerID FROM attendance WHERE [Programs] LIKE '*" & program & "*' AND [Language] = '" & Language & "' AND [Status] = '" & Available & "' ORDER BY TS ASC"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rs.BOF And Not rs.EOF Then
        'Found next assignee, update date/time stamp
'        strSQL = "UPDATE tblUser SET TS = " & DMax("[TS]", tblUser) + 1 & " WHERE [WorkerID]= " & rs!workerid
        strSQL = "UPDATE attendance SET TS = " & DMax("[TS]", "attendance") + 1 & " WHERE [WorkerID]= " & rs!workerid
        db.Execute strSQL, dbFailOnError
        GetNextAssignee = rs!workerid
    Else
        'Field TS has NO VALUE FOR ALL RECORDS!
        'Code calling this function should check for a return of 0 indicating an error.
        GetNextAssignee = 0
    End If

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing

End Function

解决方案

When you call this function, you must supply 2 arguments (string values):

Public Function GetNextAssignee(program As String, Language As String) As Long

But when building your UPDATE statement, you call that function without supplying the required arguments:

strSQL = "UPDATE CFRRR SET assignedto = " & GetNextAssignee & ", assignedby = "

So Access is complaining that those (string) arguments are not optional --- you must supply them. Your code should compile when you give the function 2 strings like this ...

strSQL = "UPDATE CFRRR SET assignedto = " & GetNextAssignee("foo", "bar") & ", assignedby = "

Although the code would compile with those strings, the function would probably not return the result you need. So substitute realistic values in place of foo and bar.

这篇关于编译错误的MS Access SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！