New to Access - Help

[mccrimmon]

Hello,

Im wondering if anyone can help me.

Im new to access and have read up on my normalisation documents etc.
I've taken all my data and split into tables that I think are logical.

I have images that shows my visio diagram of tables and an example of what I want my form to look like.

Can someone take a look and see if my tables look logical for what im trying to achieve and if I can achieve it though this setup?




The databse is used for logging Fund Activity.

• 1 activity can have many funds associated to that record.
• Each Client and Fund Type combination has a tier1 and tier 2 profile associated with it that will be used for calculated fields on a form.
• Activities should only be added to the Funds table if the Activity Type is a "Launch"

So my questions are as follows:

How do I go about creating a form like my attached example (ie how do I link my activities form into my funds form so that if the activity type is a launch then I can add say 3 funds to the Funds table)

I still want to show the funds even if the Activity Type is not a launch. In this example they are stored but just not added to fund table.

Any help or comments greatly appreciated. My first post in this forum resulted in me getting told to go back and do some reading up so hopefully my table layouts now make a bit more sense.

Thanks

McC

[June7]

One way is form/subform arrangement.

Main form has RecordSource of Activities. Subform has RecordSource of ActivityFunds. The Master/Child link properties of the subform container will synchronize the related records.

Not sure what you mean by "I still want to show the funds even if the Activity Type is not a launch." You want to show all funds in a combobox drop list?

[mccrimmon]

Apologies for the confusion.

I only want the tblFunds to be updated if the Activity Type is a Launch.

If its a Launch I want the fund codes to be added to the tblFunds. If its a Closure then I want the funds to be marked as non active on the tblFunds.

Any other activity type should only add the funds to the Activity Funds table.

If I post up an example of the database are you able to show me what you mean by linking the forms together etc?

Thanks again

[June7]

Creating forms/subforms is basic Access functionality. Access Help has guidelines, also plenty of web tutorials.

Your data structure seems right.

A Launch event means a new Fund for an Activity (could be a new Activity, new client also)? This means data entry to add new records?

A Closure event means data entry to modify existing tblFunds record?

What other event types would mean only adding new record in Activities?

[mccrimmon]

Hi,

Thanks again for coming back to me.
Its much appreciated.

The way I see it working is that the user can add a new Activity Entry through a form.

If there is a any new information required on the lookup tables then this will have to be added by a super user.

So the super will be the only one that can add new Activity Types, Fund Types, TierProfile Details, Currencies, Clients etc etc to the relevant tables.

With regards to a New Activity Type:

• The user will enter all the relevant details shown in my example form
• If the Fund Type is a Launch then the funds entered in the subform should be added to the tblFunds as inactive.
  
  The funds will only be marked active once the user marks that specific activity reference as complete (which is more than likely going to be through an edit record form as the initial add form is really only going to be used to add the details to the planner)
• All other Activity Types should have no action to the funds table, however, the user will still be able to enter the fund codes in the subform. They just wont be sent to the tblFunds.
  
  So for example - I could have an Activity Type as a Transfer In and enter funds BM01 and BM02. These funds are associated with that Activity Type, however, should not be sent to the Funds Table (they would already exist in this scenario anyhow)
• The only other Activity Type that should have a relation to the Funds table is Activity Type Closure. However, the funds would only be marked as inactive once the specific reference has been marked as complete.

So in summary, All Activities will be added to Activity Table and Activity Funds table.

Only Activity Type Launch would add a fund to the tblFunds.

Only Activity Type Closed would mark a fund as inactive once the Activity Reference has been marked as complete.


Does this make anymore sense and are you able to help me out a little? I've attached a copy of the database with a blank form with frames.

Any help or guidance would be greatly appreciated.
I am good with VBA and understand how the data should fit, however, my access knowledge itself is fairly limited.

Thanks

McCrimmon

[June7]

Regardless of the form structure, you need to offer users a way to filter recordset so they can pull up specific record. Check this tutorial for one way to build filter into form http://datapigtechnologies.com/flash...tomfilter.html

