sql help, I think 
Author Message
 sql help, I think

Hi. I have 4 main tables (Contacts, Animals, Volunteering
and Shifts) and one trasactional table (Events) that has
no direct relationship to the 4 tables, although it has
fields: ContactID, AnimalsID, VolunteeringID and ShiftsID.

The EventsSubform is built on the Events table. And the
Events form is built on Contacts and contains the
EventsSubform.  Basically, the Events form is 3 combo
boxes.  

One combo box is based on Volunteering and pulls in
Date/Event/Org and it's sql is:
SELECT Volunteering.VolunteeringID,
Volunteering.VolunteerDate,
Volunteering.VolunteerOrganization,
Volunteering.VolunteerEvent
FROM Volunteering;

One combo box is based on Animals and pulls in Animal
information limited to the Contact that owns the Animals:
SELECT Animals.AnimalsID, Animals.AnimalName
FROM Animals
WHERE (((Animals.ContactID)=[Forms]![Event]![ContactID]));

The last combo box is the problem.  It is based on Shifts
and I would like to limit it to the shifts that are
available given the VolunteeringID I chose in the first
combo box.  As it stands, the combo box gives ALL shifts
for every volunteering opportunity.

What can I do to limit the Shifts combo box?  Do I ned a
sql statement and where would I attach it? Please be VERY
explicit as I don't understand sql very well.  
Thanks, Stephanie



Thu, 03 Mar 2005 02:08:20 GMT  
 sql help, I think
On Sat, 14 Sep 2002 11:08:20 -0700, "Stephanie"

Quote:

>Hi. I have 4 main tables (Contacts, Animals, Volunteering
>and Shifts) and one trasactional table (Events) that has
>no direct relationship to the 4 tables, although it has
>fields: ContactID, AnimalsID, VolunteeringID and ShiftsID.

>The EventsSubform is built on the Events table. And the
>Events form is built on Contacts and contains the
>EventsSubform.  Basically, the Events form is 3 combo
>boxes.  

>One combo box is based on Volunteering and pulls in
>Date/Event/Org and it's sql is:
>SELECT Volunteering.VolunteeringID,
>Volunteering.VolunteerDate,
>Volunteering.VolunteerOrganization,
>Volunteering.VolunteerEvent
>FROM Volunteering;

>One combo box is based on Animals and pulls in Animal
>information limited to the Contact that owns the Animals:
>SELECT Animals.AnimalsID, Animals.AnimalName
>FROM Animals
>WHERE (((Animals.ContactID)=[Forms]![Event]![ContactID]));

>The last combo box is the problem.  It is based on Shifts
>and I would like to limit it to the shifts that are
>available given the VolunteeringID I chose in the first
>combo box.  As it stands, the combo box gives ALL shifts
>for every volunteering opportunity.

>What can I do to limit the Shifts combo box?  Do I ned a
>sql statement and where would I attach it? Please be VERY
>explicit as I don't understand sql very well.  
>Thanks, Stephanie

You don't mention whether VolunteerID is part of the Shifts table or
not. Presumably it is, otherwise how is the relationship defined
between volunteers and shifts?

Since you are using queries for the row source of the other combo
boxes, I presume you also have a query for the Shifts combo box, if
only to sort it. What you need is a parameter that refers to the
Volunteer combo box as a criteria. Assuming you have [cboVolunteers]
as the form control name for the Volunteers combo box, and VolunteerID
was the bound column, your parameter would look like:

 [Forms]![cboVolunteers]

Set the data type to whatever is appropriate (if it's AutoNumber, you
need Long Integer).

In the query, set the criteria (the "where" clause in the SQL
statement) of VolunteerID in the Shifts table to
[Forms]![cboVolunteers].

Next, you need to add a little line of code to the AfterUpdate event
procedure of your Volunteers combo box to "tell" the Shifts combo box
that the criteria has changed, Add [Event Procedure] to the
AfterUpdate event, click on the "build" button (which looks like
[...]) and enter one line between the procedure stub lines you will
see:

Private cboShifts_AfterUpdate()
Me.cboVolunteers.Requery
End Sub

HTH

Bob Hairgrove



Thu, 03 Mar 2005 03:28:56 GMT  
 sql help, I think
Hi, Bob. Thanks for the reply.  I do have a couple of
questions, but first, the answer to your questions.

I do have VolunteerID as part of the Shifts table.
I do have a query in the Shift combo box:
SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
Shifts.ShiftEndTime
FROM Shifts;

My Volunteers combo box is (lucky) Combo95 and I think
that is what you mean by "form control name".  The Bound
Column is "1" which is VolunteerID.  So I set my query
parameter for ShiftsID to [Forms]![Combo95] which makes
the query in the Shift combo box:
SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
Shifts.ShiftEndTime
FROM Shifts
WHERE (((Shifts.ShiftID)=[Forms]![Combo95]));

Is this correct?  Does having ShiftID = Combo95 (which is
VolunteeringID, VolunteeringDate, VolunteeringEvent, and
VolunteeringOrg) make sense when there is no shift
information in volunteering?

Then I'm confused when you say to "set the data type to
whatever is appropriate- if it's AutoNumber, you need Long
Integer".  Both ShiftsID and VolunteeringID are
AutoNumbers in their native tables (and VolunteeringID is
a Long Integer in the Shifts table).  But I don't
understand where I set the data type.  Is it in the
properties for one of the combo boxes?

I added the line of code, thanks to your great
instructions :-)

Now, the shifts combo box offers NO choices for shifts
even though there are volunteer opportunities that have
shifts assigned. Hmm.

Any suggestions?  Thanks for your help, Stephanie

Quote:
>-----Original Message-----
>On Sat, 14 Sep 2002 11:08:20 -0700, "Stephanie"

>>Hi. I have 4 main tables (Contacts, Animals,
Volunteering
>>and Shifts) and one trasactional table (Events) that has
>>no direct relationship to the 4 tables, although it has
>>fields: ContactID, AnimalsID, VolunteeringID and
ShiftsID.

>>The EventsSubform is built on the Events table. And the
>>Events form is built on Contacts and contains the
>>EventsSubform.  Basically, the Events form is 3 combo
>>boxes.  

>>One combo box is based on Volunteering and pulls in
>>Date/Event/Org and it's sql is:
>>SELECT Volunteering.VolunteeringID,
>>Volunteering.VolunteerDate,
>>Volunteering.VolunteerOrganization,
>>Volunteering.VolunteerEvent
>>FROM Volunteering;

>>One combo box is based on Animals and pulls in Animal
>>information limited to the Contact that owns the Animals:
>>SELECT Animals.AnimalsID, Animals.AnimalName
>>FROM Animals
>>WHERE (((Animals.ContactID)=[Forms]![Event]!
[ContactID]));

>>The last combo box is the problem.  It is based on
Shifts
>>and I would like to limit it to the shifts that are
>>available given the VolunteeringID I chose in the first
>>combo box.  As it stands, the combo box gives ALL shifts
>>for every volunteering opportunity.

>>What can I do to limit the Shifts combo box?  Do I ned a
>>sql statement and where would I attach it? Please be
VERY
>>explicit as I don't understand sql very well.  
>>Thanks, Stephanie

>You don't mention whether VolunteerID is part of the
Shifts table or
>not. Presumably it is, otherwise how is the relationship
defined
>between volunteers and shifts?

>Since you are using queries for the row source of the
other combo
>boxes, I presume you also have a query for the Shifts
combo box, if
>only to sort it. What you need is a parameter that refers
to the
>Volunteer combo box as a criteria. Assuming you have
[cboVolunteers]
>as the form control name for the Volunteers combo box,
and VolunteerID
>was the bound column, your parameter would look like:

> [Forms]![cboVolunteers]

>Set the data type to whatever is appropriate (if it's
AutoNumber, you
>need Long Integer).

>In the query, set the criteria (the "where" clause in the
SQL
>statement) of VolunteerID in the Shifts table to
>[Forms]![cboVolunteers].

>Next, you need to add a little line of code to the
AfterUpdate event
>procedure of your Volunteers combo box to "tell" the
Shifts combo box
>that the criteria has changed, Add [Event Procedure] to
the
>AfterUpdate event, click on the "build" button (which
looks like
>[...]) and enter one line between the procedure stub
lines you will
>see:

>Private cboShifts_AfterUpdate()
>Me.cboVolunteers.Requery
>End Sub

>HTH

>Bob Hairgrove

>.



Thu, 03 Mar 2005 08:01:35 GMT  
 sql help, I think
On Sat, 14 Sep 2002 17:01:35 -0700, "Stephanie"

Quote:

>Hi, Bob. Thanks for the reply.  I do have a couple of
>questions, but first, the answer to your questions.

>I do have VolunteerID as part of the Shifts table.
>I do have a query in the Shift combo box:
>SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
>Shifts.ShiftEndTime
>FROM Shifts;

Sorry, I made a mistake in my answer. I forgot to include the form
name before the control name in the parameter. What the SQL should
look like after you are done is this (you have to insert the real name
of your form where <form name here> is):

PARAMETERS [Forms]![<form name here>]![Combo95] LONG;
SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
Shifts.ShiftEndTime
FROM Shifts
WHERE (((Shifts.ShiftID)=[Forms]![<form name here>]![Combo95]));

Quote:
>Then I'm confused when you say to "set the data type to
>whatever is appropriate- if it's AutoNumber, you need Long
>Integer".  Both ShiftsID and VolunteeringID are
>AutoNumbers in their native tables (and VolunteeringID is
>a Long Integer in the Shifts table).  But I don't
>understand where I set the data type.  Is it in the
>properties for one of the combo boxes?

It's in the query properties. You can right-click in the grey area of
the query designer (or just choose "View->Properties->Parameters") and
they should pop up. There are two columns, one on the left for the
name and on the right for the datatype. Or you should be able to copy
& paste the SQL and then view the properties to see what it looks
like.

HTH

Bob Hairgrove



Thu, 03 Mar 2005 16:15:18 GMT  
 sql help, I think

Quote:

>Sorry, I made a mistake in my answer. I forgot to include the form
>name before the control name in the parameter. What the SQL should
>look like after you are done is this (you have to insert the real name
>of your form where <form name here> is):

>PARAMETERS [Forms]![<form name here>]![Combo95] LONG;
>SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
>Shifts.ShiftEndTime
>FROM Shifts
>WHERE (((Shifts.ShiftID)=[Forms]![<form name here>]![Combo95]));

Sorry ... I goofed again! (slowly but surely we're getting there
<g>...)

You want to limit the Shifts according to VolunteerID:

PARAMETERS [Forms]![<form name here>]![Combo95] LONG;
SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
Shifts.ShiftEndTime
FROM Shifts
WHERE (((Shifts.VolunteerID)=[Forms]![<form name here>]![Combo95]));
                ^^^^^^^^^^^

HTH

Bob Hairgrove



Thu, 03 Mar 2005 18:04:48 GMT  
 sql help, I think
Hi. OK, here's my sql statement:
SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
Shifts.ShiftEndTime
FROM Shifts
WHERE ((([Shifts].[VolunteerID])=[Forms]![Event]!
[Combo95]));

I'm still too slow to get the parameter thing ;-)
I'm using Access 2000.  When I am in query builder, my
field: [Shifts].[VolunteerID] has the
parameter: [Forms]![Event]![Combo95]
I can't seem to tack LONG on to the end of the parameter
string and when I right click on the gray part of query
builder and pull up properties, I get the Query Properties
window that doesn't have 2 columns with one for the
datatype.  It has things like values, records, filters and
no datatype.

