Access 2003

[Trish]

Hi, I would like to set up a simple database that will record how people vote
at a club meeting. This is what I would like to record:

Meeting Date
Apologies
Item No
Mover (initials)
Seconder (initials)
Carried Y/N
Those voting against ... initials initials initials initials

For the ease of use of the user at these meetings, I thought about setting
up a form where input was minimal and to maximise the use of checkboxes.

I thought about having all the initials and checkboxes across the top
(opposite Apologies), so that it can be noted if anyone is not there.

I thought about having another set of initials and checkboxes to record who
votes against the motion. There is more to this, as there would be
additional items that require a series of initials/checkboxes, but this is it
in its simplicity.

Please note I have no understanding of code. I do, however, have a fairly
good understanding of Access, but it would be helpful to have some input into
the design (relational tables?) to increase and maximise the functionality.

Also, is there a way to prevent input into the form if the date and item
numbers have not been entered. This really is a failsafe because down the
track they may want to print the report and it would be useless without the
date. The user, in a hurry, may forget to do this.

Thanks in advance for any suggestions.

 

[John W. Vinson]

Hi, I would like to set up a simple database that will record how people vote
at a club meeting. This is what I would like to record:

Meeting Date
Apologies
Item No
Mover (initials)

I'd avoid using initials. What if you end up with members Trish Roberts,
Trevor Richardson and Tarik Rahman? Who's TR?

Instead you could have a Members table with a unique MemberID, LastName,
FirstName and other biographical information. This table would store a Long
Integer MemberID as a link, and you would use a combo box on the form.

Seconder (initials)
Ditto...

Carried Y/N

I'd say two integer fields, Yeas and Nays; you can do an expression to
determine if the measure passed.

Those voting against ... initials initials initials initials

WRONG. If you're not using a secret ballot, you'll be much better off using a
subform or listbox and simply check off the names of those voting yea, voting
nay, abstaining or absent.

For the ease of use of the user at these meetings, I thought about setting
up a form where input was minimal and to maximise the use of checkboxes.

I thought about having all the initials and checkboxes across the top
(opposite Apologies), so that it can be noted if anyone is not there.

Makes the programming a lot more complicated. What do you do if you get a new
member!? Restructure your entire form? What if a member changes his/her name?
Again, you'll need to change the label on your form.

I thought about having another set of initials and checkboxes to record who
votes against the motion. There is more to this, as there would be
additional items that require a series of initials/checkboxes, but this is it
in its simplicity.

Please note I have no understanding of code. I do, however, have a fairly
good understanding of Access, but it would be helpful to have some input into
the design (relational tables?) to increase and maximise the functionality.

No code is needed - UNLESS you insist on your checkbox interface. That will
require some pretty complicated code to dynamically construct the form on
demand whenever the membership roster changes. "Fields are expensive, records
are cheap" - a Form with one or more Subforms will require no code at all.

Also, is there a way to prevent input into the form if the date and item
numbers have not been entered. This really is a failsafe because down the
track they may want to print the report and it would be useless without the
date. The user, in a hurry, may forget to do this.

Sure. Make those fields Required in table design view.

For a more user-friendly experience, instead (or better, also!) put some very
simple VBA code in the Form's BeforeUpdate event such as:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me!MeetingDate) Then
MsgBox "Please enter the meeting date", vbOKOnly
Cancel = True
Me!Meetingdate.SetFocus
Exit Sub
End If
If IsNull(Me!ItemNo) Then
MsgBox "Please enter the Item Number", vbOKOnly
Cancel = True
Me!Itemno.SetFocus
Exit Sub
End If
End Sub

 

[Trish]

Thanks John, I will give it a go.

--
Trish

I'd avoid using initials. What if you end up with members Trish Roberts,
Trevor Richardson and Tarik Rahman? Who's TR?

Instead you could have a Members table with a unique MemberID, LastName,
FirstName and other biographical information. This table would store a Long
Integer MemberID as a link, and you would use a combo box on the form.


I'd say two integer fields, Yeas and Nays; you can do an expression to
determine if the measure passed.


WRONG. If you're not using a secret ballot, you'll be much better off using a
subform or listbox and simply check off the names of those voting yea, voting
nay, abstaining or absent.


Makes the programming a lot more complicated. What do you do if you get a new
member!? Restructure your entire form? What if a member changes his/her name?
Again, you'll need to change the label on your form.


No code is needed - UNLESS you insist on your checkbox interface. That will
require some pretty complicated code to dynamically construct the form on
demand whenever the membership roster changes. "Fields are expensive, records
are cheap" - a Form with one or more Subforms will require no code at all.


Sure. Make those fields Required in table design view.

For a more user-friendly experience, instead (or better, also!) put some very
simple VBA code in the Form's BeforeUpdate event such as:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me!MeetingDate) Then
MsgBox "Please enter the meeting date", vbOKOnly
Cancel = True
Me!Meetingdate.SetFocus
Exit Sub
End If
If IsNull(Me!ItemNo) Then
MsgBox "Please enter the Item Number", vbOKOnly
Cancel = True
Me!Itemno.SetFocus
Exit Sub
End If
End Sub

 

[Trish]

Well I have had a go, taking into account what you suggested, but also having
to follow, as best as possible, the request and requirements of the user.
Firstly, he wants everything on one page, and I have achieved this using a
form. I do, however, feel as though I am in a mess, and would appreciate
any advice. Firstly, is there a way I can post my database on the site so
that you can see it? I have set it up like this:
Date
Item No
Subform with all other details, using list boxes for the members.

The error messages I am getting are :

When I type in the item number (which needs to start from 1. for each new
date), I get a message saying "Field cannot be updated", yet when I click OK,
it allows me to put it in.

Also, when I use the first drop down list, I get this message (which only
occurs with the first list, and not the others)
You can’t assign a value to this object.
*The object may be a control on a read-only form
*The object may be on a form that is open in design view
* The value may be too large for this field
Again, once I click OK it allows my selection anyway.

Next thing... any data input in the form disappears as soon as I go to the
next record. The data is, however, storing in the table.

Next thing .... I tried to create a report, using Date as the first level,
Item as the second level, but when I preview the report, it is all on one
level and the date and item numbers do not show. When I get it functioning
properly, I want to put a parameter query in, so that they just print the
reports by date.

Last thing ... I created a switchboard for the user, and ticked the default
box, but it does not come up when I open the database.

I appreciate this is lengthy and messy, but I am trying to assist someone
here, and could really use some help myself!

Many thanks in advance.