how to write this WHERE condition??

[RON]

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.

 

[Wayne-I-M]

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

 

[RON]

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]

Provided that the BoundColumn of the ComboBox corresponds to the *numeric*
StudenID and StudentID is in the RecordSource of the Form
frmqryCouseSections, this should work:

DoCmd.OpenForm "FRMqryCourseSections", , , _
"[StudentID = & Me.cmbStudent

(assuming that the code is executed in the context of the current Form
frmCourses).

 

[SteveS]

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.

 

[SteveS]

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.

 

[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]

What is the correct name of your ComboBox?

Earlier in the tread, you used "cmbstudent.selected" (note the DOT) which
indicated the name of the ComboBox is "cmbstudent" ("Selected" Is a Property
of ComboBox). In your last post, you used "cmbStudentselected" which is a
different name for the ComboBox.

Which ListBox are you referring to?

--
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.

 

[RON]

OK I am getting closer buty obviously missing something. Now I am using
this code

For Each i In lstcourses.ItemsSelected
strwhere = lstcourses.Column(0, i)
MsgBox "course selected is " & strwhere
Next i

'open the FRMqryCourseSections form for the current student
' and selected course
DoCmd.OpenForm "FRMqryCourseSections", , , "courseid='strwhere'"
Forms!frmqrycoursesections.txtstudentid = cmbStudentselected

My message box always pops up and says Course Selected is ENG100 or
whatever the actual course selected is. But it does not get inserted into
my other form tyhat is popping up.....However when I hard coded it in like:

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

It would open up the next form for ENG100, can someone tell me what I can
do to make this work?

What is the correct name of your ComboBox?

Earlier in the tread, you used "cmbstudent.selected" (note the DOT) which
indicated the name of the ComboBox is "cmbstudent" ("Selected" Is a
Property of ComboBox). In your last post, you used "cmbStudentselected"
which is a different name for the ComboBox.

Which ListBox are you referring to?

--
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.

 

[SteveS]

Before it sounded like you wanted to select a student ID AND a course ID.

Try this (watch for line wrap)


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

Dim stDocName As String
Dim stCourseList As String
Dim stLinkCriteria As String
Dim stCourse As Variant

'first time thru loop?
Dim FirstTime As Boolean


stDocName = "FRMqryCourseSections"
stCourseList = ""

'get Courses selected in lstboxcourses
' assumes the bound field of the list box
' is a string
FirstTime = True
For Each stCourse In lstboxcourses.ItemsSelected
If FirstTime Then
stCourseList = "In('" & lstboxcourses.ItemData(stCourse) & "'"
FirstTime = False
Else
stCourseList = stCourseList & ",'" &
lstboxcourses.ItemData(stCourse) & "'"
End If
Next stCourse

'make sure len of stCourseList > 0
If Len(Trim(Nz(stCourseList , ""))) > 0 Then
stCourseList = stCourseList & ")"

'create criteria string
stLinkCriteria = "[COURSEID] " & stCourseList
End If

'-------------------------
' for debugging - delete after code runs without errors
' MsgBox stLinkCriteria
'-------------------------

'open report in preview mode
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_btnDoIt_Click:
Exit Sub

Err_btnDoIt_Click:
MsgBox Err.Description
Resume Exit_btnDoIt_Click

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


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

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.