You have to decide at what level you want to apply the filtering. The filter can be at the Activity level so to return a specific activity record (just don't allow edits of the Activity data) and a subform will display all its related Fund records. Or the filter can be to a specific Fund record and this would not be a form/subform arrangement.

[mccrimmon]

Regardless of the form structure, you need to offer users a way to filter recordset so they can pull up specific record. Check this tutorial for one way to build filter into form http://datapigtechnologies.com/flash...tomfilter.html

You have to decide at what level you want to apply the filtering. The filter can be at the Activity level so to return a specific activity record (just don't allow edits of the Activity data) and a subform will display all its related Fund records. Or the filter can be to a specific Fund record and this would not be a form/subform arrangement.

I am quite happy that for the Edit Record part I will need to build up filters etc, however, do not understand why I would need that for Adding a New Activity Record through the form?

I'll take a look at the tutorial tomorrow morning.

Would be grateful if you could take a look at database and provide any pointers?

Thanks

[June7]

If you build filter on the Activity then the subform (probably in datasheet view) will automatically synchronize records to the Activity record on main form. Any new records added in the subform will automatically associate.

If you have the form set up to filter down to the Fund record, then form would be structured with options, perhaps buttons "Find Record", "New Record". In Find the user would have to input search criteria of Activity and Fund. The code for New Record could move the focus to a new blank record where user selects Activity from a combobox.

The form/subform approach is probably easier.

[mccrimmon]

I am not understanding why we would be using the filter on adding a new record.

To give you more of an understanding, lets just say for example the Funds table is empty along with the Fund Activity Table and the Activities table.

I go and add a new record through the Activities Form. The Activity reference is auto numbered as 1.

I select the Activity Type as a Launch.

In the subform I would like to add 2 fund codes so add Fund BM01 and BM02 with names Brian 1 and Brian 2.

I dont want these Funds to go to the Funds table until I press a button such as save record. At the same point the Fund Activity table would be updated to have records for Activity 1 and Fund BM01 and Activity 1 and Fund BM02.

The funds table now has BM01 and BM02 aswell with both showing as inactive.



Say I then go and add another record. Auto Numbered as 2.
This time the Activity Type is a Transfer In.

In the subform I add Fund BM01.

On the Save Record button only the Fund Activity Table is updated to show Activity 2 and Fund BM01.

Obviously the Activities Table would show the full information for Activities 1 and 2 at this stage.

Can you please help at all?

[June7]

What you are asking for requires significant code effort and there is probably more than one way to accomplish. Likely would not involve a synchronized subform. One is an unbound form, another is multi-select listbox. I have never used either so don't have specific code. The use of unbound forms and multi-select listbox for data entry are both common topics. Suggest you search forum and Google.

Why Brian 1 and Brian 2?

[mccrimmon]

What you are asking for requires significant code effort and there is probably more than one way to accomplish. Likely would not involve a synchronized subform. One is an unbound form, another is multi-select listbox. I have never used either so don't have specific code. The use of unbound forms and multi-select listbox for data entry are both common topics. Suggest you search forum and Google.

Why Brian 1 and Brian 2?

Brian 1 and Brian 2 were just example fund names to go with Funds BM01 and BM02.

I am fairly positive I can work out the VBA code to achieve what im after. The problem I have got is I am not sure how my form should be layed out.

Is there any chance you could do me a favour and knock up a quick example in my example database?

Just so I know what should be included as when im currently doing the wizard, the subform is the Fund Activity table which requires a Fund ID from Funds table so not really what im after as it should be in order such as:

• Activity (add new activity)
• Fund Table (commit funds to Fund table if launch)
• Fund Activity (requires the funds to be on the Funds table in order to use Fund ID and Activity ID)

Any help on the actual form layout then I can attempt the code behind the scenes.

Im basically looking for nothing to be committed to tables until I press a button at the bottom of the form saying "Add Record" or something.

[June7]

I prefer you to determine the preferred form structure for your needs and build then post for analysis if need more help.