pick your brain...

G

gizmogeek

Registered User.
Local time
Today, 16:30
Joined
Oct 3, 2007
Messages
95
[FONT=&quot]If I save the form it's not going to save all info to the table because there are drop down lists that are chosen from. I was wondering if I should save all information to a different table then print the PO's from a report. If I am way off track please let me know.

Last but not least I did notice that the data in the subforms doesn't save when I save the report. Is there a way I can save data from them at the same time? For example when the user clicks on save it saves all data from form and subform to a new table that will hold all PO's. I also have a problem where the last record in the table gets overwritten. Is this because the ProductCodeNum is not changed?

I have attached a copy of my database so that you can see what I'm talking about.
[/FONT]
 

Attachments

Last edited:
There's no attached database . . .
 
There is no attachment in your post that I can see.
 
Sorry about that. Not sure what happened with that but I attached it again to my original post.
Thanks!!
 
Your table design has serious trouble. Your OrderItems table should contain one record for each item, not multiple items in a single record. Search for "principles of normalization"
 
I did so much research on normalization and the easiest way for me to fix it was to take it out of the flat table it was in. All data was in one table. Not sure how I am supposed to get around the multiples as that is what the client wants to show up on the form. If you can think of a better way to create 5 instances of each then please let me know. I have already done the whole database over.

Also if anyone can answer my questions about the file save to a different table... whether it be a good idea or not I am open to suggestions. Also still can't figure out why on close it only overwrites the last record which is always the first record in the table.
Thanks!
 
You have a query named tblItemListSubform. Is it really a Query or a Table. If it is a Query put qry in Front Not tbl
Included in this Query you have a second Table tblPurchaseOrder but you do not use any part of it in the form so delete it.

When you write code present it better.
Code: 
Option Compare Database
Option Explicit
 
Private Sub Combo6_Change()
    
    Me.PricePerUnit.Value = Me.Combo6.Column(2)
 
End Sub
 
Private Sub Combo8_Change()
 
         Me.PricePerUnit_2.Value = Me.Combo8.Column(2)
 
End Sub
 
Private Sub Combo10_Change()
 
    Me.PricePerUnit_3.Value = Me.Combo10.Column(2)
 
End Sub
 
Private Sub Combo12_Change()
 
        Me.PricePerUnit_4.Value = Me.Combo12.Column(2)
 
End Sub
 
Private Sub Combo14_Change()
   
     Me.PricePerUnit_5.Value = Me.Combo14.Column(2)
 
 
End Sub

The spacing makes it clearer. You have used Combo6, Combo8 etc.
Please give them more discriptive names.

Why do you use .Value. >Value is the default and there is no need for it.

Always use

Option Compare Database
Option Explicit

At the beginning of every module.

You should also compile your code to see if it is correct.

Go to Debug on the Tool bar then click Compile. Not all mistakes will show up but some will. If the code does not compile your Database will not work.

NEVER Join one primary hey to another. Create Foreign Keys for this purpose. Do a Google if you don't understsnd.

You have FIVE PricePerUnit fields. The price per unit should be stored in a separate Table. Another Must do. What if you wanted 6 or 7 or 8. Things change so if someone wanted this changed it would be a Major rewrite. It also breakes a very important rule in normalisation.
 
The tblPuchaseOrder is used on the main form. Then I created 3 subforms off of that. You originally told me to pull the data "QuantityOrdered 1-5, PricePerUnit 1-5, etc. So I pulled them out and put them in another table. Now everyone is telling me to change them again...
I don't have any queries, just tables. I'm sure they'll create tables some day when they want reports.
I do have 1 primary key to primary key relationship because I didn't have any like fields "autonumber" in any table. Not sure what else I would join on. If you remember I struggled to find my joins on my first post to the forum.
As far as the calculations I used the Expression Builder to create them as I didn't know of any other way.
I do appreciate your feedback!
 
This topic has been going on for a while and i don't remember everthing that was written.

Primary Keys are Autonumber and therefore unique.

Foreign Keys are not Autonumber. They are Integers. So one Record in the Main Table can Join to the Foreign key in the other table.

So you have one Table that Stores eg People Names.. The other table use the AutoNumber in the Foreign Key Field so you could end up with Thousands of matching Records. FK used once with eg number 101 which could match many records in the main table where the value equals the PK. In this case 101.

Try creating two new tables and test the theory.

Good night for me.
 
Last edited:
Okay so now I'm at the point where I just have to know why the form data is not saving to a new row and overwrites the other. Put some bogus information in and see what comes out.

The issue I started with was that I didn't have anything to ad to tblPurchaseOrder as autonumber and use it as a primary key (I think that this might be the reason why it's overwriting but don't know how or what to put in place so it does.
They didn't have anything that I could use for an autonumber and therefore had to us ProductCodeNum as it matches in all tables. That's still not giving me an autonumber though.
Thanks!
 
A quick thing - your combo boxes should have the ID information and be bound to a the field.

