A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Create dbase and Form to add Client & report access



 
 
Thread Tools Display Modes
  #1  
Old February 24th, 2006, 02:22 AM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old February 24th, 2006, 02:44 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old February 24th, 2006, 07:04 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 09: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 10:47 PM
Show form when activating a dbase Jenn General Discussion 2 October 13th, 2004 11:34 PM


All times are GMT +1. The time now is 12:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.