Re: Clearing Multiple Forms using Add New Record button.

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

You are taking the right steps.

Getting things correct now will save you a whole heap of pain later I can guarantee you!

EDIT: This is probably a better link http://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.html

Work through the courses available, they are excellent!

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

You are taking the right steps.

Getting things correct now will save you a whole heap of pain later I can guarantee you!

Thank you all for your help.

There are duplicated fields within this (not sure why, but hey, the new method will eliminate these).

My next question is, I have a field called Product Group in CRM but that is duplicated in Accounts. I will do away with the field in Accounts and when creating the form I will take the field from the CRM table and display that data within the Accounts form, so that it can be displayed in two different places, which is what the company want.

The question is, when creating a record in CRM, can I somehow create a blank record in the other sections too, so that the tables are all linked together by one ID Number?

What will happen is that, some information can be entered straight away, but others will need to be entered later. I.e. the CRM Contact/Business details can be added straight away into this form, but another user will need these details first before they can then do their job and then fill out the Accounts section.

Does this make sense what I'm trying to achieve?

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

Have a look at the other link I posted in my edit, it is a better more thorough explanation.

Table design is imperative to getting your database to work properly.
Currently, you are jumping ahead of the design stage and worrying about how it will work.

Take a step back and break down your business process and data requirements into things that you can move around on bits of paper. If you cant do it on a whiteboard or with pen and paper, then I can promise you can't do it in Access.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

Doing that as we speak. Putting a doc together and going to speak to the dept. heads.

Thanks!

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

Sorry, me again, I've broken down the form into different tables and it will look like so in Access. The colours indicate where they are duplicated, so if they are duplicated in the second instance, they will be removed from the table.

As I understand it, between the tables, Access needs something common. I am going to setup the AutoID Number in each table and have it link that way but as I mentioned before, not all of the information will be filled out straight away. The majority of the CRM should be filled out straight away but some things like IL1 Code/IL2 Code will be filled out later as the users at the company work through it.

Am I on the right path in using AutoID Numbers to link the tables? I want a blank record to be created in other tables whenever a new Trading Name is entered and a new AutoID Number is given.

I.e. Trading Name of Grey Goose will be AutoID 1 in CRM and 1 in the other tables. If I then start a new Trading Name with a name of Black Label it will be AutoID 2 in CRM and 2 and so on and so forth.

Am I on the right track? Following that blog, seems to be my understanding that AutoID won't work and it needs common fields?

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

