How to save new form records

[sud2017]

Wish I am not very far from the right track but is it something you were suggesting in Post # 73 (see diagram). This is closer to what I was thinking too but I have few confusions on flow and design of forms. Could you please review and advise. Pardon me if I have lost the direction. Thank you for your time!

Brilliant! Hope you are having fun.

There are a few links missing, one repeated field, and one missing field. The links are important, the other things will be fixed as you go into form design.

1 and 2 will be a main form/subform setup - the main form containing everything to do with the quote header and the subform carrying everything to do with the items, linked by quote ID.

For 3 you may want to think about using the same main form/subform setup but adding a listbox which shows all opps for that job. The user will click on them one at a time and the subform will then show the details for that opp, same as for 2. There is nothing to do with jobs that the user is going to change or any value they need to enter, so a listbox display is sufficient.

My philosophy is to give the user as little as possible to complete their task - minimal clicking, scrolling, forms opening and closing. And to have as little repetition as possible in order to cut down on the maintenance and possibilities of things going wrong in future development. Believe me, you are going to forget everything in a couple of months, then you will have to come back and say - "Now why did I do that?". So much easier to keep things to a minimum.

Before you begin, make 100% sure that
1) user will ALWAYS select a job or an opp, never quote for purely parts


2) user will ALWAYS want all opp's associated with a job and will never want to remove one from the list or add another
3) user will ALWAYS want all parts associated with an opp and will never want to remove one from the list

These things are very important to get straight before you go into form design as they will totally change the way the form is designed and will be hard to change later. You are being very strict, which is good and necessary if that is the way your company operates, and also makes it far easier to develop. But it does tie you down to giving the user no choice and no flexibility.

[sud2017]

This will make the database look organized and surely would help avoid confusions. I just learned that PK and FK names SHOULDN'T necessary be the same, which I had initially thought of. Thank you very much. I'd use this convention when revising my table tonight.

A couple of my naming conventions is if the PK field is an Autonumber, I use "ID" in the name (you have) and I use the suffix "_PK".
For the foreign keys I have a suffix of "_FK". Again, for me, it makes it easier to know which are PK fields and which are FK fields when creating complex queries.
The order of the fields in tables is the PK field is the first (top) field, then FK fields, then other fields. Grouping the FK fields makes it easier to find them in the relationship window.

I find this especially helpful when writing/reading SQL....

So for the table OppheaderT, the fields would be
OppID_PK
JobID_FK
Opp_Desc

And OppDetailT would have
OppDetID_PK
OppID_FK
PartID_FK
OppDet_UnitCost
OppDet_Qty

[aytee111]

First, work on one form at a time, your picture seems to be showing two different ones at the same time?

Do them in this order - create the form and get it working before moving on to the next one:
- customers
- parts
- opps
- opp details
- jobs

Do those before tackling the quote form.

[sud2017]

A couple of my naming conventions is if the PK field is an Autonumber, I use "ID" in the name (you have) and I use the suffix "_PK".

Hi Steve,

Just hit a hurdle in organizing tables this way. I had no PKs in Autonumber before, but just changed some of them (JobID, OppID, OppDetID) from Text to Autonumber and tried adding prefix and suffix as advised in your post. However, after changing all PKs and FKs to autonumbers (in both parent & child tables), table doesn't allow two Autonumbers. So I have put them back to Text for now. Wondering if I'm missing something obvious here? Thank you for your help.

[sud2017]

Thanks again. I will update with my form design soon. Just caught up with other stuff. Thank you very much for helping me with this project. Much appreciated.

First, work on one form at a time, your picture seems to be showing two different ones at the same time?

Do them in this order - create the form and get it working before moving on to the next one:
- customers
- parts
- opps
- opp details
- jobs

Do those before tackling the quote form.

[ssanfu]

However, after changing all PKs and FKs to autonumbers (in both parent & child tables)

The PK fields are autonumbers.
The FK fields are Numbers- Long Integers.

[Micron]

The PK fields are autonumbers.
The FK fields are Numbers- Long Integers.

sud2017: and those 'child' longs are the PK parent/related values (in case you were wondering)

[sud2017]

