Access 2010 Pre Populating Monthly Sales Form Records With All Active Employees

Question

Using Access 2010 and relatively new to Access in general. For simplicity say I have the following two tables:

Tbl 1: Employee_Info (Fields: Employee_ID (primary key), Employee_Name, & Employee_Status (active, inactive, termed))

Tbl 2: Monthly_Sales (Fields: Month/Year, Sales, & Employee_ID (foreign key))

Every month a member of our team has to enter in the monthly sales for all active employees and I would like to design a form where all active employees are displayed as records and the person doing the data entry only needs to enter the month and year and sales. Something similar to this:

Date: User inputs date here once and pre-populates all the records below

Column 1: Employee_ID: All active employee IDs are displayed

Column 2: Sales:These fields are blank and user enters in the monthly sales.

I have looked all over the internet and have been unable to find a solution to this problem. I don't think it is as simple as using an append query but again I am relatively new to access. Thanks in advance for your help.

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2016-10-14T20:18:32

You can use the following code to add records for a month... just change table / field names to match your DB. Your table design should prevent duplicate Employee_ID and YearMonth combinations. If so, code will ignore errors if someone runs code twice for same month. If not, you need method of insuring no dups are added.

Option Compare Database
Option Explicit

Function Create_New_Rows()
Dim strSQL          As String
Dim i               As Integer
Dim iAdd            As Integer
Dim iDuration       As Integer
Dim lCampaignID     As Long
Dim dbs             As DAO.Database
Dim rsIN            As DAO.recordSet
Dim rsOT            As DAO.recordSet
Dim DateRange       As Date
Dim dStart          As Date
Dim dEnd            As Date
Dim InDate          As String

    On Error GoTo Error_Trap

    InDate = InputBox("Input the Year and Month to process. i.e. 201610", "Enter YYYYMM", _
        Format(YEAR(Date) & month(Date), "000000"))

    ' Add some validation to insure they enter a proper month and year!!


    dStart = Mid(InDate, 5, 2) & "/01/" & left(InDate, 4)
    dEnd = DateSerial(YEAR(dStart), month(dStart) + 1, 0)

    Set dbs = CurrentDb

    strSQL = "SELECT Employee_ID, Employee_Status " & _
                "FROM Table1 " & _
                "Where Employee_Status = 'active';"

    Set rsIN = dbs.OpenRecordset(strSQL)
    Set rsOT = dbs.OpenRecordset("Table2")

    If rsIN.EOF Then
        MsgBox "No Active Employees found!", vbOKOnly + vbCritical, "No Records"
        GoTo Exit_Code
    Else
        rsIN.MoveFirst
    End If

    Do While Not rsIN.EOF
        DateRange = dStart
        Do
            With rsOT
                .AddNew
                !Employee_ID = rsIN!Employee_ID
                !MonthYear = Format(YEAR(DateRange) & month(DateRange), "000000")
                .Update
            End With
            DateRange = DateAdd("d", 1, DateRange)
            If DateRange > dEnd Then
                Exit Do
            End If
        Loop

        rsIN.MoveNext
    Loop

Exit_Code:
    If Not rsIN Is Nothing Then
        rsIN.Close
        Set rsIN = Nothing
    End If
    If Not rsOT Is Nothing Then
        rsOT.Close
        Set rsOT = Nothing
    End If
    dbs.Close
    Set dbs = Nothing

    MsgBox "Finished"

    Exit Function
Error_Trap:
    Debug.Print Err.Number & vbTab & Err.Description & vbCrLf & "In:   Create_New_Rows"
    ' Ignore if duplicate record
    If Err.Number = 3022 Then
        Resume Next
    End If

    MsgBox Err.Number & vbTab & Err.Description & vbCrLf & "In: Create_New_Rows"
    Resume Exit_Code
    Resume
End Function

Access 2010 Pre Populating Monthly Sales Form Records With All Active Employees

1 Answers