Full Version: Forms Planning for Subform Subform Command Buttons
UtterAccess Forums > Microsoft® Access > Access Forms
likajoho
Before I proceed with a major change in my database, I would like to run this change by you much more experienced users.
I have frmReceiptsEntry (based on Contacts who are donors query) with sfrmReceipts (based on all receipts query). sfrmReceipts has checkboxes indicating a need to send a letter and then command buttons to preview the letters. I keep adding new letters (I'm up to about 20 now). Since I have four back-ends to this front-end, I keep having to add new fields to all four tblTransactions each time I add a letter, plus add new queries, command buttons, etc.
I thought I should build two new tables instead:
tblCorrTypes:
CorrID pk
CorrName
...
tblCorrLog:
CorrLogID pk
ContactID fk
CommID fk
TrsID fk
CorrID fk
MarkToSend chkbx
DateSent
....
What problems will I encounter in getting command buttons to work in a new sfrmCorr of the sfrmReceipts?
Should I stick to the way I am doing it now instead of affecting this change?
I would appreciate any input since this is a major change.
Linda
dashiellx2000
The table idea seems valid to me. However, you could also replace you command buttons with a combo box that lists the different letters and open the report/letter that way.
TH.
likajoho
William,
Could you elaborate a little more. I'm note sure I understand what you mean. How can you have commands in a combo box?
FOr if you mean choose one letter from a combo box instead of from a sub-form, that would be fine if only one letter were all that was ever needed, but that is not the case. Every donor gets a thank-you letter. Many get a request letter, and some more than one request letters. Vendors also can get more than one request letter.
Linda
Rainlover
Linda

You can have a commond behind any object. Just select the event most appropiate, probaly Before Update and place your code there.

However if you are wanting to print various letters on the one command then the combo may not be good for you but it is a good idea.I like it.

But I am curious as to why you have several Back Ends.
likajoho
Des, I'm not sure how the code behind would work. I want to be able to click on a button when I want the letter printed. Sometimes I will print them in batches by opening a report or by using a special form for printing batches. But these buttons are just for printing one letter at a time. Sometimes I will want to preview the letter more than once so I can tweak the letter by changing the information in fields used by the letter.
I have a small business. I have several clients. I use the front-end for all of the clients because I'm continually developing new reports and queries and more. But I use different back-ends for each one to keep their data separate and not accidentally mix it up.
Thanks for you interest, Des. I'm glad for any new ideas on how I should do this.
Linda
Rainlover
Linda
uote; "Des, I'm not sure how the code behind would work."
Are you able to write the code for a command button. If so then do it that way.
Quote; " I want to be able to click on a button when I want the letter printed. Sometimes I will print them in batches by opening a report or by using a special form for printing batches. But these buttons are just for printing one letter at a time. Sometimes I will want to preview the letter more than once so I can tweak the letter by changing the information in fields used by the letter."
Are you saying you have more than one form for the purpose of printing?
Quote; "I have a small business. I have several clients. I use the front-end for all of the clients because I'm continually developing new reports and queries and more. But I use different back-ends for each one to keep their data separate and not accidentally mix it up."
This is a problem. What would you do if you had a Thousand Clients. You would then need a Thousand Back Ends. Very messy if this is the case. Have you read the article on Normalization ? If you haven't I recommend that you take the time now to read it. It will help you with future development.
likajoho
<Are you saying you have more than one form for the purpose of printing?>
Yes, I originally created a form for printing thank-you letters in batches. However, I occassionallly print them one at a time directly from the frmReceiptsEntry. I also have other letters where I have to request information and perform other administrative tasks. These I prefer to print individually, although I might conceivably print them in batches if my data increased and it was warranted.
<What would you do if you had a Thousand Clients. >
Not likely. I have a VERY SMALL business-just me. I can only handle so many clients.
But, your point is well taken. I don't see what else you can do though. That's what everyone told me to do--to split the database into a front-end and back-ends.
Odid try to read the article on Normalization. The problem is, I'm continually stymied by words and phrases I don't understand. I can only absorb so much at one time--I'm no genius. So I just have to take this stuff one step at a time. I think I generally speaking undersand what normalization is. That's why I have so many tables in my database. But I'm sure there are some fundamentals I'm missing. There really is no substitute for a good teacher to sit down with you--with your database in front of them--and expalin what you have done wrong. In lieu of that, I appreciate everyone's help. But lengthy articles are hard for me to digest. And when I'm tired like I am right now, impossible. I'll have to look at it another time.
Thanks, Des.
Linda
Rainlover
Linda I will post a sample you you to look at. Have a good nights sleep.
Rainlover
Linda
The attached is a very simple DB with two Tables. The first is for Client Details and the second is for Client Invoices. It hopefully demonstrate how each client is given a unique Number and that the Invoices are joined to that table by that number. You will see that the clients cannot be mixed up.
HiTechCoach
Having lots of tables does not necessarily mean that the data is properly normalized. I have found that properly normalization of data can also reduce the number of tables required.
This usually means that your data is not properly normalized. Every time you add a new letter, you probably should be adding a record to a "look up" table not a new field. This would indicate that you have repeating data. This is what normalization of your data should eliminate.
Also having four tblTransactions sounds like to many. You maybe could to it with only one tblTransactions.
likajoho
<This usually means that your data is not properly normalized. Every time you add a new letter, you probably should be adding a record to a "look up" table not a new field. >
hat's what I was thinking and why I'm working on changing to having a correspondence log and a correspondence types table instead of a separate field in my transactions table for each letter. I'm assuming I would need the table that stores the type/name of each letter and then a table which connects the two--assigning a letter to each transaction.
My original question was, what problems will/can this cause if I include these new fields in a sub form of a sub form and use a command button for previewing the form from a specific transaction, like I'm doing now in the sub form. Will this additional layer cause some problems that I can't even imagine?
<Also having four tblTransactions sounds like to many. You maybe could to it with only one tblTransactions. >
Odon't exactly have four tblTransactions. I have four back-end databases, each with a transactions table. When I fundamentally change that table, then I have to change each of the back-ends. Am I missing something and there is a better way (that a bascially non-vba person can handle)?
FYI, each of the back-ends are identical so changing them isn't difficult. (I have a different back-end for each of four of my clients which I use this database for.)
<Having lots of tables does not necessarily mean that the data is properly normalized. >
I can see that. Each of the tables has a purpose of reducing redundancy. I actually have found I wish I had put in more tables instead of letting look-ups to values lists handle that. I keep having to change the values list in each of the back-ends.
THere is what my database looks like:
tblCommittees - pk CommID (one record per back-end & per Contact)
fk - Treasurer (ContactID in Contacts table)
value list - CommType
...

tblContacts - pk ContactID
fk CommID
fk ETID
fk HHID
fk - Status
value list - Sex
value list - Email1Type (Home, Work, etc.)
value list - Email2Type
value list - FaxType
value list - CellType
...
tblStatuses - pk StatusID
...
tblEntities - pk ETypeID (one per contact)
...
tblContactTypes - pk CTypeID
...
tblContactTypesList - pk CTLID
fk CTID (more than one per contact possible)
fk ContactID
tblHouseholds - pk HHID
fk - HOHID (ContactID)
...
tblGroups - pk GroupsID - (not using for obvious reasons--needs to be normalized into 2 tables I think)
fk - GroupID (ContactID)
fk - MemberID (ContactID)
tblAddresses - pk AddID (more than one possible for each individual)
fk ContactID
fk AddType
value list - AddStatus
...
tblAddTypes - pk AddTypeID (one per address)
tblReports - pk ReportID (more than one per Committee)
fk CommID
value list RepType
...
tblBatches - pk BatchID
...
tblTransactions - pk TrsID
fk CommID
fk ContactID
fk RepTrsTypesID
fk DocTypeID
fk CashAcctID
fk IncAcctID
fk DisbAcctID
value list - TrsType (Receipt, Disbursement, Memo...)
value list - TYLtr (Gen, HH, etc.)
value list - PrtBatch (1,2,3,4....Hold,Sent)

tblRepTrsTypes - pk RepTrsTypeID
...
tblDocTypes - pk DocTypeID
...
tblCashAccts - pk CashAcctID
....
tblIncAccts - pk IncAcctID
...
tblDiscAccts - pk DisbAcctID
tblNotes - pk NoteID
fk ContactID
....
tblEvents - pk EventID
tblAttendees - pkAttendeesID
fk Attendee (ContactID)
fk TrsID
tblCorrLog - pk CorrLogID
fk CorrType
fk ContactID
...
tblCorrTypes - pk CorrTypeID
Boyd, this does seem like a lot of tables. But I find myself wishing I had made most of the value lists into tables. I would be glad to hear about where I could cut the number of tables.
BTW, I'd also like to hear your take on moving some of these tables and putting them in the front-end instead of the back-end:
AddressTypes
ContactTypes
CorrTypes
DocTypes
RepTrsTypes
Statuses
This would be because the contents in this table generally don't change, and, if they change, they should change across the board so that new queries access the correct ID in all back-ends.
Thanks for your comments and ideas, Boyd. I appreciate your input as well as all the other UAers.
Linda
likajoho
<You will see that the clients cannot be mixed up. >
hat's if all goes well.
I actually am currently having a problem of the clients not registering from a form to the table. I have to go in and put them in by hand before querying, etc.
It's one of those things that I know I need to address, but my volume is so low I haven't.
Besides I just can't risk my data getting corrupted. I prepare government reports from this data and have to have every i dotted and every t crosssed. My database may be pathetic, but my data must be perfect.
In my mind I can see no way to combine the back-ends; however, cumbersome it might be. Now there is probably a better way to keep them all synched.
Thanks for your assistance, Des. But my situation is a bit more complex than your example. The CommID is an important part of my database. Committees is an essential table. Perhaps I could dispense with the CommID in the Transactions table, I'm thinking about that one--it would require changes to queries. It may be just leftover from when I had all of the back-ends combined and I no longer need it. But for now anyway, I need both a unique ContactID in each of the transaction tables as well as a CommID.
If I'm not making sense it's because my daughter is rushing me to get around for church.
Thanks and please throw them ideas at me. One of them my stick!
Linda
Rainlover
Hello again Linda.
My original question was, what problems will/can this cause if I include these new fields in a sub form of a sub form and use a command button for previewing the form from a specific transaction, like I'm doing now in the sub form. Will this additional layer cause some problems that I can't even imagine?
To answer this question.............. Probaly not. But the design of your back end may cause problems.
If you post a cut down version of your back end then we could give better advice.
HAs far as having some tables in the front end and some in the back end, I do not have a problem with this. in fact I would say that as you are the only person using the db, you do not have a need to have a back end at all.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.