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
"(E-Mail Removed)" wrote:
> On May 15, 2:45 am, Ken Sheridan
> <KenSheri...@discussions.microsoft.com> wrote:
> > 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?
>
Similar Threads
