How to create this WHERE condition

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

 

[Albert D. Kallal]

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.

The above makes no sense at all. What do you mean by the next form that pops
up? The command of:

Forms!frmqrycoursesections.txtstudentid = cmbStudentselected

That simply sets the value of a text box on a form. It does not filter the
form. It does not cause the form to load (so, why speak of "next form" that
pops up?????

You will have to OPEN the form frmqrycoursesections BEFORE you attempted to
set the value of txtstudentid.

(so, mention of "next form" makes me think I am missing something here)

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'"

ok, so now we are all of a sudden talking about opening this form? Why then
all that information about setting a value in the form BEFORE you even have
any code posted to open the form? (sating seems wrong, or out of order
here? Perhaps by accident you decided to leave out the part about opening
the form AFTER you posted some code about modifying a text box prior to
this information?)

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?

Are you saying that FRMqryCourseSectaions will HAVE MORE THEN ONE record? If
so, then which particular record are you palling to modify with:

Forms!frmqrycoursesections.txtstudentid = cmbStudentselected

(or, perhaps is txtStudentid a un-bound text box?).

I guess I did not see the previous question, but seems to me:


1) I have a form A, and want to make some selections from a listbox
2) I want to open a form B based on the selections in form A listbox
3) c) after I open the form, then I need to go:
Forms!frmqrycoursesections.txtstudentid = cmbStudentselected

4) A report from this form B is launched????

I guess it is step 3 that is confusing here (unless textStudentID is
un-bound).

 

[RON]

sorry for the confusion.

Yes form A has a list box that I then want to filter form B for. And yes
Form B has an un bound text box that I want to pass the studentID from Form
A's combobox to it. Yes I did list it out of order, I would need to open
the form first filtering it for the Listbox selections, and then I will
also pass the combobox selection from Form A to the text box on form B

My question is this, how do I loop through the listbox on form A and get
the selected entries in the list box?

the code here: DoCmd.OpenForm "FRMqryCourseSections", , ,
"courseid='eng100'"
was just showing what I want to be able to pass to the next form....only
instead of eng100 it will be a value or values of whatever the user swelects
in the listbox.

 

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

 

[RON]

OK got it....

DoCmd.OpenForm "FRMqryCourseSections", , , "[COURSEID] = '" & strwhere & "'"

 

[Albert D. Kallal]

ok...

For Each i In lstcourses.ItemsSelected
strwhere = lstcourses.Column(0, i)
Next i

the problem is that we need to allow for *more* then one course selected

So, lets go:
strWhere = ""
For Each i In lstcourses.ItemsSelected
if strWhere <> "" then
strWhere = strwhere & ","
end if
strwhere = chr(34) & lstcourses.Column(0, i) & chr(34)
Next i

' note that blank selections in listbox = "all"..

if strWhere <> "" then
strwhere = "courseid in (" & strWhere & ")"
end if

DoCmd.OpenForm "FRMqryCourseSections", , , strWhere
Forms!frmqrycoursesections.txtstudentid = cmbStudentselected

Note how we have the command to set that txtStudentID AFTER we have opened
the form. (you can't place that command BEFORE the openform).

Note that the strWhere is a VALID sql "where" clause without the word where.
This means that:

select * from someTable where
courseid = eng100 eng102 eng105

The above is not good. Nor is

courseid = "eng100" "eng102" "eng105"

However, we could use:

courseid = "eng100" or courseid = "eng102" or courseid = "eng105"

or, better yet:
select * from someTable where
courseid in ("eng100", "eng102", "eng105")

So, you can see that the where clause has been modifed to pass a "list" of
values using the sql "in" (but, again without the word "where")

 

[RON]

thanks, I tried this code but on the form that pops up only the item that is
selected last is displayed on the nexy form

for example if I select just ENG100 in the list box, the next form opens
with ENG100

However if I select ENG100 and ENG150 only ENG150 is displayed on the next
form.

 

[Albert D. Kallal]

thanks, I tried this code but on the form that pops up only the item that
is selected last is displayed on the nexy form

for example if I select just ENG100 in the list box, the next form opens
with ENG100

However if I select ENG100 and ENG150 only ENG150 is displayed on the
next form.
sorry...type-o


strwhere = chr(34) & lstcourses.Column(0, i) & chr(34)

That needs to be

strWhere = strWhere & chr(34) & lstcourses.Column(0, i) & chr(34)