Thank you Steve, Micron and Aytee111 for you help with organizing my tables. Here's how it look now. If you have any other comment (missing or wrong field/relationship etc.), kindly suggest, so I can avoid banging my head later. Please note that I ONLY have these PKs in Autonumber - JobHeadeID, OppHeaderID, and OppDetailID, as I am thinking I might want the remaining PKs (QuoteID, ItemID, PartID, CusID) in different naming convention that our company currently follows. For example, I might want QuoteID values as something like Q1708-201, that will match with our company's numbering system. Also, please advise if there's anything wrong in having few PKs as Autonumbers and others as TEXT. Thanks so much for your time.

[sud2017]

Hello Aytee111: I posted my updated table design in last post, and have completed designing these forms (haven't entered any data yet). Wondering if the next logical step would be to start entering test data or go ahead with Main Form/Subform design. Currently I have all the fields from tables to the forms. Any comment on my forms so far? Thanks so much for your time and help again.

First, work on one form at a time, your picture seems to be showing two different ones at the same time?

Do them in this order - create the form and get it working before moving on to the next one:
- customers
- parts
- opps
- opp details
- jobs

Do those before tackling the quote form.

[ssanfu]

Please note that I ONLY have these PKs in Autonumber - JobHeadeID, OppHeaderID, and OppDetailID, as I am thinking I might want the remaining PKs (QuoteID, ItemID, PartID, CusID) in different naming convention that our company currently follows. For example, I might want QuoteID values as something like Q1708-201, that will match with our company's numbering system. Also, please advise if there's anything wrong in having few PKs as Autonumbers and others as TEXT. Thanks so much for your time.

Uhhh, you can do what you want re PK fields, but PK/FK fields are to link related records in tables.
I do not use Text type fields as PK fields, ONLY Autonumber/Long fields types. If you need a text field to be unique, create an index on a field.

See
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers

(read each site a number of times.... I am up to 18 times on each )

[orange]

I have casually glanced through this thread. It seems to me that there the focus is on forms etc, but the underlying data model/structure (tables and relationships) has not been tested/vetted against the requirements. The diagram in post #83
seems to have too many crossed lines/paths, but I don't know the requirements.

Here is a diagram I have seen previously in a forum that deals with quotes, orders and invoices. It may be of some value to you.

Good luck with your project.

[sud2017]

Thanks. I just made the text PKs as Indexed (No Duplicates) and hopefully that should suffice. Every post in this forum is new learning and helping me progress with my project.

Uhhh, you can do what you want re PK fields, but PK/FK fields are to link related records in tables.
I do not use Text type fields as PK fields, ONLY Autonumber/Long fields types. If you need a text field to be unique, create an index on a field.

See
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers

(read each site a number of times.... I am up to 18 times on each )

[aytee111]

Once you have the tables designed and working the way everyone wants (!), THEN begin on the form design.

After creating each form, test it and get it working. The one you show, Customers, is blank so I presume it hasn't been tested. Enter data first the way you would like it entered, then try every scenario that the users would/could do. They are imaginative and will try their best to break it - you must get in their heads before them and add preventions of every kind.

In your form, try and change the customer's ID number, for instance.

[sud2017]

Thank you all for your guidance. I have attached my database which contains all tables with established relationships. Also contains some forms though I haven't tested them with data yet.

[sud2017]

Please consider this db for review, which is tested with few sample data. Could you please advice what's missing/wrong in my DB so far. Few comments/questions I have are below:-

1. My FKs are 'No Index' for corresponding PKs that are 'Index (No Duplicate values)'
2. Wondering if I should also have 'PartNo' and 'Part_Desc' fields in my OppDetailsT Table and/or Form? Thinking that OppDetailsF will be subform in QuoteForm, then user would like to see 'PartNo' and 'PartDesc' in QuoteForm
3. Relationship established b/w QuoteHeaderT & ItemsT is 1-to-1 but I initially thought it would be 1-to-many.

Thank you. Your help is much appreciated!

Once you have the tables designed and working the way everyone wants (!), THEN begin on the form design.

After creating each form, test it and get it working. The one you show, Customers, is blank so I presume it hasn't been tested. Enter data first the way you would like it entered, then try every scenario that the users would/could do. They are imaginative and will try their best to break it - you must get in their heads before them and add preventions of every kind.

In your form, try and change the customer's ID number, for instance.