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