Filter from Drop-Box

  • Thread starter Thread starter Chris Beardsley
  • Start date Start date
C

Chris Beardsley

My table is Tbl_QuestionBank and I have a field CDB_id
that identifies a course. My drop-box is called Quicklist
and consist of fields CDB_id (hiden key) and a text
field. When I select a field in the drop box, I am asked
to give a value for quicklist. I put togeather a debug to
show the values and the quicklist keeps appearing with " "
so I have even tried a val() statement to no evail.....
Please help! Here is my current code I am trying....

Me.RecordSource = "Select * from Tbl_QuestionBank
where CDB_ID = val(Quicklist)"
Refresh
Me.TQ_Topic.SetFocus
 
Hi Chris,

Try putting the reference to the Quicklist control outside of the quotes
that delimit the rest of the SQL string.

Using a line continuation to split the line you would have:

Me.RecordSource = "Select * from Tbl_QuestionBank " & _
"where CDB_ID = " & Me.Quicklist
 
thanks, but why does this work just by splitting the line
up?

Chris
-----Original Message-----
Hi Chris,

Try putting the reference to the Quicklist control outside of the quotes
that delimit the rest of the SQL string.

Using a line continuation to split the line you would have:

Me.RecordSource = "Select * from Tbl_QuestionBank " & _
"where CDB_ID = " & Me.Quicklist

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Chris said:
My table is Tbl_QuestionBank and I have a field CDB_id
that identifies a course. My drop-box is called Quicklist
and consist of fields CDB_id (hiden key) and a text
field. When I select a field in the drop box, I am asked
to give a value for quicklist. I put togeather a debug to
show the values and the quicklist keeps appearing with " "
so I have even tried a val() statement to no evail.....
Please help! Here is my current code I am trying....

Me.RecordSource = "Select * from Tbl_QuestionBank
where CDB_ID = val(Quicklist)"
Refresh
Me.TQ_Topic.SetFocus
Click to expand...

.
Click to expand...
 
Hi Chris,

What makes it work is putting the reference to the control *outside* the
quotation marks. In your original SQL string,

Me.RecordSource = "Select * from Tbl_QuestionBank where CDB_ID =
val(Quicklist)"

the following literal string gets passed to the database engine:

Select * from Tbl_QuestionBank
where CDB_ID = val(Quicklist)

The database engine (Jet) tries to resolve val(Quicklist) by looking for a
field or parameter named QuickList but Jet does not know to look at the
control on your form because the value used in the criteria is not a
properly formed reference to a control on a form.

Instead, by putting the value *outside* of the quotation marks, VBA can
substitute in the value of the control so if the current value of
Me.QuickList happens to be 4, using this code (unsplit line this time):

Me.RecordSource = "Select * from Tbl_QuestionBank where CDB_ID = " &
Me.Quicklist

the string that goes to the database engine is:

Select * from Tbl_QuestionBank
where CDB_ID = 4

I hope this makes it more clear for you!

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Chris said:
thanks, but why does this work just by splitting the line
up?

Chris
-----Original Message-----
Hi Chris,

Try putting the reference to the Quicklist control outside of the quotes
that delimit the rest of the SQL string.

Using a line continuation to split the line you would have:

Me.RecordSource = "Select * from Tbl_QuestionBank " & _
"where CDB_ID = " & Me.Quicklist

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Chris said:
My table is Tbl_QuestionBank and I have a field CDB_id
that identifies a course. My drop-box is called Quicklist
and consist of fields CDB_id (hiden key) and a text
field. When I select a field in the drop box, I am asked
to give a value for quicklist. I put togeather a debug to
show the values and the quicklist keeps appearing with " "
so I have even tried a val() statement to no evail.....
Please help! Here is my current code I am trying....

Me.RecordSource = "Select * from Tbl_QuestionBank
where CDB_ID = val(Quicklist)"
Refresh
Me.TQ_Topic.SetFocus
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top