Your relationships are all messed up. If your relationships form basically a circle you can be pretty sure you did something wrong.
Additionally, you have a field named ID in each table. That is not productive. You should have it named as to what it is. So, if you have tblSupplierList you need to have SupplierID and then in the other table where you want to use it you would use SupplierID as the foreign key.
IN tblPurchaseOrderItems you should not have repeating fields. You should not have QuantityOrdered_2, QuantityOrdered_3, etc. And the TO should not be stored as text. It should be an ID from another table. I did a couple of changes but you will have to do the rest.


Look at the changes I made in the tables and relationships (by the way you don't need to use Cascade Updates as that only deals with the PK field if you were not using an Autonumber). In one of the tables I changed the name to show it should be deleted and I added the SupplierID field which is what should be being used.
 

Attachments

See now I watched all kinds of videos and read all kinds of things but you have actually put it in terms that I can see and understand. From the beginning I kept saying that I had no idea what to use for my PK's and FK's because they didn't have anything other than PO and they wanted to used that as consecutive "next numbers".

I don't think I understand that "To" should not be text. I actually went in and just put that as a subform in the spot where they have it on the diagram of what they want the form to look like. Not sure if I did that right. Did you take a look at the subforms on the main form? Not sure I used them right but hoping I did. I know that I'll have to do the whole thing over again but its the sixth time so I am seeing it in my sleep.

One last thing I don't understand how to get around of QuantityOrdered 1-5, PricePerUnit 1-5, etc. I was originally told to take them out of the main table and put them in a table of their own...
 
Last edited:
I went in and made some changes to the database. I still don't have a good understanding about the repeating fields. Also if I bind the combo boxes to a field then each time I chose something in one of the boxes the rest change to the same thing. If I unbind them then I am able to choose each one independently. I think now that I'm at a good place but still need an understanding of the repeating fields.
 
Read this tutorial on Normalization http://www.phlonx.com/resources/nf3/

You do NOT want repeating groups in your database. Getting your tables normalized is a major step toward having a useful database.

I have just looked at your database - WOW. You need to get your tables identified, then Normalized, then establish the appropriate relationships.

Your tables and relationships as they stand send a message of confusion.

You seemed to have jumped over database design basics and into programming. That approach often leads to frustration and endless "workarounds".

Here are a number of videos, all related to Customer, Order, OrderItem and Items all presented by the same person. The video quality isn't the greatest, but the content should be helpful to you.

Video tutorials:
If you google for videos "normalization langer" you will find a series of free videos by Dr.Art Langer. These are quite good for learning by Watching/Listening rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Complete set of tutorials on Acc2010.
https://www.youtube.com/playlist?lis...FoilxbUY0yUqZP

Here are some other videos that deal with the basics of relational database. Perhaps you have already seen these.
Here are some videos and links to assist in the process that should help put things into context.
Principles of relational Design http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Good luck -- getting the concepts clearly understood before jumping into programming will be helpful.
 
Thanks Jdraw. I think you must be looking at the old database file. I'm a bit beyond that but still don't know what to do about the unbound combo boxes. I've been working with the database pretty much non-stop. Hopefully can overcome the rest of the issues. Still working on it now so when I'm done working on it I'll upload just for a checkup.
Thanks!
 
I looked at the database on your post #1 in database.zip

I think you'll still earn from the videos - especially based on your comments on repeating groups in your #12 and #13.
 
gizmogeek

The information given to you in this thread is high quality. The only problem you have is that it just won't click in your mind.

Combo Boxes a simple as I can make it.

For the sake of this explanination assume you have a Table about people. It would look like this.

tblPeople

PersonPK Autonumber
FirstName Text
LastName Text
AddressLine1 Text
AddressLine2 Text
CityFK Integer

Naturally there would be other fields of information.

tblCities

CityPK Autonumber
CityName Text

Because you use City quite often and you want the name spelt the same every time.

You need to place the value of CityPK which is a Number into the field CityFK in tblPeople.

So on the form you have all the fields from tblPeople. Including CityFK. Use a combo box to look up this value from tblCities. Namely CityPK which will be a number. So you store the Number in the CityFK not the CityName of the town.

In your properties of the Combo box you include two Fields. CityPK and CityName.

The result is that you see the City Name but the CityPK is stored.

The Control source is CityFK

Then you adjust the Column (Field) widths to 0 and 2.5 (In Metric)

From here I can only suggest you compare what I said with an example Database.
 
Thanks Jdraw. I think I was looking at everything too close and under the gun. I don't know how I remembered but today I sat down after stressing and somehow it all clicked. Still working on it now will upload later on. Thanks again!!
 
You must log in or register to reply here.

Similar threads

T
Cancel,Save and Delete buttons for Order form
Replies
11
Views
749
Pat Hartman
P
R
How do I save the layout of a subform after modifying it on the main form's on open event?
Replies
12
Views
366
RickSanchez
R
J
Export data in Work to different folders
Replies
2
Views
348
JPR
J
S
Solved Create new child record with modal dialog?
Replies
16
Views
816
Pat Hartman
P
R
Closing all MDI child forms when Ctrl-F4 not available
Replies
6
Views
556
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom