# Solved: Access 2007 Form does not update table



## CVAadmin (Oct 14, 2010)

I am currently building a database to track uniform service orders. I have created tables for Customers / Employees / uniform# / uniform options (individual tables for each option)/ Service Item (IE type of repair) etc. I have linked everything to the unique uniform #.

I have a Order ID Table which has the fields "OrderID" "Customer Name" "DateIn" "DateOut" "Uniform#". I have a Order Items Table which has the fields "OrderID" "Service Item" "Service Item Quantity".

I have created a form "Work Order". The OrderID is an Autonumber from the Order ID Table. When I type in the Uniform # the workorder autofills all the information pertaining to that uniform. Ie: Customer Name, Employee Name, Colour, Size.....etc. I input DateIn and DateOut. The items I input myself ie: uniform#, datein, dateout all update my order tracking table just fine.

The problem I am having is that the autofilled items ie: Customer Name, employee name do not update the order tracking tables.

I have subforms that have dropdown boxes to select the Service item, which in turn autofills the price.
I input the quantity of the selected item. All the inputs are linked to the Order ID Table and the Order Items table. These update the Servicetable just fine.

The whole idea of the database is so the seamstresses can fill out the order form for repairs to each uniform as well as wash rate and rental rate. Then at the end of the month I can print off a report for each customer showing the details for each uniform and total the cost of all services.

Any help would be greatly appreciated as I have a fast approaching deadline to get this finished.
I will monitor this from home as well as work so I can be available for any questions or clarifications.

Thank you
Li


----------



## Ziggy1 (Jun 18, 2002)

I would venture to say that you are trying to duplicate data into a table when you don't need to. remember Data is "related", so I am guessing you are seeing "ID's" in the tables, this is the connection to the data, which can be linked in queries.

I think you should indicate why this is causing you a problem, if it is a report, then you need to make a query that links the data and use the query as the source of the report.


if I am incorrect then post a copy of the DB without personal data.


----------



## OBP (Mar 8, 2005)

CVAadmin, welcome to the Forum.
As well as the concern that ziggy has expressed, which I completely agree with, I am also concerned by this statement "individual tables for each option".
This sounds like a nightmare to collate the data after entry.
How many "options" do you have?


----------



## CVAadmin (Oct 14, 2010)

I have attached a copy of the database in question so you can see what I am talking about. Much easier that way. I will go in to review what I have done with your comments in mind and see where I went wrong.

Thank you for responding. Any further advise would be greatly appreciated.
Li


----------



## OBP (Mar 8, 2005)

Sorry, I can't open Access 2007 databases, only Access 2000-2003.
Can you show me a Screenprint of your Table Relationships?


----------



## CVAadmin (Oct 14, 2010)

Here is a printscreen of the database.
Li


----------



## OBP (Mar 8, 2005)

Sorry, I can't open Word 2007 docs either can you save it as Word 2003 format please?


----------



## CVAadmin (Oct 14, 2010)

Sorry about that I should have clued in. Here is the relationship snapshot and the database in 2003.
Thank you
Li


----------



## OBP (Mar 8, 2005)

OK, looking at your Relationships you have far too many tables, which is not the usual problem as most users try to make it all with just one table.
Do you know about the "Many to Many" Linking Table?
The concept is that you take data from 2 (or more) tables and link them in a 3rd table where the 1 relationship is on the 2 main tables and the many is on the 3rd table. It allows you to link more than one Customer to the same Inventory Item etc.


----------



## OBP (Mar 8, 2005)

Sorry about the Many to Many question as I see that you are using it for your OrderItems.
Whay do you not have the Coverall Options in just one table?


----------



## CVAadmin (Oct 14, 2010)

I started out with the coverall options on one table....."Coverall Option" table. I thought that was part of my problem so made them seperate tables. That would be easy enough to convert back.
I guess where my problem lies is with the customer name in the OrderID Table not linking....I think.
In the end what I really need to achieve is a report the brings up "Customer Name" "Employee Name" "Coverall #" "SI Description" "SI Qty" "SI Rate" "General Repair Description" "GR Qty" "GR Rate" "Cover01 Qty" "Cover01 Rate" "Cover05 Qty" "Cover05 Rate" by "DateIn" with a total per "Coverall#" and a Grand Total for the period selected for the "Company Name".
Am I out to lunch ?


----------



## OBP (Mar 8, 2005)

How are you putting the data in to the subtables? Are you using subforms?
What you want to do is simple if the Table data structure is correct.
I am in the UK and have been on the Forum too long already, so I will have to go shortly, but I will take a look at your database in 2003 format and post something tomorrow.


