List Box question

  • Thread starter Thread starter Harmony
  • Start date Start date
H

Harmony

I have a follow up question about this link. It says to
reference a hidden control to which I assign the code. How
do I do that??

Thanks!!!

Harmony
----------------------------------

Hi,
See if this helps you out:
http://www.mvps.org/access/forms/frm0007.htm

There is no simple way to do it, you have to write some
code.
 
On the form - in design view - create a new textbox and set it's
visible property to No. For example you might name it txtWhere and
then set your query to reference it.

- Jim
 
On your form create another control (a textbox should do).
What they want you to do is assign the value of the new
textbox using a long string (SQL statement) - generated by
the code.
For example purposes we'll call your listbox List1 and the
new textbox Text2. On the menu select View > Code.
Now from the drop-down on the left select List1 and from
the right choose Click (or LostFocus or whatever event you
wish to choose) then Access will create a subroutine.
Inside that subroutine insert the code form the link and
just before the End Sub statement add the following:
Text2.Controlsource = strSQL
When you run this, the text box will get filled with the
string generated by the code. If for example, your List1
is a list of Names, in your query - under the Name column
and in the Criteria row, enter the value for Text2:
[Forms]![Form1]![Text2]
(We are also assuming your form is named Form1 here)
Anyway, once you get it working the way you like, make the
Text2 invisible(hidden) to the users. In form design mode
select the textbox and open the properties dialog box,
then on the Format tab, change Visible to No.

Hope this helps.
jmonty
 
Thanks for the tip..

How do I refer to this code in that text box?
Click to expand...
It isn't code that you would put in the textbox. It's part of an SQL
string - part of the WHERE clause.

It would be something like this ...

Dim strItems As String
Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.lstBox

'Assuming long [EmpID] is the bound field in lb
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem

'Trim the end of strSQL
strItems = left$(strItems, Len(strItems) - 12)
Me.txtWhere = strItems

Now, in the queries window's Criteria row under the field you are
applying this to (in the above example it is [EmpID]) build a
reference to the form or put in your version of ...
[Forms]![frmMyForm]![txtWhere]

- Jim
 
It would be something like this ...

Dim strItems As String
Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.lstBox

'Assuming long [EmpID] is the bound field in lb
For Each varItem In ctl.ItemsSelected
Click to expand...

'That should be...
strItems = strItems & ctl.ItemData(varItem) & " OR [EmpID]="

'So much for air code.
 
I am getting a runtime error on the last line of the
code: Me.txtWhere = strItems

It says "You can't assign a value to this object", do you
have any ideas?

Thanks for your help, I feel that I am getting a lot
closer.
-----Original Message-----
It would be something like this ...

Dim strItems As String
Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.lstBox

'Assuming long [EmpID] is the bound field in lb
For Each varItem In ctl.ItemsSelected
Click to expand...

'That should be...
strItems = strItems & ctl.ItemData(varItem) & " OR [EmpID]="

'So much for air code.
Next varItem

'Trim the end of strSQL
strItems = left$(strItems, Len(strItems) - 12)
Me.txtWhere = strItems
Click to expand...

.
Click to expand...
 
Hmm, that sounds like something wrong with the textbox (txtWhere)
settings. Check the following:
That it is a text box - not a label for example.
That it is unbound - the Control Source for it is empty.
Enabled is Yes

- Jim

I am getting a runtime error on the last line of the
code: Me.txtWhere = strItems

It says "You can't assign a value to this object", do you
have any ideas?

Thanks for your help, I feel that I am getting a lot
closer.
-----Original Message-----
It would be something like this ...

Dim strItems As String
Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.lstBox

'Assuming long [EmpID] is the bound field in lb
For Each varItem In ctl.ItemsSelected
Click to expand...

'That should be...
strItems = strItems & ctl.ItemData(varItem) & " OR [EmpID]="

'So much for air code.
Next varItem

'Trim the end of strSQL
strItems = left$(strItems, Len(strItems) - 12)
Me.txtWhere = strItems
Click to expand...

.
Click to expand...
Click to expand...
 
They are all set that way, but I still get the error.
-----Original Message-----
Hmm, that sounds like something wrong with the textbox (txtWhere)
settings. Check the following:
That it is a text box - not a label for example.
That it is unbound - the Control Source for it is empty.
Enabled is Yes

- Jim

I am getting a runtime error on the last line of the
code: Me.txtWhere = strItems

It says "You can't assign a value to this object", do you
have any ideas?

Thanks for your help, I feel that I am getting a lot
closer.
-----Original Message-----
On Mon, 29 Dec 2003 20:08:52 GMT, "Jim Allensworth"

It would be something like this ...

Dim strItems As String
Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.lstBox

'Assuming long [EmpID] is the bound field in lb
For Each varItem In ctl.ItemsSelected

'That should be...
strItems = strItems & ctl.ItemData(varItem)
Click to expand...
& "
OR [EmpID]="
'So much for air code.

Next varItem

'Trim the end of strSQL
strItems = left$(strItems, Len(strItems) - 12)
Me.txtWhere = strItems

.
Click to expand...
Click to expand...

.
Click to expand...
 
Thanks for your help. I am still having issues. The field
returns "#Name?", but when I look at the control source it
returns the names from my list. Now my challanges is
getting the names into my query criteria. Also, these are
names and I am not sure my query will recongnize them
without a quote around each individual name.

HELP!

Thanks!!

Harmony
-----Original Message-----
On your form create another control (a textbox should do).
What they want you to do is assign the value of the new
textbox using a long string (SQL statement) - generated by
the code.
For example purposes we'll call your listbox List1 and the
new textbox Text2. On the menu select View > Code.
Now from the drop-down on the left select List1 and from
the right choose Click (or LostFocus or whatever event you
wish to choose) then Access will create a subroutine.
Inside that subroutine insert the code form the link and
just before the End Sub statement add the following:
Text2.Controlsource = strSQL
When you run this, the text box will get filled with the
string generated by the code. If for example, your List1
is a list of Names, in your query - under the Name column
and in the Criteria row, enter the value for Text2:
[Forms]![Form1]![Text2]
(We are also assuming your form is named Form1 here)
Anyway, once you get it working the way you like, make the
Text2 invisible(hidden) to the users. In form design mode
select the textbox and open the properties dialog box,
then on the Format tab, change Visible to No.

Hope this helps.
jmonty

-----Original Message-----
I have a follow up question about this link. It says to
reference a hidden control to which I assign the code. How
do I do that??

Thanks!!!

Harmony
----------------------------------

Hi,
See if this helps you out:
http://www.mvps.org/access/forms/frm0007.htm

There is no simple way to do it, you have to write some
code.

--
HTH
Dan Artuso, Access MVP




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