If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create dbase and Form to add Client & report access
If at all practical, stay away from hard coding report names and
titles into labels on your form or into the field names of the underlying table. Businesses and their applications evolve. Individual reports may eventually fall into disuse. New reports will come into being. You would be continually going back to re-design and coding of your table form. I recommend that you create a new table: tblCustomerReport which will be in a one to many relationship with tblCustomer. It will have a record for each report for this/each customer. For Customer Report management, create a form based on tblCustomer and a subform based on tblCustomerReport. At this point, you have something that will work and that doesn't require constant re-design/re-code. However, it leaves lots of typing to do in order to get the correct reports associated with individual Customers. Also, those selfsame changes I referenced above would require individually removing defunct reports from customers' lists. A new report coming into the system would require manual entry into every appropriate customer list. If this were mine to do I would replace the subform above with a custom multiselect list box. That listbox would load all of the reports in the database. tblCustomerReport would then be scanned and each report found would be marked in the listbox. The user could then add or remove reports from the list by selecting them on or off (I always provide "Select All" and "Unselect All" so that the user can decide which way to begin). At the Before_Update event for this form, all of this customer's records in tblCustomerReport are deleted and the currently selected list of reports is added. Now you have a system that will allow you to maintain report lists without manual data entry. I don't use the MSLB provided by Microsoft because it's vulnerable to version changes of Access and the OS but I'm sure you can use it somehow to achieve the same effects.. HTH -- -Larry- -- "ttbbgg" wrote in message ... Hi there. Trying to create a dbase that keeps track of client name, their affiliated codes (could be more than 1), and the reports they use. We have about 150 reports but not all clients use every report. Trying to sort out a proper dbase and the required form for entry of new client, their code(s), and reports they use. I was hoping to have a form that had a series of checkboxes for each report but if there is a better or easier way to do it I'm open to suggestion. |
#2
|
|||
|
|||
Create dbase and Form to add Client & report access
Ok thanks, Larry. Can you just provide some guidance as to how the table
CustomerReport would be setup? I assume the fields in the customer table would be Customer Name and the affiliate code(s). What relation would I input? Should I just make an autonumber field and relate it that way? TG "Larry Daugherty" wrote: If at all practical, stay away from hard coding report names and titles into labels on your form or into the field names of the underlying table. Businesses and their applications evolve. Individual reports may eventually fall into disuse. New reports will come into being. You would be continually going back to re-design and coding of your table form. I recommend that you create a new table: tblCustomerReport which will be in a one to many relationship with tblCustomer. It will have a record for each report for this/each customer. For Customer Report management, create a form based on tblCustomer and a subform based on tblCustomerReport. At this point, you have something that will work and that doesn't require constant re-design/re-code. However, it leaves lots of typing to do in order to get the correct reports associated with individual Customers. Also, those selfsame changes I referenced above would require individually removing defunct reports from customers' lists. A new report coming into the system would require manual entry into every appropriate customer list. If this were mine to do I would replace the subform above with a custom multiselect list box. That listbox would load all of the reports in the database. tblCustomerReport would then be scanned and each report found would be marked in the listbox. The user could then add or remove reports from the list by selecting them on or off (I always provide "Select All" and "Unselect All" so that the user can decide which way to begin). At the Before_Update event for this form, all of this customer's records in tblCustomerReport are deleted and the currently selected list of reports is added. Now you have a system that will allow you to maintain report lists without manual data entry. I don't use the MSLB provided by Microsoft because it's vulnerable to version changes of Access and the OS but I'm sure you can use it somehow to achieve the same effects.. HTH -- -Larry- -- |
#3
|
|||
|
|||
Create dbase and Form to add Client & report access
Sure.
First, design and populate tblReport tblReport: ReportID, Autonumber ReportName, text,100 ReportDescription, text, 100 Once you have that table designed, populate it with the name and description of every report you want to be selectable as a Customer Report. tblCustomerReport CustomerReportID, Autonumber CustomerID, Long Integer (Foreign Key on tblCustomer) ReportID, Long Integer (Foreign Key on tblReport) CustomerReportNote, text, 250, notes about this report and this customer In the Relationships window bring up tables tblCustomer and tblCustomerReport. Draw a relation from tblCustomer.CustomerID to tblCustomerReport.CustomerID. Doubleclick the relation line and establish the one-to-many relationship. Set Referential Integrity on and enable Cascading Deletes. Design a new form, frmCustomerReports based on tblCustomer. Make it easy to find the customer by including a Find Customer combobox in the header area. Make sure that CustomerID is included in the form's underlying query but don't show it on the form. Leave plenty of space at the bottom of the form for the subform you are about to design. Don't worry about exact size of anything the first time through this process. It's an iterative process so be patient with yourself. Before we get into the subform I want to raise a couple of points. 1. The first is that you should adopt a naming convention and stay with it. On days when you're bored and have nothing else productive to do, go back through your applications and keep bringing them into conformity with your chosen naming convention. As you get more used to the convention, always do it correctly the first time. The one I use is the Reddick Naming Convention. There is another, similar convention; the Leszynski convention. When I first knew it, they were a team. My favorite author, Ken Getz and his cohorts favor Reddick and so do I. Now, as you may already know re-naming things after you have already built forms, queries and reports is a risky and tedious business. To help ease that pain there is Find And Replace, a shareware utility for helping with that process. It's written by Rick Fisher and is available at www.rickworld.com The person you are doing all these things for is the future YOU. Some day the code you are working on now won't even be a memory but if you have used the naming convention with any rigor at all you will eventually be immensely grateful. That future you thanks you (or not!). 2. I will now assume that you will always name your objects consistently so all reports will begin with "rpt" followed by the name: rptReportName. Those names aren't terribly friendly to most users. In what we are doing you don't have to show the user that name. You can select your reports based on your description alone. By selecting the description in a combobox you can then use the stored ReportID to find the correct report name in the report field. User friendly and effective... Design a new form named, for purposes of this discussion, sufCustomerReports. You can start a rough design based on tblCustomerReport. Later you'll change the form's data source to a query based on tblCustomerReport and embedded in the form. The query will have tables tblCutomerReprt and tblReport. Output fields tblCustomerReport.CustomerID and tblReport.ReportDescription. Also sort ascending on ReportDescription. Do not show Header and Footer as this form will show multiple times in a possibly scrollable subform control on frmCustomerReports. Keep it down to one line not much taller than the single control that will be on it. It shouldn't be much more than 3 inches wide. Put a single combobox on it named cboRepoortDescription. The row source for the combobox is a query on tblReport that returns ReportID and ReportDescription. Ascending sort ReprtDescription. The cboReportDescription has 2 fields. The field widths are 0";2.5" adjust the 2.5" later to suit your taste. The bound column is '0'. (The column count is 1 based but the column naming is 0 based). Close this form. Open frmCustomerReports and small size it so that the open space at the bottom of the form is showing. Shift the form so that you can find sufCustomerReports in the Database|Forms window. Click and drag suf... into the bottom of frm... and release. From here on in it's a matter of move, size, redesign, frm,suf, etc. My day is totally turned around and my head is hitting the keyboard. I may have been telling you to do it all backwards. Anyway, I do hope it helps. Post back with your results. If you run into troubles in the next few hours just post. Someone will probably pick it up. -- -Larry- -- "ttbbgg" wrote in message ... Ok thanks, Larry. Can you just provide some guidance as to how the table CustomerReport would be setup? I assume the fields in the customer table would be Customer Name and the affiliate code(s). What relation would I input? Should I just make an autonumber field and relate it that way? TG "Larry Daugherty" wrote: If at all practical, stay away from hard coding report names and titles into labels on your form or into the field names of the underlying table. Businesses and their applications evolve. Individual reports may eventually fall into disuse. New reports will come into being. You would be continually going back to re-design and coding of your table form. I recommend that you create a new table: tblCustomerReport which will be in a one to many relationship with tblCustomer. It will have a record for each report for this/each customer. For Customer Report management, create a form based on tblCustomer and a subform based on tblCustomerReport. At this point, you have something that will work and that doesn't require constant re-design/re-code. However, it leaves lots of typing to do in order to get the correct reports associated with individual Customers. Also, those selfsame changes I referenced above would require individually removing defunct reports from customers' lists. A new report coming into the system would require manual entry into every appropriate customer list. If this were mine to do I would replace the subform above with a custom multiselect list box. That listbox would load all of the reports in the database. tblCustomerReport would then be scanned and each report found would be marked in the listbox. The user could then add or remove reports from the list by selecting them on or off (I always provide "Select All" and "Unselect All" so that the user can decide which way to begin). At the Before_Update event for this form, all of this customer's records in tblCustomerReport are deleted and the currently selected list of reports is added. Now you have a system that will allow you to maintain report lists without manual data entry. I don't use the MSLB provided by Microsoft because it's vulnerable to version changes of Access and the OS but I'm sure you can use it somehow to achieve the same effects.. HTH -- -Larry- -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
adding a form to a table already linked to several forms | debs_n_soots | Using Forms | 2 | January 5th, 2006 08:20 AM |
Linking v Embedded | Jim Bunton | General Discussion | 5 | August 10th, 2005 11:09 PM |
Understanding Primary Keys | Khai | Database Design | 3 | January 20th, 2005 09:47 PM |
Show form when activating a dbase | Jenn | General Discussion | 2 | October 13th, 2004 11:34 PM |