----------



## OBP (Mar 8, 2005)

Your file in the zip appears to be just a Shortcut link.


----------



## CVAadmin (Oct 14, 2010)

Yes subforms for Service Items and the General Repairs. However the cover01 and cover02 are on the main part of the form.
Thank you very much for your time, I will keep an eye out for your response.
Cheers
Li


----------



## OBP (Mar 8, 2005)

Li, can you repost a copy of the database, instead of the shortcut?

Also why is there cover01 and cover02?


----------



## CVAadmin (Oct 14, 2010)

Cover01 is the wash rate. Cover 05 is the Rental rate.
Attached is the proper copyof the 03 database.
Li


----------



## Ziggy1 (Jun 18, 2002)

ODP, not sure if this is going to work, I'll know when I download to my other PC. but I tried to trick access 2007 by creating an mdb during the creation of a new database in 2007.... so attached is an mdb with all the objects imported from the 2007, minus the relationships, don't think I can bring those accross.


*** Edit... Success, ODP you should have something to work with now.... except no relationships....so what I did was went to my PC with Access 2000, created a blank mdb ( my trick using Acc2007 didn't work)....so I used a true Acc2000 version, then brought it over to my Acc2007 pc, imported all the objects and whoila... here you go, at least now you have something tangible....


----------



## Ziggy1 (Jun 18, 2002)

you have all these options on the work order form, but you don't have a place to put them, and the form doesn't even have a customer name field. The work order form looks like it should be bound to the order ID table, after that your subforms will work properly, but still need to figure out where all the combos are supposed to fill in


----------



## Ziggy1 (Jun 18, 2002)

you need to clarify what you are trying to accomplish, what I see is you have your cusomer table, first step is enter the customer detail, next create the work order from where you can select the customer ( you don't have it on the work order form?) you want to create the order "Header" with info related like order number, reference numbers etc ( alll one to one), now you want to enter the "Order Details" ( one to many), this is where you select the items ordered.

To me the form is not properly connected as the records don't switch, to start just change the work orders form Control source to point to the order ID table... then run your form, the combos won't work because you don't have a place for them to populate, but you should notice that by changing the main record selector those sub forms will switch in sync.. you only have 2 order ID records when you set it like that.


----------



## OBP (Mar 8, 2005)

Ziggy, you seem to be handling this problem rather well, so I will see what CVA admin has to say before I do any more on it.
This Unbound Forms appears to be a common problem with Access 2007 users.


----------



## OBP (Mar 8, 2005)

Ziggy, actually I think the main problem is the Mainforms Record Source, it is a query with 3 tables, whereas ideally you would have 1 table per form, so a lot of the data that is in the OrderDetails table should probably be in the Order ID table. It would depend on whether or not the Items can be in multiples, if they can then they should be in a Subform.


----------



## Ziggy1 (Jun 18, 2002)

OBP said:


> Ziggy, actually I think the main problem is the Mainforms Record Source, it is a query with 3 tables, whereas ideally you would have 1 table per form, so a lot of the data that is in the OrderDetails table should probably be in the Order ID table. It would depend on whether or not the Items can be in multiples, if they can then they should be in a Subform.


I know, that is what I was saying in my other post, to use the orderID table as the main...if that change is made the subs start to work, but there are too many other fields that have no where to go.


----------



## CVAadmin (Oct 14, 2010)

Good Morning. Thank you both for the advice. I am at home right now, but will stop in at work later to pull up the file you downloaded Ziggy. I am understanding some of what you are saying, more should become clear when I have the database in front of me.
I am a little unsure when you say there is no customer field on the workorder form as it was located right under the coverallID#. When I typed in the coverallID # it would bring up the CompanyName, Employee Name, all the options attached to the CoverallID and the wash and rental rates (cover01/cover05).
I did however get rid of the second subform for general repairs. When I first put that subform in they wanted an item they could input the price into. I realized that the item had to have a value already in the table otherwise it would constantly be updated by the workorder input. So I created a selection of General Repair items with values attached in the Service Item Table.....one less table to work with. (ie: General repair $1.00, General repair $2.00....and so on)

When you type in coverall ID 999999, you will see it pull up the info. I have only filled out all the data for the Customer "TEST" so I could have a full record to work with. The rest of the data entry details will have to be done as they go along. The I created the workorder so it would update all the tables. So as each coverall is entered they can update the details for each one.

Did I come at this from the wrong direction?

I'll post more when get a chance to get to the office and see what the file contains. Thank you again for your help.
Cheers
Li


----------



## Ziggy1 (Jun 18, 2002)

the other DB I attached was for ODP, I'll upload something shortly, so Employee is the customer then?

Ok I attached something, still as 2007 though, but all I did was link the work order form to the workorder form and changed the employee combo to drop into the customer name ( just added the control from field list).

If you explain your process a little better, we can just build it the way it needs to be, I'm not quite sure why you are selecting a coverall and then other details after.

like I was trying to say, I envision selecting the customer, then adding the coverall detail...however the coveral seems to be your focal point so the main form should have the coverallID as the primary and then linked to the sub as a foreign key.

the one I attached right now is not much different, I will rework the tables a bit to work from the "Coverall" id.


----------



## CVAadmin (Oct 14, 2010)

Yes the CoverallID would be the focal point.


----------



## Ziggy1 (Jun 18, 2002)

ok,

I stripped down the work order form for the time being, the way I am approacing it is the you will define the coverall in the coverall Inventory table with all its attributes and owner.

Then when the seamstress is working on the coverall they will refer to the coverall number first which is selected in the order, then they will add all the charges. The order form should not be concerned with the attributes of the coverall as this should be predefined ( as I noted above).

we can use queries to tie all the related tables together for reporting, and if you really want to see it on the order form it can be referenced, but should not be a selectable option ( just complicates things).

Note: the work order form is bound to the orderID table as the main, then I linked the sub using the CoverallID, you can see that *** you add the details of the charges the coverallID repeats for each record this is what relates it later


----------



## CVAadmin (Oct 14, 2010)

I see where you are coming from. I'll try it out in the morning as I am not able to get to the office today. Family demands.


----------



## CVAadmin (Oct 14, 2010)

I have been revamping my database based on what you sent me Ziggy......which is awesome by the way THANK YOU!
I have a couple of questions though....
1) On the OrderItemsID Table there is an "order Number" column but it doesn't have any info in it.....so is this supposed to be linked to something?
2) On the workorder the "company name" on the main page requires input as apposed to the one on the coverall inventory subform which autofills. Should this not be linked somehow to the coverall # so it autofills.
3) Also when you click on the "coverall inventory" button on the workorder.....should not the form that appears have the coverall# from the first form appear?

