Filtered records as source for appending records to a table

[LanceWilliams2418]

Here is the business scenario:
We know that every employee at every office will get a MonitorA. So
instead of navigating to each employee record in the database and then
entering a "MonitorA" record as one of their order items, I want the
data entry person to filter the Employee table (via form/query) and
then click a button that will copy and append the EmployeeIDs from the
filtered recordset to the Orders table as new records. (Subsequent
update queries will update those new records to include "MonitorA" in
the Item field and a value of "1" in the Quantity field.)
Will a simple query do the trick - one that includes the filtered
recordset as the query criteria? (sounds awkward).
Or will it take some code, with a loop? What would this solution look
like?
Any advice is appreciated.

 

[Ken Sheridan]

I'd suggest using an unbound dialogue form with a multi-select list box
(lstEmployees) for the employees, a text box (txtItem) for the item, a text
box (txtQuantity with a DefaultValue property of 1) for the quantity and a
'Confirm' button.

The list box would be set up like so:

RowSource: SELECT EmployeeID, FirstName & " " & LastName AS FullName
FROM tblEmployees ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
MultiSelect: Either 'simple' or 'extended' as desired.

The code for the button's Click event procedure would go like this:

Dim dbs as DAO.Database
Dim varItem As Variant
Dim strEmployeeList As String
Dim strCriteria As String
Dim strSQL As String
Dim ctrl As Control

Set dbs = CurrentDB
Set ctrl = Me.lstEmployees

If IsNull(Me.txtItem) Then
MsgBox "No item entered.", vbExclamation, "Invalid Operation"
Exit Sub
End If

If IsNull(Me.txtQuantity) Then
MsgBox "No quantity entered.", vbExclamation, "Invalid Operation"
Exit Sub
End If

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strEmployeeList = strEmployeeList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strEmployeeList = Mid(strEmployeeList, 2)

strCriteria = "EmployeeID IN(" & strEmployeeList & ")"

