A question about forms...

[jyanks]

So I'm new (as of today) to the access scene. I want to create a database for
the Center for Sex & Gender education at my college so that we can
electronically log all of our interactions. There are several logs (Visitors,
Shifts, Library Books, and Sales). Each one will have its own form. What I'd
like to do is to be able to have the following structure:

1. An employee accesses the database and enters their shift information
(Date, name, time in, time out, cash at beginning)- all stored in the
shift_log table.

2. An employee is able to create a visitor log, sales log, library log entry
for each visitor, sale, library book. These would be submitted via forms.

3. An employee's shift ends, they fill out the end of the shift information
in the shift_log form (cash at end, # visitors, # sales, other purchases, #
books, and comments).

4. A new employee comes on shift and is able to access the database in the
same way.


I'm running into three problems- maybe I've misunderstood the functionality
of the forms. First- I'd like to be able to have some sort of a 'submit'
button so that when the employee logs on, and starts the shift_log form, for
instance, there should be a blank form that writes to a new record. I don't
understand how to have it write to a new record, nor to have some sort of a
submit button.

Second- The shift_log has a autoNumber shiftID for each record. I'd like the
shift_ID to be added to any records added during that specific shift to the
visitor_log, sales_log, library_log, and quesiton_log. There is a field in
each of those tables called shift_code to record that. I'm not sure how to
make that happen.

Lastly, I'd like to make a little menu perhaps so that the employee
currently adding logs can access each form from a universal menu.

If any of that's possible, I'd love for some sort of guidance- I know I'm
quite the amateur at this, but it would be for a great cause!

 

[Larry Daugherty]

Everything you ask is possible with Access. However, the details of
getting there from scratch can fill volumes. I've copied the content
of a helpful post from MVP John Vinson in response to someone's
request for learning resources:

-------------------------------------
Plenty of references (and links to more) can be found at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

----------------------------------

For novice Access developers I recommend lurking

microsoft.public.access.gettingstarted
microsoft.public.access.tablesdesign

At the Access web in John's list I particularly recommend that you
read "netiquette". No, you haven't done anything wrong. The article
lays out a lot of the expectations about using these Access
newsgroups. It isn't a list of "laws" but suggests how to best use
these newsgroups.

Post back as specific issues arise. One issue per thread for best
results.

HTH

 

[jyanks]

I have a few questions, if you don’t mind. It might be best if I just end you
the database that I’m working on- it’s pretty simple.

My first question is- When I create a new shift, I want to be able to have
people easily add visitors, condoms, library books, and questions to that
specific ID number… I was thinking, on those forms, there could be a dropdown
menu for the input for the shift_ID, rather than having someone to have to
remember which ID they were working on. Maybe the dropdown could show
“Shift_ID, PA Name, Date, Time†for simplicity.

My second question is, have I set this up badly? I figured the Shift_ID
would be a pretty easy way to organize the data and keep everything more or
less connected. Maybe there’s a feature I haven’t yet discovered.

Lastly, is there any way this could be used on a mac as well, using
something like the packaging option?

Thanks so much for the help earlier this week!

(I tried emailing this message, but your email was invalid... is there some
way I could show off the database?)

 

[Larry Daugherty]

Before reading further, go to the Access Web and read "Netiquette".
When I wrote before that you hadn't done anything wrong it was true.
Now it is true that you have sinned by trying to send me an attached
file without prior agreement.

It isn't too difficult to remove the spam blocker from my email
address but I'm glad that you didn't. Unsolicited emails with
attachments will be deleted unread. The originating email address
will be placed in a killfile and no further emails will be received
from that address and no further postings in the newsgroups will be
seen.

If you read the referenced article you'll see that the intended
mechanism of posting and responding in the newsgroups is that the
issue is posted, it is clarified, remedies are suggested, the Original
Poster (OP) implements and tests the proposed solutions. If the
proposed solution doesn't produce satisfactory results then relevant
parts of the process are repeated until there is a satisfactory
outcome or no further progress can be made. The entire dialogue
regarding that one technical issue is contained in the thread and is
available for the active participants and for the hundreds of silent
lurkers who are learning as well. The purpose is to share technical
Access lore among developers and budding developers. With very few
exceptions the responders here are all unpaid volunteers. It is
against the charter of these Access newsgroups to solicit work for a
fee. It is also forbidden to provide free programming.

While I do sometimes invite newbies in trouble to send me their
application it is only after there is evidence that they're doing
their very best to help themselves. Further, it is necessary that I
believe that they are heeding and acting on the advice given and still
having difficulties. It is not evident to me that you are heeding nor
acting on the suggestions given.

Have you spent an hour or a few hours poking around on The Access Web?
Have you looked into any of the links from John Vinson that I passed
along? Have you been regularly spending an hour or more in the
suggested newsgroups?

If you were to send me your application as it now is, I would expect
to find a disaster. That isn't a put down of you. People coming to
Access without any prior Relational Database Management System theory
and practice will be sorely lacking is some necessary modes of
thinking.

You would probably send your application without supporting
documentation. That would only permit verifying internal consistency
and leave the burden of inferring the requirements in the blind. Also
required are the Problem Statement and the Solution Statement (also
known as the Product Specification). A Systems Analyst could generate
those two documents irrespective of whether the eventual
implementation were to be hi-tech paper and pencil, abacus and paper
work sheets, RDBMS or whatever.

You should begin your work by turning your back on Access and either
using pen and paper or Word to generate those first two documents and
sketch out the entities in your application and the interactions
between them.

Only when you've done the things in the paragraph above should you
begin to think in terms of Access. The earlier the step in the
process the more important it is. Those early steps are seldom
discussed. Even among professional Access developers they get short
shrift and a wave of the hand. Everybody is rarin' to go to get to
the fun parts. The heck of it is that the scarred old veterans can
actually function pretty well using a few notes on a pad of paper
boiled down from a few client interviews. They have their processes
pretty well established. Novices trying to function in the same way
will find themselves continually going back to the beginning to
correct earlier wrong assumptions or omissions and then percolating
their changes throughout their application - an expensive process. It
is pretty well accepted that the cost of finding and correcting an
error in the specifications and subsequent design increases by a
factor of 10 for each succeeding phase of a software project. Since
you're doing your own application starting from scratch, assign a
value to your time and plug that value into the formula.

It may sound grim and intimidating. Don't let it get you. The Access
learning curve is long and steep but getting over the hump is a lot of
fun and can be very rewarding.

The specific questions you asked can't be answered without a view into
your schema. You need to be able to concisely describe your tables
and fields so that others can know your meaning.

In the interest of speed I invite you to send your application as an
attachment in a zipped file. Also, please include your Problem
Statement and your Product Specification in that zip file. *Don't*
post binaries into the newsgroups! I don't know your mail client or
mechanism but I'll leave it to you to take the spam blocker out of my
address and get the email to me.. Please include your name and
contact information . What's your time zone?

Expectations: expect that I will give guidance and leave the heavy
lifting to you. I may include a few examples but remember that the
purposes of these newsgroups is that issues get resolved in the
newsgroups for the benefit of all.

HTH