OpenForm - WHERE condition

[Guest]

I am having trouble opening a form based on the value from a cboListBox on
another form.

frmClasses contains detailed information about students and their classes.
The form navigates from record to record as usual.
a cboListBox (cboClassByName) is provided to search for a student name.
cboClassByName contains names of students enrolled in all classes.
many students are enrolled in a number of classes.
before displaying class enrollment detail, the user should see multiple
enrollments for a student, (frmClassSearchList) and be able to select which
enrollment to veiw detail for.
frmClassSearchList is unbound with a lstListBox, the RowSource for which is
qryClassSearchList.
Name is a field in lstListBox.

I have tried many variety of qutoes and brackets around the WHERE condition.

' User inputs or selects a Name to search for.
Function cboClassByName_AfterUpdate()

DoCmd.OpenForm "frmClassSearchList", , , [Name] =
Forms![frmClass]![cboClassByName]

End Function

I also tried;

' User inputs or selects a Name to search for.
Function cboClassByName_AfterUpdate()

TempStr=cboCLassByName
DoCmd.OpenForm "frmClassSearchList", , , [Name] = TempStr

End Function

I have tested TempStr and it properly stores the value of cboCLassByName

Any ideas?

 

[Guest]

I don't know what you tried, but try this

' If class cbo is string
DoCmd.OpenForm "frmClassSearchList", , , "[Name] = '" &
Forms![frmClass]![cboClassByName] & "'"

' If class cbo is number
DoCmd.OpenForm "frmClassSearchList", , , "[Name] = " &
Forms![frmClass]![cboClassByName]

Also, name is a reserved name in Access, so you should consider changing it
to something else, it will be OK as long that you keep it in square brackets.

 

[Marshall Barton]

I am having trouble opening a form based on the value from a cboListBox on
another form.

frmClasses contains detailed information about students and their classes.
The form navigates from record to record as usual.
a cboListBox (cboClassByName) is provided to search for a student name.
cboClassByName contains names of students enrolled in all classes.
many students are enrolled in a number of classes.
before displaying class enrollment detail, the user should see multiple
enrollments for a student, (frmClassSearchList) and be able to select which
enrollment to veiw detail for.
frmClassSearchList is unbound with a lstListBox, the RowSource for which is
qryClassSearchList.
Name is a field in lstListBox.

I have tried many variety of qutoes and brackets around the WHERE condition. [snip]
Function cboClassByName_AfterUpdate()

TempStr=cboCLassByName
DoCmd.OpenForm "frmClassSearchList", , , [Name] = TempStr

End Function

I have tested TempStr and it properly stores the value of cboCLassByName

This should be all you need:

Function cboClassByName_AfterUpdate()
DoCmd.OpenForm "frmClassSearchList", , , _
"[Name] = " & cboCLassByName
End Function

If the Name field is a Text type field, then it would have
to be:
"[Name] = """ & cboCLassByName & """"

You are aware the Name is a reserved word, aren't you?
You really should change it to something like StudentName.

 

[Guest]

Thanks for both replies above.
I will try the suggestions.
My post comprised psuedo-code and Name is not used in my code. I was aware
of it's reservation.
Thanks again, let you know how I go.

I am having trouble opening a form based on the value from a cboListBox on
another form.

frmClasses contains detailed information about students and their classes.
The form navigates from record to record as usual.
a cboListBox (cboClassByName) is provided to search for a student name.
cboClassByName contains names of students enrolled in all classes.
many students are enrolled in a number of classes.
before displaying class enrollment detail, the user should see multiple
enrollments for a student, (frmClassSearchList) and be able to select which
enrollment to veiw detail for.
frmClassSearchList is unbound with a lstListBox, the RowSource for which is
qryClassSearchList.
Name is a field in lstListBox.

I have tried many variety of qutoes and brackets around the WHERE condition. [snip]
Function cboClassByName_AfterUpdate()

TempStr=cboCLassByName
DoCmd.OpenForm "frmClassSearchList", , , [Name] = TempStr

End Function

I have tested TempStr and it properly stores the value of cboCLassByName

This should be all you need:

Function cboClassByName_AfterUpdate()
DoCmd.OpenForm "frmClassSearchList", , , _
"[Name] = " & cboCLassByName
End Function

If the Name field is a Text type field, then it would have
to be:
"[Name] = """ & cboCLassByName & """"

You are aware the Name is a reserved word, aren't you?
You really should change it to something like StudentName.

 

[Guest]

Sorry Guys,

Both versions brought up all records in the lstListBox.

Any ideas?

Glenn52

 

[tina]

i'm guessing that the bound column of cboClassByName is *not* a name, since
it would be unusual to use a person's name as the primary key of a table.
how about posting the SQL of the combo box's RowSource. and did you change
the combo box's BoundColumn from the default value 1, to something else?

 

[Guest]

The where condition that you are sending to the second form does not filter
the list box, it will sort only the form you are openning using the open form
command line.

If you want to filter a list box, then pass the value using the OpenArgs,
and then assign the row source to the list box

Dim MyOpenArgs As String
MyOpenArgs = "[Name] = '" & Forms![frmClass]![cboClassByName] & "'"
DoCmd.OpenForm "frmClassSearchList", , , "[Name] = '" &
Forms![frmClass]![cboClassByName] & "'" ,,, MyOpenArgs

On the Load event of frmClassSearchList you can write the code
Me.[lstListBoxName].RowSource = "Select Field1,Field2 From TableName Where "
& Me.OpenArgs

 

[Guest]

Ofer,

You are Gold mate!!
Thank You
The girls in the office are going to love you...........


Thanks
Glenn52

The where condition that you are sending to the second form does not filter
the list box, it will sort only the form you are openning using the open form
command line.

If you want to filter a list box, then pass the value using the OpenArgs,
and then assign the row source to the list box

Dim MyOpenArgs As String
MyOpenArgs = "[Name] = '" & Forms![frmClass]![cboClassByName] & "'"
DoCmd.OpenForm "frmClassSearchList", , , "[Name] = '" &
Forms![frmClass]![cboClassByName] & "'" ,,, MyOpenArgs

On the Load event of frmClassSearchList you can write the code
Me.[lstListBoxName].RowSource = "Select Field1,Field2 From TableName Where "
& Me.OpenArgs

--
I hope that helped
Good Luck

Sorry Guys,

Both versions brought up all records in the lstListBox.

Any ideas?

Glenn52