在Access 2016中更改单一表单上的记录

Question

我有一个表单，允许用户在顶部选择日期。当用户更改日期时，它应该更改记录以反映。如果该日期已有记录，则切换到该记录，但如果该日期没有记录，则创建一个新记录。该表设置为不允许在日期列上重复。这是我在Form_Load事件上的代码，以及我调用的相应子：

Private Sub Form_Load()
    Me.cobYear.Value = Year(Date)
    Me.cobMonth.Value = Month(Date)
    DaysChange Me
    Me.cobDate.Value = Day(Date)
    UpdateDate Me
    DoCmd.Maximize
End Sub
Sub DaysChange(objForm As Form)
    Dim i As Integer
    Dim DaysInMonth As Integer
    Dim LeapDay As Integer
    LeapDay = 0
    If (Int(objForm.cobYear / 400) = (objForm.cobYear / 400)) Or ((Int(objForm.cobYear / 4) = (objForm.cobYear / 4)) And Not (Int(objForm.cobYear / 100) = (objForm.cobYear / 100))) Then
        LeapDay = IIf(objForm.cobMonth = 2, 1, 0)
    End If
    DaysInMonth = DLookup("DaysInMonth", "tblMonths", "MonthNumber =" & objForm.cobMonth) + LeapDay
    For i = 1 To DaysInMonth
        objForm.cobDate.AddItem Item:=i
    Next i
End Sub
Sub UpdateDate(objForm As Form)
    If Not objForm.cobDate = "" And Not objForm.cobMonth = "" And Not objForm.cobYear = "" Then
        objForm.tbDate.Value = DateSerial(objForm.cobYear, objForm.cobMonth, objForm.cobDate)
        DayOfWeek = Weekday(objForm.tbDate.Value, 2)
        'Me!subfrmDispatchSheet.Form.cobRouteID.Requery
        objForm.lblDayOfWeek.Caption = WeekdayName(Weekday(objForm.tbDate.Value))
        DateOfRecord = objForm.tbDate.Value
    End If
End Sub

这是用户更改日期时的代码：

Private Sub cobDate_Change()
    UpdateDate Me
    ChangeRecord
End Sub
Private Sub cobMonth_Change()
    DaysChange Me
    UpdateDate Me
    ChangeRecord
End Sub
Private Sub cobYear_Change()
    DaysChange Me
    UpdateDate Me
    ChangeRecord
End Sub

我试过几种方法来做到这一点。

1( 我完全尝试了代码：

Private Sub ChangeRecord()
    If DCount("ShiftDate", "tblShiftRecap", "ShiftDate =" & Me.tbDate.Value) = 0 Then
    Else
        Me.tbShiftID.Value = DLookup("ShiftID", "tblShiftRecap", "ShiftDate =" & Me.tbDate.Value)
    End If
    Me.Requery
End Sub

如何在一张表格上完成此操作？如果我添加一个子表单，但如果所有字段都在我的单个表单中，我知道如何做到这一点。

不幸的是，当我加载表单时，它试图添加一个新记录。

2( 我试着在查询中也做

SELECT tblShiftRecap.ShiftID, tblShiftRecap.MQFStartTime
FROM tblShiftRecap
WHERE (((tblShiftRecap.ShiftDate)=GetDateOfRecord()));

以及SQL调用的函数：

Public Function GetDateOfRecord()
    GetDateOfRecord = DateOfRecord
End Function

Answer 1

如果我答对了你的问题，你想根据条件导航到当前表单中的某个记录

要浏览表单，最简单的方法是打开记录集克隆，使用.FindFirst，然后将表单上的当前记录更改为找到的记录：

Dim rs As Recordset
Set rs = Me.RecordsetClone 'Load form records into recordset clone
rs.FindFirst "ShiftDate = " & Format(DateOfRecord, "#yyyy-mm-dd#") 'Navigate to date
If Not rs.NoMatch 'If there's a matching record
    Me.Bookmark = rs.Bookmark 'Navigate to it
End If