473,322 Members | 1,345 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Q about form design atop a complex query

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
Nov 12 '05 #1
2 3525
-----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

Nov 12 '05 #2
> 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
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: WJ | last post by:
I have a need to hire Web Designers who do nothing but design plain web forms in ASP.NET format. Unfortunately, most of these folks do not know how to use VS.Net 2003 to design Asp.Net forms....
0
by: schan | last post by:
Hi there, I was wondering if someone could shed some light on a problem I have no idea on how to fix. I created an Excel Add-In that uses an ADO connection to an Access database on a file...
4
by: yanjie.ma | last post by:
Hi, I've got a two part question on table and form design (sorry for the length but it takes a bit to explain). Our sales department uses a look-up table to help the them select the best...
4
by: Deus402 | last post by:
Here is my table design: tblEmployers EmployerID autonum (primary key) EmployerName text tblLocations LocationID autonum (primary key) EmployerID longint (foreign key) LocationAdress text
3
blyxx86
by: blyxx86 | last post by:
Hey there, I'm running into a slight problem today... I have a few things to show... I'm running into duplicate values being shown in my query, but I cannot use a "Select DISTINCT" as it...
1
by: Rudolf Bargholz | last post by:
Hi, We have created triggers to log modifications to tables in our application. The triggers work fine, just on one of the tables in our database the triggers fail with the error message...
0
crystal2005
by: crystal2005 | last post by:
Hi, I am having trouble with some complex SQL queries. I’ve got winestore database, taken from Web Database Application with PHP and MySQL book. And some question about queries as the following ...
3
by: william67 | last post by:
I'm having a hard time building a query to do what I need to do and was hoping some genius could help me out, I need to do a complex query and any and all help is much appreciated this is the...
0
by: Kozy | last post by:
Hello everyone, i have a big problem with imports. I have only see this problem on 9.5, i have never goten it on 9.1. 99% of time db2 works great ( version 9.5 fixpack 5 ) on windows 2008 (...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.