Full Version: Carrying on info to next form/sub form
UtterAccess Forums > Microsoft® Access > Access Forms
Hi all,
I have a form (jobdates) and a sub form (hiring equipment), in the job dates it has the company name that the quote is being done for , a quote number and the planned job start date, from there the user then add any equipment he may have to hire, costs etc.
Then they click next and go onto the next form which is Job Dates with expences as a sub form, what Im tyring to get working but cant is for the quote number, comapny name and planned job date carried forward so they dont have to be put in again and eliminates the margin for user errors and carry this forward again to other form/subforms until the quote is complete.
When they then come back to do a new quote, click new on the first from/sub form which is job dates, equipment hire all the forms/sub forms are cleared ready for the next quote.
I have attached what Im doing if any would care to have a look it would be greatly appreciated.
Regards Dave
orry, got no time to look at the attachment right now. In my humble opinion, you have two options to approach your problem:
1. If you are opening a new form each time the next button is clicked, then you can copy/assign the values you need from the first form to the next with something like:
Forms!Form2.NameOfControl = Forms!Form1.NameOfControl
2. Instead of opening new forms, you can place them in tab controls as subforms to your main form. When the user clicks the next button, you can make the next tab visible or have the focus.
Hope that helps...
Thanks for that, I have tryed to add more subforms but want allow me to have more than three for some reason, so ive had to go about it this way.
When you get the chance would you mind having a look for me please.
Regards Dave
Hi Dave,
First, you need to normalize your data. you have JobName in the clients table -- the Clients table should ONLY contain Client information.
You need a Jobs table something like this:
- JobID, autonumber
- ClientID, long integer -- FK to Clients (you currently have this named ClientDetailsID -- shorten it to ClientID)
- JobName, text
do not use spaces or special characters in your names -- you can, but it is much better not to
The JobDates table looks like it has info that should go in the Jobs table -- always keep names short and as general as you can
You should have a main form based on Jobs
ClientID will be a combobox and, once a client is chosen, you can echo more information from the combo columns, such as Address and Phone Numbers using caluclated controls (read the combobox example in Access Basics)

Access Basics
8-part free tutorial that covers essentials in Access

You will use a TAB CONTROL on the subform to organize the subforms -- just like a tabbed dialog box, the tab control gives you different "pages" that can be labeled like 'Consumeables', 'Equipment', 'Expenses', 'Invoices', etc

Organize your relationship diagram so that the "1" table is on the left and the "many" table on the right. I have attached a relationship diagram that has been rearranged.

Also, show ALL the tables -- even if they do not have relationships yet. this gives you a good overall view of what you have set up. If there are temporary tables in the database that you do not need anymore, delete them.
before you think about your forms, you need to get the data structure right
Thanks sooooo much crystal, I shall go through and do all that. I have tryed to organise all the sub forms on one form but it want allow me, hence the form/sub form for each one.
Thanks again Dave
you're welcome, Dave

remember: first work on the table structure and relationships

in tblEquip, the ID should be called EquipID. Here is where you will also put default values for things like: RateDaily, RateHourly, etc

I would rename tblEquipHire to JobEquip (you can keep the 'tbl' prefix if you like but, personally, I see no advantage, only disadvantages -- do preface query names with 'q' though)

it will have:

- JobEqID, autonumber
- JobID, long, FK to Jobs
- EquipID, long, FK to Equip
- RateDailyJob
- RateHourlyJob

RateDailyJob and RateHourlyJob are repeated in this table in case you need to adjust the default rates for particular Jobs

I imagine you should have an Expenses table that defines expenses... something like this:

- ExpenseID, atuonumber
- ExpTypID, long -- FK to Expense Types
- ExpDescr, text -- expense desctription

ExpenseTypes -- for categorizing the different types of expenses
- ExpTypID, autonumber
- ExpType, text

then, what you are now calling Expenses should be JobExpenses
no worries, do you mind if I fix up the table structure etc, then send it to you for a look before I continue to make sure it right from the start.
Regards Dave
Hi Dave,

once you get your database fixed up, compact/repair, zip, and attach here. Make sure you have laid out the relationship diagram properly -- and please put at least a sample record in each table showing what kind of information each field will contain -- and be sure to fill out field descriptions in the table design. These descriptions will be used for status bar text when you make forms, so make them nice for the user <smile>

