在Access VBA数组中使用ADO更新

Question

我有31个文本框填充了来自Calendar Table的ADO，如下所示：

Private Function FillDates()
    Dim cnn As ADODB.Connection
    Dim ssql As String
    Dim rst As ADODB.Recordset
    Set cnn = CurrentProject.Connection
    Dim i As Integer
    Dim Records As Integer
    ssql = "SELECT RoomAvailabilityId, Availability FROM RoomAvailability  WHERE Month(AvailabilityDate)=Month(Now()) AND RoomTypesId=1"
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open ssql, cnn
    Records = rst.RecordCount
    For i = 1 To Records
     Me("idtext" & i).Value = rst.Fields!RoomAvailabilityId
     Me("Text" & i).Value = rst.Fields!Availability 
     rst.MoveNext
    Next i
         rst.Close
        Set rst = Nothing
End Function

这是实际代码的简化版本。当一个月由30天或29/28天组成时，实际代码会隐藏文本框。

所以我现在有两个值存储在我的网格中，由上面的文本框组成。

现在，我想通过单击按钮来更新名为Availability (Number - Long Integer Datatype)的表字段，但我无法使用此方法。

你能建议一下怎么做吗？这是我开始的非工作代码：

Private Sub cmdUpdatetxt_Click()
Dim cn As ADODB.Connection '* Connection String
Dim oCm As ADODB.Command '* Command Object
Dim iRecAffected As Integer
Set cn = CurrentProject.Connection
Dim i As Integer
For i = 1 To 31
AvailableRooms = Me("txt" & i).Value
AvailableRoomsId = Me("idtext" & i).Value
Next i
Set oCm = New ADODB.Command
oCm.ActiveConnection = cn
oCm.CommandText = "Update RoomAvailability Set Availability ='" & AvailableRooms & "' WHERE RoomAvailabilityId = '" & AvailableRoomsId & "' AND Month(RoomAvailability.AvailabilityDate) = '" & cboMonthYear.Value & "' "
oCm.Execute iRecAffected
If iRecAffected = 0 Then
MsgBox "Nessun Utente Inserito"
End If
If cn.State <> adStateClosed Then
cn.Close
If Not oCm Is Nothing Then Set oCm = Nothing
If Not cn Is Nothing Then Set cn = Nothing
End If
End Sub

非常感谢你提前

Answer 1

根据Nathan_Sav的建议，以下是工作代码：

  Private Sub cmdUpdatetxt_Click()

    On Error GoTo ErrorTrap

    Dim i As Integer
    For i = 1 To 31
    Dim cn As ADODB.Connection '* Connection String
    Dim oCm As ADODB.Command '* Command Object
    Dim iRecAffected As Integer
    Set cn = CurrentProject.Connection
    AvailableRooms = Me("txt" & i).Value
    AvailableRoomsId = Me("idtext" & i).Value
    Set oCm = New ADODB.Command
    oCm.ActiveConnection = cn
    oCm.CommandText = "Update RoomAvailability Set Availability =" & AvailableRooms & " WHERE RoomAvailabilityId = " & AvailableRoomsId & " AND Month(RoomAvailability.AvailabilityDate) = '" & cboMonthYear.Value & "' "
    oCm.Execute iRecAffected
    If iRecAffected = 0 Then
    MsgBox "Nessun Utente Inserito"
    End If
    If cn.State <> adStateClosed Then
    cn.Close
    If Not oCm Is Nothing Then Set oCm = Nothing
    If Not cn Is Nothing Then Set cn = Nothing
    End If
    Next i
    Exit Sub
ErrorTrap:
    MsgBox (Err.Description)
End Sub

祝你一天愉快