Cheers
Li


----------



## Ziggy1 (Jun 18, 2002)

Hi CV, I just glanced over your points, and you are correct in your assumptions, I just did not go into a great amount of detail, I just wanted to get the "Concept" started. I have a class to go to, so I will check back later tonight or in the morning and upload something with more functionality.


----------



## CVAadmin (Oct 14, 2010)

You are awesome Ziggy....I'll keep plugging away. The more I play with it the more I understand. I look forward to hearing from you.
Thank you
Li


----------



## Ziggy1 (Jun 18, 2002)

Ok, I;m just uploading this version, its still rough, I use a variety of methods for referencing data, so I'm not completely happy with this one, but the coverall fields are ferencing the combo "Columns", like I said the way I have it only needs to be for a visual display as you should edit int eh coverall table, which is also now restricted to the selected coverall.


I'll explain more if you don't know what I have done.

^^ note that the if you click the build button on the coverall combo you see all the columns, from left to right they are numbered, but I don't display them in the combo, but reference them in the columns. I don't think this method works well because the table has lookups.... I'll change it later.


----------



## CVAadmin (Oct 14, 2010)

Good Morning Ziggy.
I just got in and have downloaded the new file. Thank you.
I'll review it and implement changes to my original. I'll update you on my progress.
Cheers
Li


----------



## Ziggy1 (Jun 18, 2002)

Hi Li,

i want to look at the link later on the subform, even though it works the little voice in my head tells me not to do it that way.... I went that route because I was thinking about the relation between cover and the work done on the cover, but I think the same can still be achieved between order ID's. 

I should maintain the link as OrderID and Order detail ID, which is the field you were asking about.


----------



## CVAadmin (Oct 14, 2010)

I know I looked at that and ended up confusing myself......so I have been working with my original version and trying to make it work and reimplementing things from your two files. Just when I think I have it figured out, something else doesn't work. I have to have it completed tomorrow as I am already 5 days overdue. So I'll keep plugging away. I obviously have some serious learning to do when it comes to expression building and such. I'll keep checking the forum for updates.
Cheers
Li


----------



## Ziggy1 (Jun 18, 2002)

Ok, I made a bunch of changes. as I noted previously I reverted back to the order.order detail...here is where the Order number field comes in on the subform...for each detail on the subform, the current order number will repeat ( its the "Foreign key").

