使用函数从网络ID设置字段中的访问VBA形式

Question

我已经在以下代码进行了一段时间。一直试图弄清楚如何根据所有者字段设置用户ID，该字段基于抓取人员网络ID的功能。这是完成的

我不断遇到的问题是，我在if语句行上不断遇到类型不匹配错误。我还应该提到这是作为从表格的复选框完成的。

这是代码：

Private Sub OkButton_Click()
Dim c As MSForms.Control
Dim StrSQL As String
For Each c In Me.Controls
   If TypeOf c Is MSForms.CheckBox Then
    If c Then
     Select Case c.Name
        Case "CheckBox1"
            'Monday
            Hide
            CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [Need Help], [DateCreated], [Owner]) VALUES ('Change Notice', 'Daily Task', '(4) Ginny''s', '(1) Hot!', '0', DateAdd('d',(8-Weekday(Date(),2)) mod 7,Date()), 'No', Date(), '" & Getowner() & "')")
            GetUser
    Case Else
        End Select
       End If
      End If
    Next c
End Sub
Function GetOwner()
  GetOwner = Environ("USERNAME")   
End Function
Function GetUser()
Dim StrSQL As String
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("tblTasks")
Set rs2 = CurrentDb.OpenRecordset("tblTasks")
rs2.MoveLast
With rs2
  If Environ("USERNAME") = "12345" Then
    .Edit
    ![User ID] = "Name"
    .Update
  End If
End With
rs1.Close 'Close the recordset
Set rs1 = Nothing 'Clean up
rs2.Close 'Close the recordset
Set rs2 = Nothing 'Clean up
Forms![frmTasks].Form.Requery
Forms![frmTasks].Form.Refresh
End Function

任何帮助或向正确的方向推动将不胜感激！

Answer 1

有一个旧的错误，您不能只期望复选框的值(其默认属性(作为布尔值。

所以尝试：

    If c.Value = True Then

另外，您在这里错过了一句话：

CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [User ID], [Need Help], [DateCreated], [Owner]) VALUES ('Change Notice', 'Daily Task', '(4) Ginny''s', '(1) Hot!', '0', DateAdd('d',(8-Weekday(Date(),2)) mod 7, Date()), " & GetUser() & ", 'No', Date(), '" & GetOwner() & "')")

Answer 2

解决了问题。必须使用记录集来使其正常工作。

Private Sub OkButton_Click()
Dim c As MSForms.Control
Dim StrSQL As String
For Each c In Me.Controls
   If TypeOf c Is MSForms.CheckBox Then
    If c Then
     Select Case c.Name
        Case "CheckBox1"
            'Monday
            Hide
            CurrentDb.Execute ("INSERT INTO tblTasks ([Task Name], [Task Description], [Company], [Priority], [Status], [DueDate], [Need Help], [DateCreated], [Owner]) VALUES ('Change Notice', 'Daily Task', '(4) Ginny''s', '(1) Hot!', '0', DateAdd('d',(8-Weekday(Date(),2)) mod 7,Date()), 'No', Date(), '" & Getowner() & "')")
            GetUser
    Case Else
        End Select
       End If
      End If
    Next c
End Sub
Function GetOwner()
  GetOwner = Environ("USERNAME")   
End Function
Function GetUser()
Dim StrSQL As String
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("tblTasks")
Set rs2 = CurrentDb.OpenRecordset("tblTasks")
rs2.MoveLast
With rs2
  If Environ("USERNAME") = "12345" Then
    .Edit
    ![User ID] = "Name"
    .Update
  End If
End With
rs1.Close 'Close the recordset
Set rs1 = Nothing 'Clean up
rs2.Close 'Close the recordset
Set rs2 = Nothing 'Clean up
Forms![frmTasks].Form.Requery
Forms![frmTasks].Form.Refresh
End Function