linking forms (and automatically creating new record)

[fonzair23]

Please forgive me if I don't explain everything properly, I am relatively new to Access.

I have two tables with the following information:

VOLUNTEER
PK: volunteer_ID (autonumber)
name, address, school, etc

SCHOLARSHIP
PK: scholarship_ID (autonumber)
description (text)
amount (currency)
FK: volunteer_ID (number)

One scholarship can be awarded to only one volunteer.
I have a form to enter all the volunteer information. At the bottom of the form, I would like to place a button to open a new form to award a scholarship to this volunteer. I believe I need to link the forms in the following manner:

VOLUNTEER.volunteer_ID = SCHOLARSHIP.volunteer_ID

The problem is this: The forms are not linked properly because in order to assign a volunteer to a scholarship, the scholarship must already exist. So when I click the button to open the scholarship form, the scholarship form is empty and the volunteer_ID defaults to "0".

I think this could be fixed by somehow making my button create a new scholarship_ID in the scholarship form and THEN linking the volunteer_ID fields.

Does this seem like a solution? If so, how would I implement it? I have a screenshot to help. Thank you in advance for your help.

 

[stumpers]

Same problem

Hey sorry to get your hopes on a solution here but I have a similar problem to yourself and was wondering if you actually found a soultion to it. i noticed one of your other posts on the cascading combo boxes which help me a lot. Seems our learning curve is hitting the same barriers.

It seems a ridiculous notion to have a relation ship with the table and have it not directly link with a form with out some VB code (which i am not very familiar with)

 

[fonzair23]

Stumpers, unfortunately i did not find a very good solution to my problem. Instead, I used a rather cheesy (and in my opinion, unsatisfactory) workaround. When the button was pressed to open the related form, the forms did not link properly, and the field in the second form that was supposed to link just defaulted to "0". On that form, I simply created a text box stating that the user had to refer to the previous form and manually enter the corresponding ID number. I never got them to link automatically. Fortunately, the project was not such that it was absolutely vital that they link.

I asked an old IT professor of mine as to what the problem might be. He concurred that the forms could not link because in order for a link to be valid, a new Scholarship record must already be present. The solution he offered was one that would require some knowledge of VB. He said that when the button on the first form (the one to open the second form and link the two of them) was pressed, instead of standard linking, you would need to do some coding. The coding would proceed in the following manner:

1. Save the current record (in my case, save the volunteer information)
2. Create a new record in table number two (in my case, create new scholarship)
3. Retrieve PK from form #1
4. Place retrieved PK from form #1 as the FK in form #2
5. Open this form #2, keeping in mind steps 1-4.

This was way beyond any knowledge I had (or have) and opted for the easy way out since it was not critical. Hopefully you were able to follow this reply, and good luck. Let me know if you have any success.

 

[fonzair23]

Thanks for the reply, and I know what you are saying. Initially, I had things set up exactly how you described. When I reported back to the scholarship foundation, however, they filled me in as to why they wanted it set up the way I described in my first post. I guess I should have made the rationale for having it this way more clear.

The scholarship foundation sponsors an event to raise money to give scholarships to high school students and those just entering college. There are many volunteers of all ages, most of whom do not apply or receive scholarships.

In order for a scholarship applicant to be eligible for a scholarship, he/she must put in some time as a volunteer at the event, thus the recipient of a scholarship must be a volunteer (that's why volunteer_ID is the FK for a scholarship record). When awarding scholarships, there is not a set number of pre-existing scholarships. If an applicant is deserving, the foundation wishes to set up a new scholarship specific to that person. That's why I wanted a subform from my volunteer entry form that links voluteer_ID from both VOLUNTEER and SCHOLARSHIP--so it would be easy to enter applicant/volunteer info as well as scholarship details at the same time.

Hopefully this explains the situation a little better than I had done before. Sorry for the confusion, and thank you for your input.

 

[fonzair23]

With your current design, you cannot see the scholorships already assigned to an individual. What happens if you award the same scholorship twice? You won't even notice.

I see your point, and it's a good one. I guess I'm not sure of a good solution, however. The foundation wanted to be able to assign a scholarship as easily as possible, and directly from the volunteer form. Maybe that is just not possible (and even if it is possible, it may just be illogical).

I do have a separate form for entering scholarships where a scholarship is created and then it can be assigned to a volunteer by a drop down that lists all those eligible. The reason for the two different ways to assign scholarships is in the types of scholarships they give out. Some scholarships exist and are static every year. They are always awarded. Others are created specifically for individuals to fit a specific need (that's why I wanted to be able to assign scholarships directly from the volunteer form). I guess the logic should be rethought a bit, though. Thanks again for your feedback.