Subform issue

[larochy]

Hi,

Not sure if this is a table problem or a subform problem. I have two tables:

tblInterview
InterviewID
Date
Volunteer1
Volunteer2

tblVolunteers
VolunteerID
Full Name
Phone Number

I'm trying to show a form based on the Volunteers table that shows the
Volunteer information at the top and any related Interviews they've been
involved with. They can be in either the Volunteer1 or Volunteer2 field.

When I try to join the subform on eith the Volunteer1 or Volunteer2 field
matching up to the Volunteer ID, it doesn't work. I'm assuming it wants to
see the Volunteer ID show up in both fields.

What am I doing wrong here? Table design or form issue?

Thanks

 

[jubiiab via AccessMonster.com]

If tblInterview is your subform you need to have VolunteerID in the table. Or
if tbl Volunteers is your subform you need to add InterviewID.

 

[larochy]

Thanks

The Volunteer1 and Volunteer2 fields are linked to the Volunteer ID in the
Volunteer table. If I get rid of the Volunteer1 and Volunteer2 fields and
replace it with the VolunteerID, how do I show that there were two volunteers
for the one interview?

 

[jubiiab via AccessMonster.com]

Look I am not sure I got your question right but I would do it in a different
way if it was me.

First I would create two tables:

tblInterview
InterviewID
Date

tblVolunteers
VolunteerID
FullName
PhoneNumber
InterviewID


Now click on Tools in the menubar and select Relationships and create
relations with the InterviewID field.

Now in your tblInterview you have this data:

InterviewID---------Date
-----1-----------------30-08-2009
-----2-----------------25-07-2009


In your tbl_Volunteers you have this data:

VolunteerID--------Fulname---------PhoneNumber--------InterviewID
------1-------------------Peter---------------33332233------------------1
-----2-------------------Tom ---------------55335533------------------1


Now create a form by using Wizard and select tblInterview

Make the form bigger and create a subform based on tblVolunteers

Now you will be asked to include fields and make the selection as this:
First select: InterviewID, VolunteerID, FullName, PhoneNumber

Next you have to mark “Chose from list†and select “show tbl_volunteers for
each……â€

Now give the subform a name and finish

In the subform properties you have to make sure it will show it as Datasheet
view

Now open the form and you will see that InterviewID 1 have two Volunteers.

By doing it in this way you can have more than two volunteers on one
InverviewID.


Sorry mate but my English is not so good. I hope you understand what I wrote.
Good luck!

Thanks

The Volunteer1 and Volunteer2 fields are linked to the Volunteer ID in the
Volunteer table. If I get rid of the Volunteer1 and Volunteer2 fields and
replace it with the VolunteerID, how do I show that there were two volunteers
for the one interview?

If tblInterview is your subform you need to have VolunteerID in the table. Or
if tbl Volunteers is your subform you need to add InterviewID.

[quoted text clipped - 37 lines]

 

[jubiiab via AccessMonster.com]

Hey man I jut realized that I did a mistake. In that example above you can’t
use the same VolunteerID in another InterviewID.

That’s way you need to create 3 tables and 1 query.

I have also made a form with a subform that is using continues view because
than you can have a combobox to select VolunteerID. If you give me your email
I send you the example I have made. It’s much easier.

Look I am not sure I got your question right but I would do it in a different
way if it was me.

First I would create two tables:

tblInterview
InterviewID
Date

tblVolunteers
VolunteerID
FullName
PhoneNumber
InterviewID

Now click on Tools in the menubar and select Relationships and create
relations with the InterviewID field.

Now in your tblInterview you have this data:

InterviewID---------Date
-----1-----------------30-08-2009
-----2-----------------25-07-2009

In your tbl_Volunteers you have this data:

VolunteerID--------Fulname---------PhoneNumber--------InterviewID
------1-------------------Peter---------------33332233------------------1
-----2-------------------Tom ---------------55335533------------------1

Now create a form by using Wizard and select tblInterview

Make the form bigger and create a subform based on tblVolunteers

Now you will be asked to include fields and make the selection as this:
First select: InterviewID, VolunteerID, FullName, PhoneNumber

Next you have to mark “Chose from list†and select “show tbl_volunteers for
each……â€

Now give the subform a name and finish

In the subform properties you have to make sure it will show it as Datasheet
view

Now open the form and you will see that InterviewID 1 have two Volunteers.

By doing it in this way you can have more than two volunteers on one
InverviewID.

Sorry mate but my English is not so good. I hope you understand what I wrote.
Good luck!

Thanks

[quoted text clipped - 8 lines]