select except a particular value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have two combo boxes and two text boxes on a form. When the form loads,
the first combo box gets pre-filled with the column values. The user selects
a particular value on the first combo box and adds comments in a particular
text box.
Now, I want the second combo box to get filled with the column values minus
the value already chosen on the first combo box. How do I incorporate this?
In particular, what SQL query will yield the desired result for the second
combo box in question?

Any inputs will be much appreciated.

TIA,
cole
 
hi Ken,

Thanks for your response. I did try to follow as directed in the link that
you mentioned. But, my problem is not solved.

Let me try to be clear in what I'm looking for:

Say I have a table 'Category' with data as below:
CategotyID CategoryName
------------ ----------------
1 vb
2 C
3 sql server
4 office

Now, say I have two combo boxes. cbOne and cbTwo.
Now, when the form loads, I ahve all the category Id's prefilled in cbOne.
When the user selects a value for cbOne (say 2) then,
I want only (1,3, and 4) to be listed in cbTwo.

I tried to do this:
1. Set to rowSource for cbOne to Category table - CategoryID
2. Added the afterUpdate event for cbOne

Private Sub cbOne_AfterUpdate()
Dim strSQL As String
strSQL = "select " & Me.cbOne
strSQL = strSQL & " from Category"
Me.cbTwo.RowSourceType = "Table/Query"
Me.cbTwo.RowSource = strSQL
Me.cbOne.Requery
End Sub

3. Left the RowSource for cbTwo blank.

What this does is list only that which is selected in cbOne (i.e. 2) and not
what I'm expecting (1,3, and 4).

Am I missing something?

thanks,
cole
 
You'd want:

strSQL = "select Id from Category"
strSQL = strSQL & "where Id <> " & Me.cboOne


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Hi,
You would want something like this:
Dim strSQL As String
strSQL = "select CategoryId, CategoryName From Category"
strSQL = strSQL & " WHERE CategoryId <> " & Me.cbOne
Me.cbTwo.RowSourceType = "Table/Query"
Me.cbTwo.RowSource = strSQL
Me.cbOne.Requery
 
Slight modification to Doug's syntax. A space is needed before the Where clause.
Try:

strSQL = "Select CategoryID from Category"
strSQL = strSQL & " Where CategoryId <> " & Me.cbOne

--
Jeff Conrad
Access Junkie
Bend, Oregon

in message
You'd want:

strSQL = "select Id from Category"
strSQL = strSQL & "where Id <> " & Me.cboOne
Click to expand...
 
Good catch, Jeff.

I SWEAR I had a space after Category when I first typed it, but I went back
to pretty it up...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Jeff Conrad said:
Slight modification to Doug's syntax. A space is needed before the Where
clause.
Try:

strSQL = "Select CategoryID from Category"
strSQL = strSQL & " Where CategoryId <> " & Me.cbOne

--
Jeff Conrad
Access Junkie
Bend, Oregon

in message
You'd want:

strSQL = "select Id from Category"
strSQL = strSQL & "where Id <> " & Me.cboOne
Click to expand...
Click to expand...
 
Thank you all for responding. It worked. It was a simple SQL query! I will
endeavour to spending more time in solving things like these before posting.
Appreciate your support.

thanks,
cole
 
Back
Top