I cleaned up all the controls and made them more meaningful, you will also note I gave you an example of a Dlookup, which I use to reference the employees name...this is because as the table (coverall) shows the name, the lookup references the ID, so I use the Dlookup criteria to find me the name ( I know I could tweak other areas to make this work different ways)...this is just another example of expression use.... again the key here is we don't need to try to push related data into other tables.

I setup a query as an example of how to tie the data together.... 

1st I made a query between the "order" and "orderitems Id" tables, the key here is, setting join properties, by default query joins show only matching records from both tables, I want to show ALL records from the main and the ones that match from the sub....this way you see all the order records, subs not matching would be blank.

2nd I then take that new query (qryOrderDetailed) and bring it into a new one and also add the cover all inventory table, I know you already started doing this, but think about what I'm saying because I haven't done it all.... for example you probably want to create a coverall Inventory query that will show all the lookup values.... so all those lookup table should be included, and be sure to set the join properties to show ALL the records of the coverall Inventory table.

*** to edit join properties, just double click on the join line, and you should see the box with 3 choices, select the one that shows all the records of the primary table/query.

** my query example is filtering based on the order number, you could have one based on other criteria....this is another example of expression.... "Filter by Form"


*** you shouldn't name objects ( tables/forms reports etc) with spaces, this can get you into trouble later, also recommend prefixing them ... qry, frm, rpt it makes it easier to identify in certain views.

so I hope this is less confusing, just pop me a note if yo don't understand how something works


**edit, glad I checked before logging off....attached now


----------



## CVAadmin (Oct 14, 2010)

Thank you Ziggy. I'll look at it in the morning.
Oh I just noticed there is no attached file.
I'll check again in the morning when I get to work.
Cheers
Li


----------



## CVAadmin (Oct 14, 2010)

Hi Ziggy
I've been working away and everything seem to be going fine. I just have to generate my reports now and I should be good. I'll keep you posted.
Li


----------



## CVAadmin (Oct 14, 2010)

Hi Ziggy,
I downloaded my final copy less reports as I have some questions.
If you get a chance to look at this I would appreciate it.
I'll post my questions when I get home.
Cheers
Li


----------



## CVAadmin (Oct 14, 2010)

I guess my main question is how to calculate the totals per item per coverall. Ie cover01 Qty of 3 x cover01 Rate of $2.00. I have to read up on calculated queries.
Second would be totals by company per selected time period Ie Sept 1 to 10, 2010. I assume this would be based on parameters....again I need to do some reading tomorrow. Any info would be great.
Cheers
Li


----------



## Ziggy1 (Jun 18, 2002)

I just shut down my LT with 2007, but I'll try and explain.

You can do the calculations in the query, form or report.... it depends on what makes sense or requires the least effort. in a query, just go to a blank field and type:

Expr1:[cover01 Qty] * [cover01 Rate]

Expr1 is the label name, so name accordingly

in forms and reports add a control, and in the control source you can do the same except like this...
** note for future reference, don't put spaces in field names

=[cover01 Qty] * [cover01 Rate]

on your second question you need to use grouping in the query or the report. In a query you need to look for the "Totals" option ( look for a tool icon that looks like a capital M turned on left side)....when you click it all fields in the query default to "Group by", go to the numerical "Qty" ones and change to sum....where as a RATE might remain as group by because it repeats.... you have to mess around with it to get the results you want...start with a few fields to experiment all the while checking the results between changes.

Reports are more complicates so I don't have time to explain, but look at grouping options.


----------



## CVAadmin (Oct 14, 2010)

Awesome! Thank you Ziggy.
ttyl


----------



## Ziggy1 (Jun 18, 2002)

no problem Li, let me know how it goes.


----------



## OBP (Mar 8, 2005)

Nice one Ziggy. :up:


----------



## Ziggy1 (Jun 18, 2002)

thanks OBP


----------



## CVAadmin (Oct 14, 2010)

Well I have managed to work out most of my querks. I have solved all of my calculation and report issues except for one. So am working on that today. I have to start training tomorrow morning at 9am. LOL
Thank you for your incredible help, I would not have been able to get this far without you. ((((((HUGS)))))
Li


----------



## Ziggy1 (Jun 18, 2002)

thats good glad to help, and even better you are trying to figure out things for yourself, its the fastest way to learn.


----------



## CVAadmin (Oct 14, 2010)

Hallelujah! Hallelujah! I'm Done!!!!!!!! It is a thing of beauty. Everything I had hoped for. Thank you Ziggy! I learned so much I might even try some more projects.


----------



## Ziggy1 (Jun 18, 2002)

wooo hooo lol.. good for you


----------

