How do I write this WHERE statements?

[RON]

thanks for all the help everyone,
OK here is what I have done, I have gotten the studentID to be passed to the
other form using this code:

Forms!frmqrycoursesections.txtstudentid = cmbStudentselected

so this gets the studentID that is selected in the Student combo box into a
text box on the next form that pops up.

What I am still having trouble is taking what was selected in the listbox
and filtering the next form for that also...and multiple things can be
selected in the listbox.

I hard coded something in for this and it worked, but I want the values to
come from what users select not me hard coding.....here is what I did

DoCmd.OpenForm "FRMqryCourseSections", , , "courseid='eng100'"

that code does exactly what I want it to do....only that it is filtered for
ENG100...and not what was selected inthe listbox. Can someone show me how
to write some code that would get the values that are selected and use
those?
I am thinking tere will need to be a for loop but I dont know where to go.
I also know that the courseID is subscript 0 even though I am hiding that
from the user.

thanks for any help.


ALSO, here is the sql of the form that will pop up FRMqryCourseSectrions,
it is based on this query

SELECT COURSESECTION.SCHEDULENO, COURSESECTION.COURSEID,
COURSESECTION.SECTION, COURSESECTION.COURSESECTIME,
COURSESECTION.COURSESECDAY, LOCATION.LOCATIONBUILDING, LOCATION.LOCATIONROOM
FROM COURSESECTION INNER JOIN LOCATION ON
COURSESECTION.LOCATIONID=LOCATION.LOCATIONID;

thanks

I'm so Dee, Dee Dee.....

I saw the post from Van after mine..... what was I thinking??

This is easier to type and shorter. In the code for the button replace the
create criteria section with:

'--------
'create the criteria
'assumes studentID and CourseID are numeric
stLinkCriteria = "[studentid]= " & Me.cmbstudentSelected

stLinkCriteria = stLinkCriteria & " And "

stLinkCriteria = stLinkCriteria & " [CourseID] = " & Me.lstboxcourses
'-------------------


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Ron,

I called the bound field of the list box [CourseID]. Change "[CourseID]"
(2
places) if it is a different name.

I created an button and named it "btnDoIt"

Here is the code for the button (watch for line wrap):

'-------beg code---------
Private Sub btnDoIt_Click()
On Error GoTo Err_btnDoIt_Click

Dim stDocName As String
Dim stLinkCriteria As String

'check that both student and course selected
'otherwise, causes an error
If IsNull([studentID]) Or IsNull([CourseID]) Then
MsgBox "ERROR! Select a student and a course..."
Exit Sub
End If

'create the criteria
'assumes studentID and CourseID are numeric
stLinkCriteria = "[studentid]= " &
Forms![frmcourses]!cmbstudentSelected

stLinkCriteria = stLinkCriteria & " And "

stLinkCriteria = stLinkCriteria & " [CourseID] = " &
Forms![frmcourses]!lstboxcourses

'personal preference: I would change the name
'of the form to "frmCourseSelections"
stDocName = "FRMqryCourseSections"

'open the form
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnDoIt_Click:
Exit Sub

Err_btnDoIt_Click:
MsgBox Err.Description
Resume Exit_btnDoIt_Click

End Sub
'-------end code---------


If you have problems, post the SQL of the query for the form
"FRMqryCourseSections"

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

ok this did not seem to work for me, let me give another example of
what I
want to do.

I have a form frmcourses. On this form there is a combobox with last
names
cmbstudentselected
there is also a list box that lists course names and ID's called
lstboxcourses

A user should open this form select a name from the combo box, therefor
selecting a student, then select one or multiple courses from the list
box.
When a command button is pressed a new form pops up that is based on a
qry,
FRMqryCourseSelected. In this form that pops up it will be filtered
for the
student that was selected from the combo box and the courses that will
show
up on the form are also filtered from whatever the user selected in the
listbox.

So for example....
I chose the name Adams from the combobox, adams is associated with a ID
of
111222 the ID is hidden though and the user only sees Adams.

Then from the list box they choose ENGLISH I, there is a course ID
associated with that also but it is being hid from the user, the ID is:
ENG100

When the user clicks a button another form shows up, in a text box the
name
Adams or 111222 is displayed and all sections of ENG100 are displayed.

how would this be done?

DoCmd.OpenForm "FRMqryCourseSections", acNormal, "", "[Forms]![
frmCourses]![studentid]=[Forms]![frmcourses]![cmbstudentselected]", ,
acNormal

I have changed your field name
cmbstudent.selected
to
cmbstudentselected ( no . )
You should also do this

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

Here is what I need to do, I have a combo box that will open another
form
filtered.

The form will be filterered by whatever is selected in lstboxCourses
and
cmboxStudent
so someone will select Adams from the drop down combo box and
ENGLISH
from
the lisbox of courses.
The form they will be selecting this on is called frmCourses

the form I want to open is frmqryCourseSections

here is what I have and it it not working, can someone help me out?

DoCmd.OpenForm "FRMqryCourseSections", , ,
"studentid=forms![frmcourses]!cmbstudent.selected"

this does not work for me....can someone help me out? In this
example I
tried it only with cmbstudent, did not try it with the other list
box
yet.

 

[Van T. Dinh]

Please stick to ONE thread unless you don't get any response on the existing
thread for ,says, 24-48 hours.

