Filter a drop down list?

[Guest]

I have a form for ClientID (drop down is fine) and a drop down for job# that
show CliendID, job#, and taxexempt# - based off of a Taxeempt table. I would
like when the client id is selected from the first drop down that the drop
down for the job# ONLY includes JOB # for that given Client ID. I also want
the taxexempt field automatically populated based on what job#,client id is
selected.

Please help ASAP

Thanks so much,
Barb

 

[Graham R Seach]

This will give you an idea of how to do it:
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Graham,

My code that I have put in is below. I am getting a Enter Parameter Value
when I go to click on the Job drop down list?? With the Client Ids . any
idea on why with the code below. Didn't really want to add a cust number
since they pretty much just go by the CustId

thanks,
BArb
Private Sub cboClientId_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of ClientId, based on the value selected
in ClientId
sSQL = "SELECT ClientId, Job" & " FROM tbltaxexempt WHERE ClientId = " &
Me.cboClientId & " ORDER BY Job"
Me.cbojob.RowSource = sSQL
End Sub

 

[Graham R Seach]

Babs,

If you're getting a "Enter Parameter Value" box, then Access can't find one
of the criteria used in the query. Check on ClientId, Job, or more likely,
Me.cboClientId. If ClientId is text, then this is what you need:
sSQL = "SELECT ClientId, Job" & _
" FROM tbltaxexempt " & _
"WHERE ClientId = """ & Me.cboClientId & """ " & _
"ORDER BY Job"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Guest]

Graham,

Thanks for your help. I think I did what you suggested but now I am getting
a Syntax errorin the FROM clause with the code below. I would love if you
could figur out what I did wrong. Not sure wiith the spaces and quotes and _
sign(think it's just for a continuation of a line.


Private Sub cboClientId_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of ClientId, based on the value selected
in ClientId
sSQL = "SELECT ClientId, Job" & " FROM tbltaxexempt" & "WHERE ClientId = """
& Me.cboClientId & """ " & " ORDER BY Job"
Me.cbojob.RowSource = sSQL
End Sub

 

[Graham R Seach]

Babs,

You missed a space between tbltaxexempt and WHERE. You also added a space
between cboClientId and ORDER BY (this one doesn't matter so much).

Since you're putting it all on one line, your string should look like this
(watch out, because your newsreader will wrap the following line):
sSQL = "SELECT ClientId, Job FROM tbltaxexempt WHERE ClientId = """ &
Me.cboClientId & """ ORDER BY Job"

If you want to put it on multiple lines:
sSQL = "SELECT ClientId, Job " & _
"FROM tbltaxexempt " & _
"WHERE ClientId = """ & Me.cboClientId & """ " & _
"ORDER BY Job"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Guest]

Just got back to working on this. Everything is good and working however
when the job#combo box displays after the client id is 1st selected with only
the job# for that client - it shows only those clients job#s which is good -
However, I need to have the combo box for job# show also - in this order
job,taxex#,clientid. Not sure where to put it in the code to not mess it up.

Thanks for your help,
Barb

 

[Graham R Seach]

sSQL = "SELECT Job, Taxex, ClientId " & _
"FROM tbltaxexempt " & _
"WHERE ClientId = """ & Me.cboClientId & """ " & _
"ORDER BY Job"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Guest]

Just tried it and now when I go to the job# combo box after selecting the
client id no info is showing. I see column line but nothing in them.

Here is my code

Private Sub cboClientId_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of ClientId, based on the value selected
in ClientId
sSQL = "SELECT Job, Taxex#, ClientId" & "FROM tbltaxexempt " & "WHERE
ClientId = """ & Me.cboClientId & """ " & "ORDER BY Job"
Me.cbojob.RowSource = sSQL
End Sub

Also, before I modified the code to above when I moved off the new record
and came back the job # field displays either empty or with the client id NOT
the JOB# that I selected.

Thanks again,
BARb

 

[Graham R Seach]

You have to pay attention to spacing in SQL! You eliminated the space after
ClientId on the first line.

Also pay attention to the underscore characters ( _ ). You eliminated all of
them!

sSQL = "SELECT Job, Taxex#, ClientId " & _
"FROM tbltaxexempt " & _
"WHERE ClientId = """ & Me.cboClientId & """ " & _
"ORDER BY Job"

In order to test a SQL statement in VBA, put a breakpoint after you assign
the SQL to the variable, then use the following syntax in the Immediate
Window to see what the variable contains:
?sSQL

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Guest]

I put the space back in on the first line but still do not see any job#
after I select the Client id. There is nothing in the drop down list. I
thougth the underscore where just to break up the lines. I always get an
error when I try to use them. I type the line the underscore and then hit
enter to move to next line. Not sure what I am doing wrong there either.

Thanks for your patience and continued help,
Barb

 

[Guest]

The taxex# a job field are also text fields. Does that change anything?

 

[Graham R Seach]

You said:
<<I always get an error when I try to use them>>
What error?? You have to tell me what's happening! What does the error say??

You also said:
<<I type the line the underscore and then hit enter to move to next line>>

Maybe that's teh cause of the abovementioned error. You should:
...type the line, then a space, then hit enter to move to the next line.

I repeat:
In order to test a SQL statement in VBA, put a breakpoint after you assign
the SQL to the variable, then use the following syntax in the Immediate
Window to see what the variable contains:
?sSQL

By the way, I assume you've set the combo's ColumnCount property to 3, and
its ColumnWidths property to 0cm;1cm;1cm (or whatever is appropriate to your
measurement units).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Graham R Seach]

Sorry, I should have said:
...type the line, then a space, then the underscore character - then
hit enter to move to the next line.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Graham R Seach]

Geez! I must be tired!

....type the line, then a space, then an ampersand (&), then a space, then
the underscore character - then hit enter to move to the next line.
sSQL = "blah blah" & _
"blah blah"

....and yes I'm certain it's right now!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Guest]

I got it to work- thanks. I need help with another filter type drop down
list on the same form. I am using a drop down list on my form from a RATES
table based on state, &
county. There are too many same counties with different states and it gets
confusing to user. I would like the first drop down list to be the list of
states - ONLY
listed one time. Then have the drop down list for counties to be populated
with the counties from that state. For the State drop down list - I have
made a query from the Rates table and used the unique
records or values so the states are listed only one time. Now when I try to
select the state from the state drop
down list- it displays them one time but does not actually allow me to SELECT
the state. Anyway I can select the state and show is only one time in drop
down. Also would like the rate to be displayed when the counties are
displayed - not sure on syntax of code based on previous example. - where
would need quotes???

Thanks for all you help

Thanks,
Barb

 

[Graham R Seach]

Babs,

I'm glad you got the first one working. Now for the second one.

You'll have to show me what you have so far, but you essentially do the same
thing as before.

For the cboStates RowSource property:
SELECT DISTINCT State FROM tblRates ORDER BY State

In the AfterUpdate event for cboStates:
Dim sSQL As String
Dim lDummy As Long

sSQL = "SELECT DISTINCT County FROM tblRates " & _
"WHERE State = """ & Me.cboStates & """ ORDER BY County"
Me.cboCounties.RowSource = sSQL
lDummy = Me.cboCounties.ListCount

Also add the following to the form's Current event:
Dim lDummy As Long
lDummy = Me.cboStates.ListCount

This forces the combo to fully populate, so you can scoll easier.

In all the above, change the control names, table name, and field names to
reflect the names of the objects you have.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Guest]

Graham,

Thanks for your help. All is working except for when I move to the next
record or go back and forth throught the records the state drop down stays
to the value of the most recent record that I just selected the state for(so
all records say the same state even though shouldn't be)- the county and rate
fields are fine. Any ideas??????

Thanks,
Barb

 

[Guest]

One more thing. Actually after I closed the database and went back into the
form the state field shows BLANK but the County and rate are fine. Would
like to hold that State value in the field for later reference???

Thanks,
Barb

 

[Graham R Seach]

Babs,

In order for the States combo to reflect the value selected for the current
record, you'll need to bind it to a field in the underlying table/query.
That way. it'll change as the records change. The same thing can be said of
the Counties combo.

If you just want to reset them, then add the following to the form's
Current event:
Me.cboStates.RowSource = ""
Me.cboCounties.RowSource = ""

But that won't change the controls' values - just what's displayed in them.
If you also want to reset their values, add the following to the form's
Current event in addition to what I showed above:
Me.cboStates = Null
Me.cboCounties = Null

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html