Results 1 to 2 of 2
  1. #1
    djblois is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    6

    Change Record in Single Form

    I have a form that allows the user to select the date at the top. As the user changes the date, it should change the record to reflect. If there is a record for that date already then switch to that record, but if no record for that date create a new one. The table is set to not allow duplicates on the date column. Here is the code I have on the Form_Load event and the respective subs that I call:



    Code:
    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
    And this is the code for when a user changes the date:

    Code:
    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
    I have tried a few ways to do this.

    1) I tried completely in code:

    Code:
    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
    How can I do this on a single form? I know how to do it if I add a subform but not if all the fields are in my single form.

    Unfortunately, this tries to add a new record when I load up the form.

    2) I tried doing it in the query also

    Code:
    SELECT tblShiftRecap.ShiftID, tblShiftRecap.MQFStartTime, tblShiftRecap.MQFFinishTime, tblShiftRecap.MQFLoadingFinishTime, tblShiftRecap.MQFCases, tblShiftRecap.MQFRoutes, tblShiftRecap.MQFStops, tblShiftRecap.MQFTimeNotes, tblShiftRecap.NYPStartTime, tblShiftRecap.NYPFinishTime, tblShiftRecap.NYPLoadingFinishTime, tblShiftRecap.NYPCCases, tblShiftRecap.NYPRoutes, tblShiftRecap.NYPStops, tblShiftRecap.NYPTimeNotes, tblShiftRecap.LaborHoursCooler, tblShiftRecap.LaborHoursDry, tblShiftRecap.LaborHoursFreeze, tblShiftRecap.PalletsShipped, tblShiftRecap.POsReceived, tblShiftRecap.CutsMarkouts, tblShiftRecap.SafetyIncidents, tblShiftRecap.OrdersAudited, tblShiftRecap.SpotCheckedPallets, tblShiftRecap.InternalErrors, tblShiftRecap.SPVRObservations, tblShiftRecap.BuildingAndGrounds, tblShiftRecap.Equipment, tblShiftRecap.Trainees, tblShiftRecap.Notes, tblShiftRecap.ShiftDate
    FROM tblShiftRecap
    WHERE (((tblShiftRecap.ShiftDate)=GetDateOfRecord()));
    and the functiont that the SQL calls:

    Code:
    Public Function GetDateOfRecord()
    
        GetDateOfRecord = DateOfRecord
    
    End Function

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I'd need the DB to work my way through the code. Can you post it here?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export Single record from a form to PDF
    By ManuelLavesa in forum Import/Export Data
    Replies: 2
    Last Post: 09-10-2015, 05:47 PM
  2. Replies: 4
    Last Post: 08-29-2015, 07:38 AM
  3. Replies: 3
    Last Post: 08-02-2015, 12:42 AM
  4. Replies: 1
    Last Post: 04-11-2015, 10:31 AM
  5. Export single record from a form to PDF
    By GraemeG in forum Programming
    Replies: 1
    Last Post: 04-10-2011, 01:33 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums