Forms/subforms (1 Viewer)

[cabusmichael]

Hello,

First off, thanks, everyone, for the responses I have had so far--they have been wonderful...

I am having a strange form-design problem.

I have a design in which grants fund specific projects. I have a relationship like this:

[contracts/grants] (N) ---- (1) [projects]

So, each contract/grant funds 1 project, and a project can be funded by more than 1 grant. I look at the relationships view, and the relationship is correctly mapping as a 1:N relationship.

I'm imagining the user going into the grant form, adding a new grant, and then adding the project name. Currently, I have the project name as a unique identifier, on the assumption that no two projects will have the same name.

When I create a form based on this table, a "project" field is present, but, if one tries to enter a new project name here, there is an error message "You cannot add or change a record because a related record is required in table 'Project'". I don't understand this--if the project field is present, why can't it write a new project name to the project table?

I have thought of one solution--adding a project subform that can be used for adding new project name (say "New Jersey Pine Barrens restoration project"); then, the user could enter in the project name in the in the Contract/Grants form. It works, but it is not very elegant..

Is there a better way to configure this?

Thanks for reading, and any helpful response--very much appreciated!

Michael

Thanks for any help,

Michael

 

[wiklendt]

Currently, I have the project name as a unique identifier, on the assumption that no two projects will have the same name.

hi michael.

i would use ProjectID as the unique identifier. if you have your tables set up correctly, they won't allow duplicates BUT i would assume that project names COULD POSSIBLY repeat, as it's just a name - for example, couldn't "Waste Management Project" possibly appear in, say, 2008 and 2010 (or in different local council)?

often project names that are funded have a project number of some description... this should be another field in your projects table. but in any event, i would use an arbitraty access-defined autonumber PK field to assist in ease of relationships between other tables in your DB.

once you have the projectID textbox on the form change the textbox that stores the projectID into a combobox.

make the source of that combo a query based off your projects table. then, in the format tab of the properties, set the first column (which ought to be the primary key "ProjectID") to be zero width, the next to be 3cm (or whatever units/length you want) - in effect, the field is still storing the ID but is displaying the name.

if one tries to enter a new project name here, there is an error message "You cannot add or change a record because a related record is required in table 'Project'". I don't understand this--if the project field is present, why can't it write a new project name to the project table?

if you set up your form as i have just explained above, then the combobox will prevent the user accidentally typing the wrong project name. because you have referential integrity between the two tables, you cannot add a project that does not exist! you need to venture in to VBA to handle this.

a combobox has an event called "not in list", which is where you would do this. typically, a developer would use this event to invoke the projects form where the user could then enter a new project, then when that form closes, requeries the projectID combobox in the grants form.

I have thought of one solution--adding a project subform that can be used for adding new project name (say "New Jersey Pine Barrens restoration project"); then, the user could enter in the project name in the in the Contract/Grants form. It works, but it is not very elegant..

no, this would not be a good way to do it because it sounds as though one grant only funds one project, so there is no need to have a subform - these are only really used for N:N type relationships (in my experience)

HTH

 

[cabusmichael]

Hello,
Just a quick note--what you suggested worked very well. Thanks for taking the time to respond--I very much appreciate it.

I am thinking about exploring VBA for Access...I have done some PL/SQL, so it may not be an entirely new field..

Anyway, many thanks!

Michael

 

[cabusmichael]

Hello,
I thanked you twice on accident, not realizing my first post actually made it on...

So, I edited this to explain why there were two--

Anyway, I will escape...it is Friday night, maybe I need a bit of fun, and less computer

Michael

 

[gemma-the-husky]

generally it ought to be the other way round

if each project has multiple grants, then you ought to be selecting a project - displaying the grants, and entering a new one for it.

doing it the other way is possible - in which case you enter grants, but then you need to allocate the unattached grants to a project at some point.

it depends how the funding works - i would have thought that normally you apply for funding for a project - but maybe you get grants and then decide how to use them

one last thing - does every grant ALWAYS fund 1 project only. this seems limiting, and if this isnt the case, you need a different model.

 

[wiklendt]

generally it ought to be the other way round

if each project has multiple grants, then you ought to be selecting a project - displaying the grants, and entering a new one for it.

i should have mentioned something like that - in my case, where it possible to enter data either here or there, i will make forms to allow it. in this case, i'd place a datasheet view subform into the projects form and set it up so that i could select multiple grants into it. (in addition to the combo box in the grant form)

one last thing - does every grant ALWAYS fund 1 project only. this seems limiting, and if this isnt the case, you need a different model.

i work in a lab, and this is the way it generally works. one grant funds one project. but you CAN have on project being funded by many grants.

from what i understand that's the difference between "budget" (e.g., environmental budget) and "grant" (individual applications for money from the budget).

in australia, we have lots of 'grants' available for individuals. "first home buyer's grant" and various cashback schemes (like if you buy a super water efficient washing machine, the gov't will give you a portion of what you paid back to you as a reward for not buying a machine that uses more water than necessary).

each one grant, in those cases, is distributed to just one person. so that makes sense to me.

sorry if i'm confusing anyone!

 

[cabusmichael]

Thanks, Gemma--
This got me to think of various ways data could be input, for example, a set of data on grantees needing grant money, but their project may not have a potential funding source at present. That flexibility needs to be built in...

The relationships are a bit tricky--the person who will be using the database will be meeting with me this week to go over the first version, so hopefully I can find out if anything modeled in the database does not match her work environment. She also wants to use it as a management tool (for example, to track who was the last person who had a certain grant form, to see if they are working on it, or if it is buried on the desk). So, I wonder about logins--much better than requiring initials after updating the database (as I write that, I realize how ridiculous that sounds).

Anyway, I'm rambling--sorry!

Thanks for the reply!

MAC