Edited by: strive4peace2008 on Tue Jul 29 21:13:21 EDT 2008.
Hi Crystal
have gone right over the whole thing, added some data in tables that I could. Hope the relationship setup doesnt scare you to much, dont the best with it that I could.
Please let me know your honest thoughts and any corrections I have to do to get this right.
Kindest of Regards Dave
hi Dave,
nder Tools, Startup --> set display form to --> None
on the relationship diagram -- position the "1" table on the left and the "many" table on the right -- that way, the diagram flows as records need to be created. For instance, Clients needs to be to the left of Jobs, Equip to the left of JobEquip, etc

since you can't spell Miscellaneous, just use "Misc" <smile> ... it is shorter anyway

fill in your Field Descriptions in the table design! Not only does it help you and the users, but it also helps us to better understand what your fields are for

stretch out your fieldlists so everything shows -- another reason for shorter names wink.gif

don't use spaces in fieldnames. Long names like 'Equipment Description' should be shortened to something like "EquipDescr"

take JobDateID out of Equip

put key fields at the top of the fieldlists

what is the JobDates table for? In addition to filling out your field descriptions, right-click on each table, choose 'Properties' and also fill out Table Descriptions -- they will show when the Database window is in Detail View

what do the Rates correspond to in tblRates? RateDescription is really RateUnit

what is GST?
Remove 0 as the DefaultValue for numeric foreign keys. From Access Basics, "Change default value of all numeric foreign key fields to Null -- the default for all numbers is 0 (versions below Access 2007), which will never match with a sequential AutoNumber field -- not changing it will prevent you from being able to enforce referential integrity if it is not specified. If referential integrity is enforced, as it should be in most relationships (unless you have a good reason not to), a default value of 0 in a numeric foreign key field can cause other problems, such as append queries that fail."
Hi Crystal,
yes spelling isnt one of my better fortaes (propably spelt that wrong as well)
Ok, JobDates.... was planning on using this as a way of looking for records e.g if part of the quote had to be changed it could be done via client and between to dates.
Rates , is the ammount charged per kilometer, meter, day etc. The user could go in and set this up and update it at any time. The selections would be from a combo box. This way ... well in my way of thinking, the previous records would all change when someone put in a different rate, but if they are able to update and choose the updated one it would all stay the same.
GST is Goods and Services Tax which is 10% seeing the client would be providing a service he has to put 10% on top of the invoice total.
Regards Dave
Rates -- so these are all rates for fencing? Once a quote or Job is created, you need to store the rate that is being used so that, in the future, when you change your default rates, your historical records are ok -- and you may choose to store the calculated value -- but if some of the parameters change and you need to recalculate, you will need the rate that was used. You would still store the RateID so you know what the Unit is.

Rather than a table for GST, it would be better to have:

- TaxTypID, autonumber
- TaxType, text
- TaxRate, number

Then, you would have a table like the following:

- InvTaxID, autonumber
- TaxTypID, long -- FK to TaxTypes
- TaxAmount, currency -- this will be calculated but, since it is important that these things balance to the penny, you will want to store the value that you charged so, if the rate changes in the future, the historical records are ok
This gives you the ability to incorporate other taxes that may be imposed in the future
A Discussion on Storing Calculated Values and Audit Trails
I shall get my head further down and get into everything you have stated above and once done I shall send it on to you to have another look if thats ok.
Thanks you ..Dave
Hi Dave,
ertainly! It will take some time to do all these things ... also, read the Normalization and Relationships sections of Access Basics again ...
Hi Crystal,
gone through and done pretty much everything I think that you asked.
I have to say I dont know how to thank you, you have opened my eyes up to so much and you have no idea how much I appreciate it thank you thank you thank you.
Ok bring it on...how does it look and whats the next step. is everything so far ok?
Regards Dave
Hi Dave,

thank you sad.gif

looking better -- but not done. In carpentry, you 'measure twice, cut once'. With table design and relationships, you 'design 50 times, then continue'. Table design is an iterative process...

be sure to DELETE the DefaultValue of 0 (zero) that Access automatically creates when you define a number for foreign key fields (ie: InvTaxes.TaxTypID)

InvTaxes needs InvoiceID

still do not understand the purpose of the JobDates table

shorten fieldnames -- ie:
[Number of Days Required] --> NumDays
(this should also have a numeric Data Type, not text)

ield descriptions: nice to see them -- but cut out unnecessary words. Everything is 'entered'
Enter the type of consumable you need for this job
Consumable Type
Enter the name of your company
--> Your Company Name

