By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Looking for the right CRITERIA!

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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? :-)
Nov 13 '05 #2

P: n/a
> > 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? : - )

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.