how to select customers from a table

  • Thread starter Thread starter kallen via AccessMonster.com
  • Start date Start date
K

kallen via AccessMonster.com

I have a table that multiple users will use at the same time. It basically
consists of a list of customers names. I want to be able to select multiple
customers from that table when I am creating a new "project" so when I print
out the project report, the "customers" list on the report is the customers
that were selected from that table. How can I do that without locking the
table from other users?
 
I have a table that multiple users will use at the same time. It basically
consists of a list of customers names. I want to be able to select multiple
customers from that table when I am creating a new "project" so when I print
out the project report, the "customers" list on the report is the customers
that were selected from that table. How can I do that without locking the
table from other users?
Click to expand...

Since each Project involves many Customer, and presumably a Customer might be
involved in many Projects, you need... another table! ProjectInvolvement,
let's say, with fields for the ProjectID and the CustomerID. You could fill it
using a subform on the Projects form, using ProjectID as the master/child link
field, and a combo box to select the customer. The report would be based on a
query linking all three tables, to pick up the project information from the
project table, and the customer name (and other individual data) from the
Customers table.

John W. Vinson [MVP]
 
Structure your tables as John describes and base the report on a query
joining them. To select multiple customers and open the report create an
unbound form with a multi-select list box of all customers with CustomerID as
the hidden bound column, but showing the customers by name. To do this set
up the list box like this:

For its RowSource property:

SELECT CustomerID, Customer FROM Customers ORDER BY Customer;

For other properties:

Name: lstCustomers
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, CustomerID, column, so only the
name shows.
MultiSelect: Simple or Extended as preferred.

Add a button to the form to open the report, called rpt Customers in this
example, with the following in its Click event procedure:

Dim varItem As Variant
Dim strCustomerIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstCustomers

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

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

strCriteria = "[CustomerID] In(" & strCustomerIDList & ")"

DoCmd.OpenReport "[rptCustomers]", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No Customers Selected", vbInformation, "Warning"
End If

The above will open the report in print preview, so you might want a second
button on the form with slightly amended code ( simply remove the
'View:=acViewPreview, _' line) to print the report.

Ken Sheridan
Stafford, England
 
thank you very much, the both of you for the suggestions. I will try them as
soon as I can and give some feedback. It is obviously part of a larger
database, one that I hope will be easier to make as time goes by.
 
Back
Top