turn on the Indexes window while you are in table design

Access creates all kinds of indexes 'for you' -- and most of these, you do not want! For instance, in tblLivingAway, there are TWO indexes defined for AwayExpID -- the only one you want or need is the PrimaryKey index.

For now, unless you have a specific reason not to (like you made a Unique index), I would recommend that you delete all indexes except the PrimaryKey index in each table.

For more information on the, re-read the Normalization and Relationships sections of Access Basics. I realize this is a LOT to take in at once -- that is why I recommend reading Access Basics once a week -- and here is how I recommend you do it:

print it out. with a pencil, put a ? in the left margin of any paragraph that you skipped or was confusing. Each time you read it, you can erase more pencil marks <smile>


There is still thought that needs to go into how you are tracking your expenses. Ideally, ALL expenses for a Job will be in ONE table like JobExpenses: Consumeables, Living Expenses, Equipment, etc...

Orealize you track different fields for each expense type, so you could have related detail tables to describe that. This is not an easy concept to get your head around. If you had a couple sample records in each table, I could explain this better.

The Invoices table will be a header table -- with things like InvoiceID and JobID. You will have an InvoiceDetails table (or you can use the JobExpenses table) for the lines.
Morning Crystal,
dont want to sound stuipud but not sure on how to turn on the indexes window, from what I understand you are saying the index is the name AwayExp all that is needed is ID and set to a primary key?
Am going to sit down today and read about Normalization and Relationships like a man possessed.
Table dates from what Im seeing now that everything is falling into place isnt needed and when you look at it you can have date feilds that are set to todays date when opened....simple. I will delete it.
Oundersatnd totally what you are saying about tracking of expences. The reason i had them in individual tables was for easy tracking purpose, its simnply the way I have been shown in the past how to do it but at the same time is was just for one individual feild i.e GST rate or hourly rate.
So if I have a table Job Expences it would have the expence type in one feild and the cost in another, how do they go about finding the item updating the price?
I will go into it now and add some sample records and send it on.
Regards Dave
Hi Dave,
I have attached a screen shot showing the Indexes Window in the Table Design and the icon that toggles its display on and off
on the expenses -- I will need to see some data and learn more about what you do before I can advise you in detail
Hi Crystal,
I see what you mean about the indexes in the tblLivingaway there is two and like you said there only needs to ne one the primary key, going to read up on it so I get a total understanding of it.
Have gone through and shortened a lot of the descriptions etc, deleted all the defaults 0, also deleted the JobDates and added some info into the tables for you.
Not long ago a received a phone call from the person Im doing this for and he has asked to have it so it can do employees wages, not sure what you think but I was thinking to get what I have so far up to speed and all clear in my head then go ahead and add those details as a bit of a test, what do you think?
Hi Crystal,
have found myself playing around with the Table analyzer and not sure if I should be playing around in it to much, can see what its doing is it worth taking a copy of this application and having a play with it to see what the differences are?
There is so much in the help files on this show plan, feel like a child in a lolly shop
Hi Crystal,
from what I can make of it a Index is used to find and sort records by using 1 feild in the table, I have therefore gone through and made the primary key the index in all tables, is this ok?
Have also deleted tblInvoice and used tblJob to do everything, after doing some reding it made total sence to do it that way.
Have added a wages section as part of the quote section.
Just have a prblem getting my head around tblInvtaxes.
If you have a moment would you mind having a look.
Have found the Access Basics a incredible source of information, you should be crowned <smile>
I have been stairing at that relationship window for so long it all I can see where ever I look....I doomed!
Regards Dave
Was just sitting here thinking about the rates per, hour, kilometer, meter etc. Seeing that they want be known until the end of the quote I have deleated the rate feild out of tblrates just leaving it with rate discription,hour,kilometer etc and in tblJob have added a feild to hold a calculation on the total (sum) of everything in the quote. What Im thinking is all the client will have to do is add the ammount of kiloeters, meters etc and the application will do the calculation so they end up with a ammount to charge per rate description .
Regards Dave
Morning crystal,
I have done the tables to store the calculations (wrong) so I have to create another table to store the item description, cost and quantity I think!.
Will have to do this for each table involved in the quote or will all the items like living away expences and job hire item all go in the one table?
Regards dave
sorry, I missed the notification for this thread ... have posted a reply here:
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.