Q about form design atop a complex query

[Pete]

Before I get started with the question, does anyone have a (single)
good book recommendation for database design? Not an Access-specific
book, but something geared toward helping me figure out *what the user
wants*. I've had brief formal education about data flow diagramming,
but I'm looking for ... more, now that I'm actually running into
problems I think stem from the fact that my users can't explain what
they need done, compounded by the fact that I don't know what
questions to ask them. The data flow diagrams help, and Joel
Spolsky's "activity-based planning" (
http://www.joelonsoftware.com/uibook...000000065.html ) is
another good way of getting the design in my head, but I'm looking for
even more techniques. Or maybe I don't even know what I'm looking
for.
On with the question:

I'm building a database app with Access 97.

I have one main form, "Enrollment", in which I would like the users to
be able to do all their tasks on this one form. Let's begin further
back, at the table structure:
[Enrollment] 1 ------ 1 [Payment_Auth] (BOTH SIDES OPTIONAL)
[Payment_Auth] 1 ------ 1 [Fee#1] (Fee#1 optional)
[Payment_Auth] 1 ------ 1 [Fee#2] (Fee#2 optional)
[Payment_Auth] 1 ------ 1 [Fee#3] (Fee#3 optional)
[Payment_Auth] 1 ------ 1 [Fee#4] (Fee#4 optional)

[Enrollment] 1 ------ 1 [Invoice_Details] (Invoice_Details
optional)
Enrollment is presumably the main table, the center of the database
storage. However, the Payment_Auth can (sometimes) independently
authorize payments without corresponding enrollment. There are
several different fee types--each with their own custom/optional data,
thus I did not combine them into one Fee table. Some enrollment,
involved with a specific vendor, supplies additional "Invoice Details"
-- some, not all.
I am 99% convinced that my table design is good. That isn't the
problem.
The problem is that I want to put all this information on one tabbed
form. No subforms--just one tabbed form. I have "drawn" the form
completely, modified the behavior so that (even as an unbound form) it
has the business rules ingrained, and am now coming back and working
on tying the data source for each part of the form.

This is where I'm now running into some walls. The most recent is
here--I have posted the (obviously incomplete) code for one of my fee
textboxes. When a user sets a value in the textbox, it should set the
values for that fee's primary keys. When the user sets the value to 0
or null, we should completely delete that fee item from the specific
fee table:

Private Sub ModifyMembershipFeeTable()
If Nz(txtMembershipFee.Value, "") = "" Or
CCur(Nz(txtMembershipFee.Value, "0")) = 0 Then
'----invalid value, remove all traces of this fee

'if the value is already stored in the
'table, then we need to delete the row from the table.
If Not IsNull(Me![tblExternalMembership.DMID]) And Not
IsNull(Me![tblExternalMembership.SSN]) Then
'if the value is not already stored in the
'table, then we can simply set all the values to Null,
'and it won't attempt to save anything for this table
Else
'beware that the original DMID doesn't change
'after we set this value.
txtMembershipDMID.Value = txtDMID.Value
txtMembershipSSN.Value = cboSSN.Value
End If
Else
'----add to table or modify existing entry

'if the table row already exists, then Access has
'already taken care of the work. Do nothing.

'if the table row does not exist, set the bound
'Primary Key fields for this table.
'this will leave the records with the "#Deleted"
'text, so we need to do a Requery--right? Maybe not.
End If
End Sub


Maybe I'm making too many leaps into the complexity of this thing too
quickly, but the point is: what do you do when you want to integrate
so much information onto one page? Were I to split up this
information, one form to a table, I would have to have a total of six
(or more?) forms to handle this. On the other hand, with this
monolithic form structure, the (only now visible) interdependencies
are going to kill me bug-wise.

I want to keep the form's behavior as simple and intuitive as
possible, so I don't want to lock certain fields, or hide tabs, or
make the user click on a button--basically I want to keep the user
unaware that I am performing miracles behind the scenes. But what I
really want is for me to *NOT HAVE TO* perform any miracles to get
this thing to work. I have been pleasantly surprised by undiscovered
Access features in the past, and I believe that Access' "bound form
model" can handle my situation, without a labyrinth of workaround form
behaviors. I just don't know ... how.
I'm sorry if my post has been confusing. Confusion breeds confusion,
and my incomplete grasp of the complexities of using a bound-form with
a multi-table query, with optional (deleteable) tables, has not been
fun.
Pete

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Good db design book: Database Design for Mere Mortals (1st edition)
by Hernandez. I skimmed thru this book after I'd learned DB design &
thought it looked pretty good. Didn't buy it 'cuz didn't think I
needed it. Would have bought it if it had been 2 years earlier.

===

If, as you say, you want to simplify your form you should use subforms
and re-do the form's query. It is easier to write many queries that
each do one thing (for each subform) than it is to write one query
that does many things: easier maintenance & comprehension. Usually,
forms show data from one table and subforms show data from tables that
are dependent on the data in the main form's table. I always think of
tables (& forms/subforms) as

Category Subcategory Sub-subcategory
- ---------- ------------- ---------------
Main Tbl Dependent Tbl Dependent Tbl
Main Form Subform Sub-subform

Sort of like the Linnaeus' biological categorizations I learned in
high school:

Kingdom,
Phylum,
Class,
Order,
Family,
Genus,
Species

Going from the general to the specific.

If you're worried about the display of the form, you can put the
subforms on each Tab of the Tab control.

- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP7WrwIechKqOuFEgEQIVawCeJR35lutwC/W345Wye1PXpbHvx5kAn365
xUR8T+i7BBfNNbhBZxfOfQoJ
=d5tg
-----END PGP SIGNATURE-----
Pete wrote:

Before I get started with the question, does anyone have a (single)
good book recommendation for database design? Not an Access-specific
book, but something geared toward helping me figure out *what the user
wants*. I've had brief formal education about data flow diagramming,
but I'm looking for ... more, now that I'm actually running into
problems I think stem from the fact that my users can't explain what
they need done, compounded by the fact that I don't know what
questions to ask them. The data flow diagrams help, and Joel
Spolsky's "activity-based planning" (
http://www.joelonsoftware.com/uibook...000000065.html ) is
another good way of getting the design in my head, but I'm looking for
even more techniques. Or maybe I don't even know what I'm looking
for.
On with the question:

I'm building a database app with Access 97.

I have one main form, "Enrollment", in which I would like the users to
be able to do all their tasks on this one form. Let's begin further
back, at the table structure:
[Enrollment] 1 ------ 1 [Payment_Auth] (BOTH SIDES OPTIONAL)
[Payment_Auth] 1 ------ 1 [Fee#1] (Fee#1 optional)
[Payment_Auth] 1 ------ 1 [Fee#2] (Fee#2 optional)
[Payment_Auth] 1 ------ 1 [Fee#3] (Fee#3 optional)
[Payment_Auth] 1 ------ 1 [Fee#4] (Fee#4 optional)

[Enrollment] 1 ------ 1 [Invoice_Details] (Invoice_Details
optional)
Enrollment is presumably the main table, the center of the database
storage. However, the Payment_Auth can (sometimes) independently
authorize payments without corresponding enrollment. There are
several different fee types--each with their own custom/optional data,
thus I did not combine them into one Fee table. Some enrollment,
involved with a specific vendor, supplies additional "Invoice Details"
-- some, not all.
I am 99% convinced that my table design is good. That isn't the
problem.
The problem is that I want to put all this information on one tabbed
form. No subforms--just one tabbed form. I have "drawn" the form
completely, modified the behavior so that (even as an unbound form) it
has the business rules ingrained, and am now coming back and working
on tying the data source for each part of the form.

This is where I'm now running into some walls. The most recent is
here--I have posted the (obviously incomplete) code for one of my fee
textboxes. When a user sets a value in the textbox, it should set the
values for that fee's primary keys. When the user sets the value to 0
or null, we should completely delete that fee item from the specific
fee table:

Private Sub ModifyMembershipFeeTable()
If Nz(txtMembershipFee.Value, "") = "" Or
CCur(Nz(txtMembershipFee.Value, "0")) = 0 Then
'----invalid value, remove all traces of this fee

'if the value is already stored in the
'table, then we need to delete the row from the table.
If Not IsNull(Me![tblExternalMembership.DMID]) And Not
IsNull(Me![tblExternalMembership.SSN]) Then
'if the value is not already stored in the
'table, then we can simply set all the values to Null,
'and it won't attempt to save anything for this table
Else
'beware that the original DMID doesn't change
'after we set this value.
txtMembershipDMID.Value = txtDMID.Value
txtMembershipSSN.Value = cboSSN.Value
End If
Else
'----add to table or modify existing entry

'if the table row already exists, then Access has
'already taken care of the work. Do nothing.

'if the table row does not exist, set the bound
'Primary Key fields for this table.
'this will leave the records with the "#Deleted"
'text, so we need to do a Requery--right? Maybe not.
End If
End Sub


Maybe I'm making too many leaps into the complexity of this thing too
quickly, but the point is: what do you do when you want to integrate
so much information onto one page? Were I to split up this
information, one form to a table, I would have to have a total of six
(or more?) forms to handle this. On the other hand, with this
monolithic form structure, the (only now visible) interdependencies
are going to kill me bug-wise.

I want to keep the form's behavior as simple and intuitive as
possible, so I don't want to lock certain fields, or hide tabs, or
make the user click on a button--basically I want to keep the user
unaware that I am performing miracles behind the scenes. But what I
really want is for me to *NOT HAVE TO* perform any miracles to get
this thing to work. I have been pleasantly surprised by undiscovered
Access features in the past, and I believe that Access' "bound form
model" can handle my situation, without a labyrinth of workaround form
behaviors. I just don't know ... how.
I'm sorry if my post has been confusing. Confusion breeds confusion,
and my incomplete grasp of the complexities of using a bound-form with
a multi-table query, with optional (deleteable) tables, has not been
fun.
Pete

[Pete]

> Good db design book: Database Design for Mere Mortals (1st edition)

by Hernandez. I skimmed thru this book after I'd learned DB design &
thought it looked pretty good. Didn't buy it 'cuz didn't think I
needed it. Would have bought it if it had been 2 years earlier.

Thanks for the advice on form design. I'll split up the form into
three forms, and use subforms for all the "child" tables. Ironically,
I'm already using tab controls to (logically) separate the fields, so
I was just a hop away from a decent solution.
I'll check out the book on my next run to a B&N bookstore--I seem to
remember hearing about it before this.

Thanks,
Pete