strSQL = "INSERT INTO Orders(Item, Quantity, EmployeeID) " & _
"SELECT """ & Me.txtItem & """, " & Me.txtQuantity & ", " & _
"EmployeeID FROM Employees WHERE " & strCriteria

dbs.Execute strSQL

Else
MsgBox "No employees selected", vbInformation, "Warning"
End If

This would insert rows into the Orders table with values for each selected
employee from the list, and the item and quantity entered. NB: I've not
included any error handling.

Ideally you'd have a table Items listing all items so that, instead of the
txtItem control you could have a cboItems combo box to select an item. The
Items table would probably have a numeric (e.g. an autonumber) ItemID as its
primary key, and a corresponding ItemID column of long integer number data
type as a foreign key. The code to build the SQL statement would then be
amended as follows:

strSQL = "INSERT INTO Orders(Item, Quantity, EmployeeID) " & _
"SELECT " & Me.cboItem & ", " & Me.txtQuantity & ", " & _
"EmployeeID FROM Employees WHERE " & strCriteria

Ken Sheridan
Stafford, England

 

[rake4444]

I'd suggest using an unbound dialogue form with a multi-select list box
(lstEmployees) for the employees, a text box (txtItem) for the item, a text
box (txtQuantity with a DefaultValue property of 1) for the quantity and a
'Confirm' button.

The list box would be set up like so:

RowSource:     SELECT EmployeeID, FirstName & " " & LastName AS FullName
FROM tblEmployees ORDER BY LastName, FirstName;

BoundColum:    1
ColumnCount:   2
ColumnWidths: 0cm;8cm
MultiSelect:       Either 'simple' or 'extended' as desired.

The code for the button's Click event procedure  would go like this:

    Dim dbs as DAO.Database
    Dim varItem As Variant
    Dim strEmployeeList As String
    Dim strCriteria As String
    Dim strSQL As String
    Dim ctrl As Control

    Set dbs = CurrentDB
    Set ctrl = Me.lstEmployees

    If IsNull(Me.txtItem) Then
        MsgBox "No item entered.", vbExclamation, "Invalid Operation"
        Exit Sub
    End If

    If IsNull(Me.txtQuantity) Then
        MsgBox "No quantity entered.", vbExclamation, "Invalid Operation"
        Exit Sub
    End If

    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
            strEmployeeList = strEmployeeList & "," & ctrl.ItemData(varItem)
        Next varItem

        ' remove leading comma
        strEmployeeList = Mid(strEmployeeList, 2)

        strCriteria = "EmployeeID IN(" & strEmployeeList & ")"

        strSQL = "INSERT INTO Orders(Item, Quantity, EmployeeID)" & _
            "SELECT """ & Me.txtItem & """, "  & Me.txtQuantity & ", " & _
             "EmployeeID FROM Employees WHERE " & strCriteria

        dbs.Execute strSQL

    Else
        MsgBox "No employees selected", vbInformation, "Warning"
    End If

This would insert rows into the Orders table with values for each selected
employee from the list, and the item and quantity entered.  NB: I've not
included any error handling.

Ideally you'd have a table Items listing all items so that, instead of the
txtItem control you could have a cboItems combo box to select an item.  The
Items table would probably have a numeric (e.g. an autonumber) ItemID as its
primary key, and a corresponding ItemID column of long integer number data
type as a foreign key.  The code to build the SQL statement would then be
amended as follows:

        strSQL = "INSERT INTO Orders(Item, Quantity, EmployeeID)" & _
            "SELECT " & Me.cboItem & ", "  & Me.txtQuantity & ", " & _
             "EmployeeID FROM Employees WHERE " & strCriteria

Ken Sheridan
Stafford, England

Thank you so much for putting in this time. Let me clarify. I
misstated the business scenario - I should have said: "We know that
some subset of employees at some subset of offices will get MonitorA."
The reason why I envisioned the filtered form, rather than a list box,
is because we have over 5,000 employees at over 300 offices. If we are
going to send, say, 2,000 CPUs to employees at 100 offices, then I
would rather my data entry person have the ability to filter on any
number of criteria, e.g., Region, City, OfficeID, Cost Center,
CustomerType, to narrow down the recordset. The list box approach
makes us find and select the customers, whereas the filter approach
finds them for us.
I have already built the "Filter Customer" form and the "Pick Item
from Combobox" form - now I just need to combine the CustomerID with
the picked Item and append those combinations to the Orders table,
with Quantity = 1 (no more, no less). Does that sound reasonable?

 

[Ken Sheridan]

You could still use an unbound list box as all that's necessary is to
restrict the result set of the list box's RowSource query by referencing
controls on the form as parameters, testing both for equality and OR IS NULL
to make the selections optional. You'd just need to add some code before
that which I posted, to requery the list box and select all its rows. The
advantage I'd see in that is that the user can then 'fine tune' the
selections by de-selecting one or rows in the list box to exclude particular
employees if necessary.

However, by filtering a bound form you can iterate through a clone of its
underlying recordset and insert a row into Orders for each row in the
recordset. Suitable code would be along these lines:

Const NOCURRENTRECORD = 3021
Dim dbs As DAO.Database
Dim rst As Object
Dim lngEmployeeID As Long
Dim strSQL As String

Set dbs = CurrentDb
Set rst = Me.Recordset.Clone

With rst
On Error Resume Next
.MoveFirst
Select Case Err.Number
Case 0
' no error
On Error Goto 0
Do While Not .EOF
lngEmployeeID = rst.Fields("EmployeeID")

strSQL = "INSERT INTO Orders(Item, Quantity, EmployeeID) " & _
"VALUES(""" & Me.cboItem & """,1," & lngEmployeeID & ")"

dbs.Execute strSQL
.MoveNext
Loop
Case NOCURRENTRECORD
MsgBox "No records match filter.", vbExclamation, "Warning"
Case Else
' unknown Error
MsgBox Err.Description, vbExclamation, "Error"
End Select
End With

or, if the bound column of cboItem is a numeric ItemID column rather than a
text column:

strSQL = "INSERT INTO Orders(ItemID, Quantity, EmployeeID) " & _
"VALUES(" & Me.cboItem & ",1," & lngEmployeeID & ")"

Ken Sheridan
Stafford, England