how to select customers from a table

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?

-- 
Message posted via http://www.accessmonster.com

0
kallen
8/10/2007 11:22:45 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
682 Views

Similar Articles

[PageSpeed] 11

On Fri, 10 Aug 2007 23:22:45 GMT, "kallen via AccessMonster.com" <u6724@uwe>
wrote:

>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?

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]
0
John
8/11/2007 1:53:10 AM
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

"kallen via AccessMonster.com" wrote:

> 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?
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> 

0
Utf
8/11/2007 8:18:03 AM
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.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1

0
kallen
8/14/2007 6:03:32 PM
Reply:

Similar Artilces:

Combo box list selection based on earlier selection by user
HI, I have a form that has a combo box 1 (Product) that reads a the Product table and has a unique id attached to each product (RO#). There is a second combo box 2 that is RO#. Now what I would like to do is when a user selects a Product in combo box 1, the system should lookup the Product selected from combo box 1 and upload the corresponding RO# in the combo box 2 list. Now I am using both combo boxes as Unbound and in the combo box 2 I have the two columns Product and RO# - in the Criteria row of RO# I have the following: Like IIf([Forms]![xa_f_lookup]![RO#]=" ",[Forms]![x...

Run report on multi selected records.
I try to make a report for a custom entity that works like the Account Summary: let's say that it's Customer's Inquiry Summary for entity Customer's Inquiry I made the report so that I'm able to run the report on Customer's Inquiry Form. But when I select multi records in the list of Customer's Inquiry and run report, only one record is show in one page of the the report. I would like to have the selected records appear in the report with each record on each page. How can I do this? ...

Customize Appointment Form
I have customized an appointment form however there are two things I have been unable to do... 1. After the fields are filled in I wish to append via VBA two user defined fields to the system field "Location" 2. Is it possible to configure Outlook to show other fields in the calendar when the Appointment window is saved and closed? ...

Can't select objects in Excel 2007
I'm trying to create a form in Excel 2007, and although I can manipulate, copy & paste controls (check boxes, etc) I can't select multiple objects to align them, etc. I've checked on-line help (and have followed the instructions), and other websites, but no one seems to have indicated a problem. It is extremely cumbersome and I need a solution. Any ideas? Thanks in advance, Jim Berglund I know this is about a year too late, but it may help others who have stumbled across this page looking for similar help. On the Home tab, in the Editing group, click Find & Select....

Using Custom Views
Complete Excel noob here - If I've defined a custom view in one workbook, how can I apply it to another. It is not shown in the list. What I want to do: I have a workbook with columnar data similar to a check register. I have set up the formatting for display and print formatting, including headers & footers. This data is for a specific client but I want to save that formatting, both visual and print so that I can create new workbooks for other clients and apply the same formatting. I thought that by saving the custom view it would work but it is not seen outside of the workbo...

Lot Selection not Showing when I select PO on Receiving Entry
Usually, the Lot selection screen would pop up when I select a PO at the Line Detail of the Receivings Entry, now it does not! And GP will let items post without a lot number! It seems as if a setting has changed. Any ideas anyone? Any help is appreciated. Thanks, R Are you sure the item is marked to track lots? This wiould be set item by item on the Item Maintenance Options window. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com &q...

MS Access Customizing an Auto Number
Hi - I would like to create my own auto number. It should include: project acronym+date+#### Example: 65947WHB-CAE_042210-0001 I have a table set up that I can pull the project acronym from, the date would be the date entered into the database, the number would generate automatically. What is the best way to do this? Thanks for your help. April On Thu, 22 Apr 2010 14:08:03 -0700, April Klein <April Klein@discussions.microsoft.com> wrote: >Hi - > >I would like to create my own auto number. It should include: > >project acronym+date+###...

filling a control in a continuous form with a field from a table
I had posted this under "Access Reports" in error and received a response and responded. Please follow the dialog for an explaination of my question: Original Question: I have a form with the fields: date, client, atty, time, billable and description. All these fields update the Time Sheet Table. I want the atty field to automatically take the attorney's name from the table Attorney which contains one field called Atty. So for I can only figure out how to use a combo box to chose the Attorney table field. Response Received: Your combo box to select the at...

Excel 2003 is missing Built-In Custom Chart Types in Chart Wizard
I recently uninstalled and reinstalled Office 2003 and now all the built-in custom charts are missing from Excel! When creating a chart using the Chart Wizard, under the Custom Types tab, there is supposed to be a list of built-in charts, when the Built-In radio button is selected. Now, there are none. Does anyone know how to restore those built-in charts? I have tried reinstalling, and that does not work. Also, I could not find an option related to this using "Modify" in Add/Remove Programs. I have Macros that use those charts, and I also need those charts for certain rep...

Select contact then hangs
Hi, please help. I have a user that when she selects a contact using To: in a new email, after selecting 2, it closes outlook??. Spec is W2kSP4\office2k with all windows updates and office updates installed. Profile has been deleted and re-created, plus i have set up personal folders and moved contacts into there, and set that folder as a default, but makes no difference. Please help. ...

Updating Yes/No table field based after form input completed
Hi. I'm new to Access VBA, so I'm erring on the side of too much information rather than too little in the hopes of receiving a clearer answer from the community... I'm working on an Access app designed to track status to contract with our customer. Contractual requirements take the form of Contract Line Item Numbers, or "CLINs." Each CLINS table entry includes a description and total contract quantity for each CLIN. Also included in the CLINS table is an IS_COMPLETE (Yes/No) field to facilitate filtering reports elsewhere in the app. The form in question, tied to a...

Pivot Table 06-04-10
I have a pivot table based on an excel worksheet with five columns - Date, Call From (Names), To, Time, and Type. In the pivot table, i am using From for the rows. I also use From in the values area as i want to count the number of times the data in From appears - as there can be repeats. Next, I want to use Time as it relates to Call From. Time ranges from 0 to > 0. I want to specifically count how many 0s and greater than 0s. i thought i could filter for the data after adding the Time field to the data area - it allows a filter but the results do not change. I am using...

Micrososft RMS and customer
My client needs to be able to establish a customer (for example- a contractor) and be able to select certain items and establish special pricing for those items only and for that contractor only. When those items are brought up at the POS, those items are given special pricing. Can this be done in RMS? My research shows that you can give price levels OR a discount for a customer, but that you cant select specific items for that customer. Am I correct? Thanks- Andrea I think RR has an add on for that or one of the other add-on companies "Andrea" <Andrea@discussions.micros...

Selecting rows from various sheets
Hi all, I have several sheets with a few columns in each. One colum in each sheet has a unique value. Most of these unique values are th same in each sheet but not all. Is there any way that I can set up new sheet that will have the unique value in column 1 and th corresponding column values frommeach sheet beside it. For example Sheet 1 aa 1 1 1 bb 2 2 2 cc 3 3 3 Sheet 2 aa 4 4 cc 5 5 Result aa 1 1 1 4 4 bb 2 2 2 cc 3 3 3 5 5 Thanks alot, Sha -- sha ----------------------------------------------------------------------- shav's Profile: http...

custom command
How can I get the paste special "Paste Formulas" as a separate command on the Toolbar similar to "Paste Formats" and "Paste Values" ...

Inserting ID Number from Another Table
Hello, I have two tables. One stores "employee information" and another stores "time sheet information" for all the employees. The "Employees" table has a unique identifier for each employee (EmployeeID). The "Time Sheet" table also has a unique identifier for each time sheet entry (TimeSheetID). When the data entry person inputs time sheet information onto the "TimeSheet" table, she selects the employee from a combo box which combines the first and last name of the employee from the employee table. What I want to do is automatically inse...

select text in Calendar
I'm working on a calendar in Publisher. I'm adding text into a cell by spacing down 1 point, then typing in 8 points. Doing fine until April. At that time, I can only select cells (whole days). It will not let me select the text. Help is no help. Any hints out there. Did I accidentally select something to do this? Bonnie Have you added a picture to the cell or a shape? If you have send it to the back, make the cell no fill and try again. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "GoBonnieGo" <GoB...

macro to filter data based on selected data
Hi, I am looking for a macro that requests input of a reference code and then a date and then filters the data in my spreadsheet based on this criteria. I am using excel 2007. Any help with this will be greatly appreciated. You can probably record a macro while doing it manually and then clean it up and add input boxes to ask the question or input your variable in a cell and use that. If all else fails, If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the ...

Link select with a blast
I have Win 7 Home Premium, x64. Quite often, when I select a link, a mouse pointer changes to a palm with a blast. I understand, the palm is the standard mouse pointer for link select, but what is the blast? What it indicates? -- Ilya Zeldes Fort Myers, Florida Hi Ilya, I suspect that this is being caused by a tracking program. Run something like Ad-Aware and see if it indicates a tracking company. If so, you can delete it, however, it might prevent you from accessing that particular website. Dave "Ilya Zeldes" wrote in message news:7A6FF9B1-5512-44...

Creating a way to attach files in custom entity?
I'm looking for a way to be able to attach files to a custom entity in CRM v3. My client originally wanted to be able to have some sort of document repository in CRM, so that users can easily refer back to program docs and then email them to customers. We tried renaming the Sales Literature entity to Program Documents, because you can attach files to those records, but you can't send email from there. Then I thought maybe we could stick the docs in the Knoweledge Base, but every article is based on a template, and they simply want to attach what they already have to CRM and...

Custom Data on Receipts
Hi, I'm not sure if this is possible, but does anyone know of a way to display data from a custom table on a receipt? Thanks dave The custom data would need to be set to the Session.Variable(1-10) before PrintReceipt Hook is called. You can use a DLL addon to set your custom data. You need to be careful that no other addon is using the same variables. If the data is static then you can just modify the receipt and hard code the information into the body. Rob "Dave" <Dave@discussions.microsoft.com> wrote in message news:261BCAF0-E737-48CB-B46B-100E00A0AA00@microso...

No customer record exists for this customer ID
This message came up during the posting process. Why did SOP allow the invoice to be created? A few questions: 1. Does the Customer ID actually exist? 2. Is there a possibility that the customer record was physically removed, say with a T-SQL DELETE statement *after* the SOP transaction record was created? 3. Is there a possibility that the customer's Customer ID was accidentally updated with a T-SQL UPDATE statement *after* the SOP transaction record was created? If the customer record is missing, I would restore the last known good backup into a test environment, then transfer ...

Select Case in a Change Event
Hi, I am trying to use a worksheet Change Event to trap changes to specific columns. depending on which column is chnaged by the user, the value in another ciolumn will be altered; so, changes in either column O or P will update column Q, changes in either column T or U will update column V and changes in column Z or AA will update column AB. What I can't figure out is the Select Case bit (where I've got the shouted question below). Here's what I've put together, any comments would be much appreciated. Private Sub Worksheet_Change(ByVal Target As Range) ...

Select Records based another table...
This may be very elementary, but i can't wrap my brain around it. I have tried to dummy it down as much as i can for myself, with plans of adapting it into something working. I have three tables that look like the following: Tbl 1: ProdID A B C 1 $1 $2 $3 2 $4 $5 $6 3 $7 $8 $9 Tbl 2: RmNum ProdID 1 3 1 2 2 1 2 2 Tbl 3: RmNum Style 1 A 2 C Relationships are: [tbl1 ProdID] ------ [tbl2 ProdID] [tbl2 RmNum] ...

Multi-Select List Box Does Not Retain Selected Items
I created an Outlook Form in 2003. But when I select items in the list box they do not remain select when I select another control on the form. Any suggestions? Did you bind the list box to a keywords field? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "kaykay" <anonymous@discussions.microsoft.com> wrote in message news:276dd01c4638d$bd5af590$a501280a@phx.gbl... > I created an Outlook Form in 2003. But when I select > items i...