Creating another thread simply means that the potential respondents will
need to retrace the steps.

See original thread ...

--
HTH
Van T. Dinh
MVP (Access)

thanks for all the help everyone,
OK here is what I have done, I have gotten the studentID to be passed to
the
other form using this code:

Forms!frmqrycoursesections.txtstudentid = cmbStudentselected

so this gets the studentID that is selected in the Student combo box into
a
text box on the next form that pops up.

What I am still having trouble is taking what was selected in the listbox
and filtering the next form for that also...and multiple things can be
selected in the listbox.

I hard coded something in for this and it worked, but I want the values to
come from what users select not me hard coding.....here is what I did

DoCmd.OpenForm "FRMqryCourseSections", , , "courseid='eng100'"

that code does exactly what I want it to do....only that it is filtered
for
ENG100...and not what was selected inthe listbox. Can someone show me how
to write some code that would get the values that are selected and use
those?
I am thinking tere will need to be a for loop but I dont know where to go.
I also know that the courseID is subscript 0 even though I am hiding that
from the user.

thanks for any help.


ALSO, here is the sql of the form that will pop up FRMqryCourseSectrions,
it is based on this query

SELECT COURSESECTION.SCHEDULENO, COURSESECTION.COURSEID,
COURSESECTION.SECTION, COURSESECTION.COURSESECTIME,
COURSESECTION.COURSESECDAY, LOCATION.LOCATIONBUILDING,
LOCATION.LOCATIONROOM
FROM COURSESECTION INNER JOIN LOCATION ON
COURSESECTION.LOCATIONID=LOCATION.LOCATIONID;

thanks

I'm so Dee, Dee Dee.....

I saw the post from Van after mine..... what was I thinking??

This is easier to type and shorter. In the code for the button replace
the
create criteria section with:

'--------
'create the criteria
'assumes studentID and CourseID are numeric
stLinkCriteria = "[studentid]= " & Me.cmbstudentSelected

stLinkCriteria = stLinkCriteria & " And "

stLinkCriteria = stLinkCriteria & " [CourseID] = " & Me.lstboxcourses
'-------------------


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Ron,

I called the bound field of the list box [CourseID]. Change "[CourseID]"
(2
places) if it is a different name.

I created an button and named it "btnDoIt"

Here is the code for the button (watch for line wrap):

'-------beg code---------
Private Sub btnDoIt_Click()
On Error GoTo Err_btnDoIt_Click

Dim stDocName As String
Dim stLinkCriteria As String

'check that both student and course selected
'otherwise, causes an error
If IsNull([studentID]) Or IsNull([CourseID]) Then
MsgBox "ERROR! Select a student and a course..."
Exit Sub
End If

'create the criteria
'assumes studentID and CourseID are numeric
stLinkCriteria = "[studentid]= " &
Forms![frmcourses]!cmbstudentSelected

stLinkCriteria = stLinkCriteria & " And "

stLinkCriteria = stLinkCriteria & " [CourseID] = " &
Forms![frmcourses]!lstboxcourses

'personal preference: I would change the name
'of the form to "frmCourseSelections"
stDocName = "FRMqryCourseSections"

'open the form
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnDoIt_Click:
Exit Sub

Err_btnDoIt_Click:
MsgBox Err.Description
Resume Exit_btnDoIt_Click

End Sub
'-------end code---------


If you have problems, post the SQL of the query for the form
"FRMqryCourseSections"

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

ok this did not seem to work for me, let me give another example of
what I
want to do.

I have a form frmcourses. On this form there is a combobox with last
names
cmbstudentselected
there is also a list box that lists course names and ID's called
lstboxcourses

A user should open this form select a name from the combo box,
therefor
selecting a student, then select one or multiple courses from the list
box.
When a command button is pressed a new form pops up that is based on a
qry,
FRMqryCourseSelected. In this form that pops up it will be filtered
for the
student that was selected from the combo box and the courses that will
show
up on the form are also filtered from whatever the user selected in
the
listbox.

So for example....
I chose the name Adams from the combobox, adams is associated with a
ID
of
111222 the ID is hidden though and the user only sees Adams.

Then from the list box they choose ENGLISH I, there is a course ID
associated with that also but it is being hid from the user, the ID
is:
ENG100

When the user clicks a button another form shows up, in a text box the
name
Adams or 111222 is displayed and all sections of ENG100 are displayed.

how would this be done?

DoCmd.OpenForm "FRMqryCourseSections", acNormal, "", "[Forms]![
frmCourses]![studentid]=[Forms]![frmcourses]![cmbstudentselected]",
,
acNormal

I have changed your field name
cmbstudent.selected
to
cmbstudentselected ( no . )
You should also do this

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

Here is what I need to do, I have a combo box that will open
another
form
filtered.

The form will be filterered by whatever is selected in
lstboxCourses
and
cmboxStudent
so someone will select Adams from the drop down combo box and
ENGLISH
from
the lisbox of courses.
The form they will be selecting this on is called frmCourses

the form I want to open is frmqryCourseSections

here is what I have and it it not working, can someone help me
out?

DoCmd.OpenForm "FRMqryCourseSections", , ,
"studentid=forms![frmcourses]!cmbstudent.selected"

this does not work for me....can someone help me out? In this
example I
tried it only with cmbstudent, did not try it with the other list
box
yet.