Richard,
On Fri, 5 Aug 2005 22:36:04 -0500, rgarber50
wrote:
I hope general design questions are appropriate for the forum ... if not
please let me know.
I should mention that this isn't really a "forum", nor is it regulated
or brought to you by nice Mr. Rubin. The "Forum" on the site that you
posted through is just a gateway to public Usenet news groups, which
you can also access by many other means. The question's therefore
entirely appropriate.
i work with kids for a non-profit and I'm creating a Client Info form
on a worksheet. I plan to store all client info in a list on a separate
worksheet and use VBA to populate the form with a particular client's
current info - work on the form updating/changing/viewing client info
- use VBA to update the client info list with any changed data. There
is a fair amount of info for each client/record - maybe 75-100 fields,
however there would only be 25-35 client/records .
At the outset I'd say that I'd be more inclined to use Access for this
sort of thing rather than Excel. But if you don't HAVE Access,
certainly Excel can be used as a substitute, particularly if you have
only 25 to 35 clients.
75 to 100 fields strikes me as odd. It seems to be too many fields for
basic information like name, address, etcetera, so I have a feeling
that you may be recording things like the childrens' activities in
those extra columns? If so, it would mean that you'd probably have to
define a specified number of activities per client... which could come
back to bite you further down the track. A relational database can
free you from those limitations, but there's no point in going into
that too much since it's only speculation on my part.
But one thing you might consider is whether you HAVE to have all of
the client's information on the one row, or whether it might be more
flexible to have basic client details in one row of one sheet, and
"variable" data (data which may or may not apply to each client, or
which may require a different number of fields for each client) on a
separate sheet. You could link them together by having a code number
for each client.
This is a somewhat cut down version of what a relational database
does. It's not a path that I'd go down if I were updating a workbook
manually (it would be too hard to do data validation to ensure that
the records linked together), but since you'll be doing it via VBA you
shouldn't have too many problems with such an approach.
However the one that you're suggesting could be just as effective, and
it would be a touch easier to maintain... as long as you don't need to
change the number of fields down the track.
So I'd have:
Wks 1: A Main Menu with list of clients displaying some key data -
Macros to get me to individual clt form, and add or delete clients
Wks 2: Hidden database storing all Clt info.
Wks 3: Client info form
I'm not a big fan of using worksheets as data entry forms; it's hard
to walk the line between protection and useability. It's certainly a
viable option, but I'd suggest that you look at creating a User Form
instead. It gives you better control over data validation. For
example, if you use Data Validation on a worksheet, the validation can
generally be blown away by someone just pasting into the cell from
another sheet. If you have a combo box on a user form, you can ensure
that the validation remains in tact.
I wanted to keep all the client data in a separate wks list rather than
1 wks form per client so i could more easily make changes to the form
design.
That's good thinking.
I'm a beginner at VBA - but I feel pretty confident about working out
the programming - my question: Is the way I've gone about structuring
the thing "good design".
With most things, there are many ways to get the same result, all with
their own pros and cons. A bit of experimentation will tell you what's
right for you.
Thanks in advance for any feedback.
Richard
---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *
|