select except a particular value

[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

 

[Ken Snell [MVP]]

This article tells you how to tie the two combo boxes together:
http://www.mvps.org/access/forms/frm0028.htm

 

[Guest]

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

 

[Douglas J. Steele]

You'd want:

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Dan Artuso]

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

 

[Jeff Conrad]

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

 

[Douglas J. Steele]

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!)

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

 

[Guest]

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

 

[Jeff Conrad]

in message:

Good catch, Jeff.

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

No problem.