Looking for the right CRITERIA!

[Geir Baardsen]

Hi!
I need to know where I go wrong:

On a frmItems I have a listbox that shows all items in tblItems.
Now the items is keys from different producers.
So I can have three identical keys:

OR-303 s,
WA-303 s,
FL-303 s
and so on...

My problem is that when I open the frmItems and have say...OR-303 s focused,
I have a button I can click and which is supposed to show me similar keys,
that is: 303 s, in the lstItemsInfo.

So, this is the code in the form's OnLoad event:

With Me.lstItemsInfo
.RowSource = ""
.ColumnCount = 4
.ColumnWidths = "0cm;10cm;2cm;2cm"
.ColumnHeads = True
.BoundColumn = 1
.RowSource = UCase("SELECT DISTINCTROW ItemID,ItemName,UnitPrice, EnvFee " _
& "FROM tblItems " _
& "ORDER BY ItemName;")
End With

And believe me, it works just fine! But...Yes I say BUT:

I'll try to write the following sql that hopefully will
change the listbox with new values when click on btnSIMILAR:

With Me.lstItemsInfo
.RowSource = ""
.ColumnCount = 4
.ColumnWidths = "0cm;10cm;2cm;2cm"
.ColumnHeads = True
.BoundColumn = 1
.RowSource = UCase("SELECT DISTINCTROW ItemID,ItemName,UnitPrice, EnvFee " _
& "FROM tblItems " _

'Now try to find similar keys that has 303 in the name

& "WHERE (((ItemName) LIKE Left(Forms!frmItems!txtItemName.Text,4))) " _

'What is the correct criteria?

& "ORDER BY ItemName;")
End With

So U see? I miss something in the criteria. U know what?

Hopefully Yours,

Me.Name

Private Sub Form_Close
With Me.lstItemsInfo
.Rowsource = ""
End With
end sub

[Trevor Best]

Geir Baardsen wrote:

Hi!
I need to know where I go wrong:

On a frmItems I have a listbox that shows all items in tblItems.
Now the items is keys from different producers.
So I can have three identical keys:

OR-303 s,
WA-303 s,
FL-303 s
and so on...

My problem is that when I open the frmItems and have say...OR-303 s focused,
I have a button I can click and which is supposed to show me similar keys,
that is: 303 s, in the lstItemsInfo.

So, this is the code in the form's OnLoad event:

With Me.lstItemsInfo
.RowSource = ""
.ColumnCount = 4
.ColumnWidths = "0cm;10cm;2cm;2cm"
.ColumnHeads = True
.BoundColumn = 1
.RowSource = UCase("SELECT DISTINCTROW ItemID,ItemName,UnitPrice, EnvFee " _
& "FROM tblItems " _
& "ORDER BY ItemName;")
End With

And believe me, it works just fine! But...Yes I say BUT:

I'll try to write the following sql that hopefully will
change the listbox with new values when click on btnSIMILAR:

With Me.lstItemsInfo
.RowSource = ""
.ColumnCount = 4
.ColumnWidths = "0cm;10cm;2cm;2cm"
.ColumnHeads = True
.BoundColumn = 1
.RowSource = UCase("SELECT DISTINCTROW ItemID,ItemName,UnitPrice, EnvFee " _
& "FROM tblItems " _

'Now try to find similar keys that has 303 in the name

& "WHERE (((ItemName) LIKE Left(Forms!frmItems!txtItemName.Text,4))) " _

'What is the correct criteria?

& "ORDER BY ItemName;")
End With

So U see? I miss something in the criteria. U know what?
You want all the 303s?

"Where ItemName Like '??-" & Mid(txtItemName,4,3) & "*'"
Hopefully Yours,

Me.Name
;-)
Private Sub Form_Close
With Me.lstItemsInfo
.Rowsource = ""
End With
end sub

I assume you have the prompt for parameter when closing the form then? :-)

[Geir Baardsen]

> > Private Sub Form_Close

With Me.lstItemsInfo
.Rowsource = ""
End With
end sub

I assume you have the prompt for parameter when closing the form then? :-)

No prompt. Using ms access 2000. But the code suggestion you made, doesn't
work. Don't know why. Have tried to play with different variations, but
nothing seems to go. Have even tried: Me!lstItemsInfo instead of
Me.lstItemsInfo. Suggestions? : - )