Which is the master record?
Assuming it is the CRM (And I'm not sure it is at this point) you would have the AutoID field and you would call it CrmID. This is known as the Primary Key.

Your related tables Marketing, PubInfo etc would have two fields, let use the marketing table for this example MarketingID - an autonumber, and fkCrmID a number field.
this is known as a foreign key. This would be used to store the CrmID that the marketing record relates to.

Now where I think you may be not right, is that I would have thought that realistically your customer details table might be the starting point? You only have a customer once but might have multiple CRM records for them?

I don't know your business, but maybe a brief overview from 1000 feet would help us to break this down better for you?

For instance, your Publisher contacts would presumably relate back to a single publisher?
And what are IL1 and IL2?

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

Which is the master record?
Assuming it is the CRM (And I'm not sure it is at this point) you would have the AutoID field and you would call it CrmID. This is known as the Primary Key.

Your related tables Marketing, PubInfo etc would have two fields, let use the marketing table for this example MarketingID - an autonumber, and fkCrmID a number field.
this is known as a foreign key. This would be used to store the CrmID that the marketing record relates to.

Now where I think you may be not right, is that I would have thought that realistically your customer details table might be the starting point? You only have a customer once but might have multiple CRM records for them?

I don't know your business, but maybe a brief overview from 1000 feet would help us to break this down better for you?

For instance, your Publisher contacts would presumably relate back to a single publisher?
And what are IL1 and IL2?

Correct master record will be the CRM and the fact that it has the Trading Name on it.

Thank you for your explanation, I will adjust my tables and have the foreign key in there. Am I correct in thinking I should de-duplicate the fields too?

The way the company works is that one of the directors would get the CRM Details/contract first from the Publisher and then my other users would fill in the relevant information. I know it's illogical but that's how it works here.

We are a publisher distribution company, things are a bit all over the place and I'm trying to bring IT/Technical processes inline and put some new procedures in place.

Yes, contacts in the CRM would relate back to one Publisher. IL1 and IL2 are specific codes we have on our distribution system but in order for me to set that up, I will need details in the CRM file filled out first.

Hope that is clear and makes sense?

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

If the field is in one table it doesn't need to be in another, provided you can link back via the master (or top-level) record, that's the purpose of the FK field.

By doing this you will only create records when you need to.

If your Publishers are regularly used then what you should have is a publishers table and store the PublisherID as a FK in the master record, same with Products.

You should have a products table and store the ProductID as a FK in the master record.

So you end up with no repeating information and tables to look up commonly used values, this avoids typo's and duplication.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

If the field is in one table it doesn't need to be in another, provided you can link back via the master (or top-level) record, that's the purpose of the FK field.

By doing this you will only create records when you need to.

If your Publishers are regularly used then what you should have is a publishers table and store the PublisherID as a FK in the master record, same with Products.

You should have a products table and store the ProductID as a FK in the master record.

So you end up with no repeating information and tables to look up commonly used values, this avoids typo's and duplication.

I agree, why it's there multiple times is a bad business decision and needs to be cleared up. Some users still want it duplicated but if the information has already been entered, I can just add it to the form and have it defaulted to the previously entered information, can't I? I.e. have product group entered in CRM and when Marketing is opened, it will be defaulted there.

Product Group is already in a product table. I created that after reading a few more blogs. I found this one helpful too https://www.oreilly.com/library/view/fixing-access-annoyances/059600852X/ch04.html. A few of the fields have drop down boxes and yes/no check boxes.

Thank you for your help again.

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

You wouldn't default it per se, you would simply look it up from the related record. Once you have it stored once in the correct place you can always "find" it through table relationships.

That link is a good one for explaining the normalisation process.

I think you are getting there though.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

No - you can create the tabs within a floating form, which is what probably 90% of the developers on here use.

Can you see the controls Page33 and Page34 below;

This is a tab control with a subform on it, as you can see the main info is always shown in the top e.g your client name/account number, then each tab would have the department-specific info on it.

Does the picture help?

I've tried to recreate this, but it just gives me a table when I open the form to actually use it. I don't want that. I still want it to resemble a form.

How do I do this?

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

You need to set the subforms default view to Single Form or Continuous form within the form design, exactly the same as a normal form.

Did you drop the table or the form onto the tab page?

If you strip the data out of your tables and upload what you have (zip it) I'm sure we could get it working for you.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

You need to set the subforms default view to Single Form or Continuous form within the form design, exactly the same as a normal form.

Did you drop the table or the form onto the tab page?

I created a tabbed form, then within that I created a subform.

I assumed that was the correct way?

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

Correct. The subform should be a form with the information you wanted on it, placed on the relevant tab page.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

Correct. The subform should be a form with the information you wanted on it, placed on the relevant tab page.

Let me try again.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

You need to set the subforms default view to Single Form or Continuous form within the form design, exactly the same as a normal form.

Did you drop the table or the form onto the tab page?

If you strip the data out of your tables and upload what you have (zip it) I'm sure we could get it working for you.

I can't find where to change the subform default view. I've tried looking at the Property Sheet of the Subform but there's nothing there. I don't see it anywhere else either. The image below shows me a table but this isn't what I want. I want to see empty fields, like the top part of the form, so that the user can enter in data.

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

Nevermind, managed to figure it out, how do I hide the this secondary box?

If you look at the screenshot, you'll see Principle Contact shows again but grayed out and with a * next to it. I assume this indicates a new record but how do I hide it?

I only want the user to see the fields that they are entering data for, i.e. the top part of the subform.

Does that make sense?

 

[Minty]

Re: Clearing Multiple Forms using Add New Record button.

Set the subform to single form, instead of continuous.

Normally (but not always) a subform is used to enter multiple related records. Hence the ability to see many contacts for a customer for instance.

 

[isladogs]

Re: Clearing Multiple Forms using Add New Record button.

If it is a continuous subform, reduce its height and/or change it to a single form.
However, the tabbed subform already says record 1 of 1.
What do you see if you slide the vertical scrollbar down?
It may be a holder for a new record

 

[TPS]

Re: Clearing Multiple Forms using Add New Record button.

If it is a continuous subform, reduce its height and/or change it to a single form.
However, the tabbed subform already says record 1 of 1.
What do you see if you slide the vertical scrollbar down?
It may be a holder for a new record

It was set to continuous yes, changed to Single and it worked correctly.
The vertical scrollbar slid down would show a holder for a new record.
Now I just need to add in the other fields and make it look good/work well.

Set the subform to single form, instead of continuous.

Normally (but not always) a subform is used to enter multiple related records. Hence the ability to see many contacts for a customer for instance.

Single Form worked.

Thank you. I will progress and ask more questions if necessary. I will also press the thanks button on a few posts too.