So I guess that is why the shifts combo box is still emtpy.
Please be patient with the LONG thing and explain it to me
again.  Thanks for the help!

Quote:
>-----Original Message-----
>On Sun, 15 Sep 2002 08:15:18 GMT,


Quote:

>>Sorry, I made a mistake in my answer. I forgot to
include the form
>>name before the control name in the parameter. What the
SQL should
>>look like after you are done is this (you have to insert
the real name
>>of your form where <form name here> is):

>>PARAMETERS [Forms]![<form name here>]![Combo95] LONG;
>>SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
>>Shifts.ShiftEndTime
>>FROM Shifts
>>WHERE (((Shifts.ShiftID)=[Forms]![<form name here>]!
[Combo95]));

>Sorry ... I goofed again! (slowly but surely we're
getting there
><g>...)

>You want to limit the Shifts according to VolunteerID:

>PARAMETERS [Forms]![<form name here>]![Combo95] LONG;
>SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
>Shifts.ShiftEndTime
>FROM Shifts
>WHERE (((Shifts.VolunteerID)=[Forms]![<form name here>]!
[Combo95]));
>                ^^^^^^^^^^^

>HTH

>Bob Hairgrove

>.



Fri, 04 Mar 2005 02:12:38 GMT  
 sql help, I think
On Sun, 15 Sep 2002 11:12:38 -0700, "Stephanie"

Quote:

>Hi. OK, here's my sql statement:
>SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
>Shifts.ShiftEndTime
>FROM Shifts
>WHERE ((([Shifts].[VolunteerID])=[Forms]![Event]!
>[Combo95]));

>I'm still too slow to get the parameter thing ;-)

Just try copying and pasting this into the SQL window:

PARAMETERS [Forms]![Event]![Combo95] LONG;
SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
Shifts.ShiftEndTime
FROM Shifts
WHERE ((([Shifts].[VolunteerID])=[Forms]![Event]!
[Combo95]));

HTH

Bob Hairgrove



Sat, 05 Mar 2005 07:06:01 GMT  
 sql help, I think
Hi, Bob.  I was successful in my copy and paste :-) but
the shifts combo box is still blank for all of the
volunteer opportunities.
Any suggestions?  Thanks, Stephanie
Quote:
>-----Original Message-----
>On Sun, 15 Sep 2002 11:12:38 -0700, "Stephanie"

>>Hi. OK, here's my sql statement:
>>SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
>>Shifts.ShiftEndTime
>>FROM Shifts
>>WHERE ((([Shifts].[VolunteerID])=[Forms]![Event]!
>>[Combo95]));

>>I'm still too slow to get the parameter thing ;-)

>Just try copying and pasting this into the SQL window:

>PARAMETERS [Forms]![Event]![Combo95] LONG;
>SELECT Shifts.ShiftID, Shifts.ShiftStartTime,
>Shifts.ShiftEndTime
>FROM Shifts
>WHERE ((([Shifts].[VolunteerID])=[Forms]![Event]!
>[Combo95]));

>HTH

>Bob Hairgrove

>.



Sat, 05 Mar 2005 08:23:06 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Please help - SQL ? (I think)

2. Thinking about WinHelp Tools? Think $$$

3. syntax error in my SQL... I think

4. ASP Returning a DIB Image (I think) from SQL , Netscape doesn't display it

5. a simple SQL question, i think.

6. SQL Trouble-I think

7. SQL Speed Question (Really Interesting me thinks)

8. ASP Returning a DIB Image (I think) from SQL , Netscape doesn't display it

9. Simple SQL syntax question (I think)

10. Simple SQL question, I think

11. I think I need some help on the help compiler

12. I think you can help me !!!!!!!!!!!!

 

 
Powered by phpBB® Forum Software