Enter parameter value popping up

  • Thread starter Thread starter XMan
  • Start date Start date
X

XMan

The parameter value window keeps popping up( which I really don't want)
whenever I run this code to change the source query for my subform. I do
this on update event of a listbox.

strSQL = "SELECT * FROM LOCATIONS INNER JOIN LOCATIONS_WITHIN ON
LOCATIONS.LOCATION_UI = LOCATIONS_WITHIN.LOCATION_SUB_UI "

strSQL = strSQL & "WHERE LOCATIONS.LOCATION_TOP_UI = " &
Me![lbxSite].Column(0)

Me!frmUnit.Form.RecordSource = strSQL

TIA.
 
Pity you didn't say what parameter it is asking for. That would be a big
clue.

My guess is that LOCATION_TOP_UI is a text field and that the left-most
column of lbxSite contains text, in which case the value needs to be
enclosed in either single or double quotes:

strSQL = strSQL & "WHERE LOCATIONS.LOCATION_TOP_UI = '" _
& Me![lbxSite].Column(0) & "'"

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
 
Yes it's asking for LOCATION_TOP_UI and its data type is number and not
text. The lbxSite is a listbox of LOCATIONS with 1st column has LOCATIONS_UI
(autonumber), and 2nd column has DESCRIPTION (text). Any help is much
appreciated.


Graham Mandeno said:
Pity you didn't say what parameter it is asking for. That would be a big
clue.

My guess is that LOCATION_TOP_UI is a text field and that the left-most
column of lbxSite contains text, in which case the value needs to be
enclosed in either single or double quotes:

strSQL = strSQL & "WHERE LOCATIONS.LOCATION_TOP_UI = '" _
& Me![lbxSite].Column(0) & "'"

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

XMan said:
The parameter value window keeps popping up( which I really don't want)
whenever I run this code to change the source query for my subform. I do
this on update event of a listbox.

strSQL = "SELECT * FROM LOCATIONS INNER JOIN LOCATIONS_WITHIN ON
LOCATIONS.LOCATION_UI = LOCATIONS_WITHIN.LOCATION_SUB_UI "

strSQL = strSQL & "WHERE LOCATIONS.LOCATION_TOP_UI = " &
Me![lbxSite].Column(0)

Me!frmUnit.Form.RecordSource = strSQL

TIA.
Click to expand...
Click to expand...
 
WOOOO! I've found the culprit after looking at the query another thousand
times

It's should be LOCATIONS_WITHIN for the WHERE statement!

Thanks a bunch!


Graham Mandeno said:
Pity you didn't say what parameter it is asking for. That would be a big
clue.

My guess is that LOCATION_TOP_UI is a text field and that the left-most
column of lbxSite contains text, in which case the value needs to be
enclosed in either single or double quotes:

strSQL = strSQL & "WHERE LOCATIONS.LOCATION_TOP_UI = '" _
& Me![lbxSite].Column(0) & "'"

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

XMan said:
The parameter value window keeps popping up( which I really don't want)
whenever I run this code to change the source query for my subform. I do
this on update event of a listbox.

strSQL = "SELECT * FROM LOCATIONS INNER JOIN LOCATIONS_WITHIN ON
LOCATIONS.LOCATION_UI = LOCATIONS_WITHIN.LOCATION_SUB_UI "

strSQL = strSQL & "WHERE LOCATIONS.LOCATION_TOP_UI = " &
Me![lbxSite].Column(0)

Me!frmUnit.Form.RecordSource = strSQL

TIA.
Click to expand...
Click to expand...
 
It's funny how one more look often solves the problem :-)

I'm glad you got it sorted.
 
XMan said:
The parameter value window keeps popping up( which I really don't want)
whenever I run this code to change the source query for my subform. I do
this on update event of a listbox.

strSQL = "SELECT * FROM LOCATIONS INNER JOIN LOCATIONS_WITHIN ON
LOCATIONS.LOCATION_UI = LOCATIONS_WITHIN.LOCATION_SUB_UI "

strSQL = strSQL & "WHERE LOCATIONS.LOCATION_TOP_UI = " &
Me![lbxSite].Column(0)

Me!frmUnit.Form.RecordSource = strSQL
Click to expand...

XMan,

is the field LOCATIONS.LOCATION_TOP_UI by any chance text type? If
yes, the criteria must be enclosed in quotes:

strSQL = strSQL & "WHERE LOCATIONS.LOCATION_TOP_UI = '" &
Me![lbxSite].Column(0) & "'"

Watch out for the single quotes!

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Back
Top