Form listbox & SQL problem

B

Billy B

I have a table with a field name of Purchased. It is a number, integer,
decimal places = 0.
I have a form with a list box. The list box shows the StoreNumber (an
integer, decimal places Auto and no default) and StoreName from a different
table. When the user picks a store from the listbox, I want to open a form
based on that store number. Below is the SQL statement. The form opens OK but
shows all records in the table. If the selection from the list box was
StoreID 2, I get a 2 character variable (I used the Len function and result
was that intstorenum is 2 characters long). What am I missing?


intStoreNum = Val(lstFindOptions.Column(0))
strCriteria = "SELECT DISTINCT tblPlants.PlantID, tblPlants.PlantName,†_
“tblPlants.Alias, tblPlants.BotonName, tblPlants.PlantType,
tblPlants.PlantCat, “ & _
tblPlants.GrowthSize, tblPlants.PlantingLoc, tblPlants.Description,
tblPlants.Culture, tblPlants.Moisture, tblPlants.HardinessZones,
tblPlants.Features, tblPlants.Usage, tblPlants.PlantWarnings,
tblPlants.PicPath FROM tblPlants WHERE tblPlants.Purchased = & intStoreNum &"

Thank you.
 
K

Ken Snell \(MVP\)

Where are you using the strCriteria variable? In a DoCmd.OpenForm method?
You need to post more of your code.

My initial guess is that you are using strCriteria as the fourth argument of
a DoCmd.OpenForm method. If this is correct, this variable should contain
only the WHERE clause's string (without WHERE):

strCriteria = "tblPlants.Purchased = " & intStoreNum

There also are some syntax errors in the code string that you've posted for
the strCriteria SQL statement.
 
B

Billy B

Thank you. Here is more code. I hope this helps.


Private Sub lstFindOptions_Click()
'Open frmPlantsEdit based on the criteria selected in the frmFindRecords

Dim intPlantNum As Integer, intStoreNum As Integer, strFormNam As String
Dim strCriteria As String, strShowInfo As String, intTagNum As Integer

intTagNum = lstFindOptions.Tag

strFormNam = "frmPlantsEdit"

*******************
'All Case statements work right except Case 7

Select Case intTagNum

Case 1 'Opens frmPlantsEdit to the plant selected in the listbox

intPlantNum = lstFindOptions.Column(0)
strCriteria = "PlantID = " & intPlantNum

Case 3 'Opens frmPlantsEdit that matches the plant type (Perenial, Annual,
etc)
strShowInfo = lstFindOptions.Column(0)
strCriteria = "SELECT DISTINCT tblPlants.PlantID, tblPlants.PlantName,
tblPlants.Alias, tblPlants.BotonName, tblPlants.PlantType,
tblPlants.PlantCat, tblPlants.GrowthSize, tblPlants.PlantingLoc,
tblPlants.Description, tblPlants.Culture, tblPlants.Moisture,
tblPlants.HardinessZones, tblPlants.Features, tblPlants.Usage,
tblPlants.PlantWarnings, tblPlants.PicPath, tblPlants.Purchased FROM
tblPlants WHERE tblPlants.PlantType = '" & strShowInfo & "'"
'strCriteria = "qryFindPlantType"


Case 5 'Opens frmPlantsEdit that matches the plant category (Aquatic,
shrub, tree, etc)
strShowInfo = lstFindOptions.Column(0)
strCriteria = "SELECT DISTINCT tblPlants.PlantID, tblPlants.PlantName,
tblPlants.Alias, tblPlants.BotonName, tblPlants.PlantType,
tblPlants.PlantCat, tblPlants.GrowthSize, tblPlants.PlantingLoc,
tblPlants.Description, tblPlants.Culture, tblPlants.Moisture,
tblPlants.HardinessZones, tblPlants.Features, tblPlants.Usage,
tblPlants.PlantWarnings, tblPlants.PicPath, tblPlants.Purchased FROM
tblPlants WHERE tblPlants.PlantCat = '" & strShowInfo & "'"

Case 6 'Opens frmPlantsEdit that matches tHE planting location
'strShowInfo = lstFindOptions.Column(0)
strCriteria = "SELECT tblPlants.PlantID, tblPlants.PlantName,
tblPlants.Alias, tblPlants.BotonName, tblPlants.PlantType,
tblPlants.PlantCat, tblPlants.GrowthSize, tblPlants.PlantingLoc,
tblPlants.Description, tblPlants.Culture, tblPlants.Moisture,
tblPlants.HardinessZones, tblPlants.Features, tblPlants.Usage,
tblPlants.PlantWarnings, tblPlants.PicPath, tblPlants.Purchased FROM
tblPlants WHERE tblPlants.PlantingLoc = & strShowInfo "

Case 7

intStoreNum = lstFindOptions.Column(0)

strCriteria = "SELECT DISTINCT tblPlants.PlantID, tblPlants.PlantName, " & _
"tblPlants.Alias, tblPlants.BotonName, tblPlants.PlantType," & _
"tblPlants.PlantCat, tblPlants.GrowthSize, tblPlants.PlantingLoc, " & _
"tblPlants.Description, tblPlants.Culture, tblPlants.Moisture, " & _
"tblPlants.HardinessZones, tblPlants.Features, tblPlants.Usage, " & _
"tblPlants.PlantWarnings, tblPlants.PicPath FROM tblPlants WHERE " & _
"tblPlants.Purchased = & intStoreNum & "



End Select
DoCmd.OpenForm "frmPlantsEdit", , strCriteria
Me.Visible = False


End Sub
 
J

John Spencer

You are missing a quote in the statement (at the end) and seem to have
an extra & and quote at the very end of the statement.

intStoreNum = lstFindOptions.Column(0)

strCriteria = "SELECT DISTINCT tblPlants.PlantID, tblPlants.PlantName, " & _
"tblPlants.Alias, tblPlants.BotonName, tblPlants.PlantType," & _
"tblPlants.PlantCat, tblPlants.GrowthSize, tblPlants.PlantingLoc, " & _
"tblPlants.Description, tblPlants.Culture, tblPlants.Moisture, " & _
"tblPlants.HardinessZones, tblPlants.Features, tblPlants.Usage, " & _
"tblPlants.PlantWarnings, tblPlants.PicPath FROM tblPlants WHERE " & _
"tblPlants.Purchased = " & intStoreNum

That assumes the Purchased is a number field and not a text field.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Top