Need MVP to explain

[Guest]

I have been trying to use one combobox to select a project and then have the
next combobox display only the chanrge codes for that project. I have been
to the page http://www.mvps.org/access/forms/frm0028.htm that is always
suggested but that page is a bit above the level that I am at. Some specific
questions from that page are as follows:

1- in line 4 "from Categories" is throwing an error that it can't be
found... prob should be changed to something within the database but not
sure what

2- The whole explaination after the code that deals with filtering records
in a combo list. Where does the stored quiery parameter go?

This should help me get going I hope. Hopefully someone out there can give
some better detail than what is on that page.

 

[Ken Snell [MVP]]

I assume you understand that the code posted on that page is an example?

"from Categories" is used to demonstrate where you put the table name from
which the data are being obtained. Categories is the name of the table; from
is an SQL keyword. So, if your table were named "Tom", your code line would
be
"from Tom"

The explanation about filtering that you mention is a "second" way of doing
this technique. The code example is one way; the use of a stored query is a
second way. In this second method, you create a query that will provide the
data for your second combo box, and you use a "Criteria:" expression under
the field being filtered that references the first combo box where the
selection is made. Again, this example is using "generic" names of the form
("FormName") and the combo box name ("NameOfFirstControl").

 

[dan artuso]

Hi,
For your situation you have to alter the query that populates your charge
codes combo.
I assume there is a common field linking such as ProjectId in the table that
has your charge codes.

So you'd have something like:
Forms!yourForm!yourProjectCombo
as criteria for the ProjectId field in the query that feeds the charges
combo

Then just requery your charges combo in the project combos AfterUpdate
event:

Me.yourChargesCombo.Requery

You have to substitute the real names of course, or post them if you need
exact syntax.

HTH
Dan Artuso, MVP

 

[Guest]

Ken,

Thanks, that got me a little farther. The issue I have now is that it wants
me to input the value after I select the name of the program. So I have it
set to the table I just am not getting the value from the program code field.
Here is what the current code looks like:


Private Sub Combo8_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Combo8
strSQL = strSQL & " from Table1"
Me!Combo6.RowSourceType = "Table/Query"
Me!Combo6.RowSource = strSQL
End Sub

Table1 has 2 items in it, both text, the name of the program and then the
program codes

 

[Ken Snell [MVP]]

I assume that Combo8 contains the value that is to be used to filter the
second combo box, right?

If yes, then the code you've posted needs to be changed slightly (I've put
in generic field names in this code, and have assumed that the
ProgramCodeFieldName field is the one being filtered):

Private Sub Combo8_AfterUpdate()
Dim strSQL As String
strSQL = "Select ProgramCodeFieldName, ProgramNameFieldName"
strSQL = strSQL & " from Table1"
strSQL = strSQL & " WHERE ProgramCodeFieldName='" & " & Me!Combo8 & "'"
Me!Combo6.RowSourceType = "Table/Query"
Me!Combo6.RowSource = strSQL
End Sub

 

[Guest]

Ken,

That didn't really work. I am having trouble selecting the name of the
project. So what I had to do now is create another table with just the names
of the projects. This is then linked to the list that has the charge codes
and the project names. The table with the names and codes is Table1, the
table with just names is Table2. Here is the breakdown of each table

Table1: chargecode, projectname
Table2: projectname

The goal as stated is to use a combobox (named combo8) to select the
projectname.

There will be a second combobox (named combo6) that will then display the
chargecode (multiple charge codes) that are associated with the projectname
selected in combo8.

To date here is the code that has been created:
Private Sub Combo8_AfterUpdate()
Dim strSQL As String
strSQL = "Select projectname, chargecode"
strSQL = strSQL & " from Table1"
strSQL = strSQL & " WHERE projectname='" & "& Me!Combo8 & '"
Me!Combo6.RowSourceType = "Table/Query"
Me!Combo6.RowSource = strSQL
End Sub

**Now there probably needs to be a call to Table2 (maybe).

One issue that I am having is that I can select the projectname with no
problem but I get nothing in combo6 (chargecode).

**also I understand some of what the code is doing and how it is calling
different objects, but I don't understand the last call to assign the
RowSource to strSQL. What is that doing exactly?

 

[Ken Snell [MVP]]

Your apostrpophes and quotes are in the wrong places:

Private Sub Combo8_AfterUpdate()
Dim strSQL As String
strSQL = "Select projectname, chargecode"
strSQL = strSQL & " from Table1"
strSQL = strSQL & " WHERE projectname='" & Me!Combo8 & "'"
Me!Combo6.RowSourceType = "Table/Query"
Me!Combo6.RowSource = strSQL
End Sub

The last line, assigning the strSQL to the RowSource, is how you assign the
actual query statement to the combo box. The combo box gets its data from
that query.
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

I find all of this to be quite quirky. Specifically, I'm having two issues...
1) I have my two combo boxes in a subform that shows multiple records. If I
come into the form and combo8 (for example) is already populated, I find that
all of the combo6's show the dropdown that matches the first record. If I go
to another record and select a new value for combo8 it works fine (but the
dropdowns on all of the records now show the new dropdown). Basically the
"AfterUpdate" solution assumes that you have just populated the field, not
that the field already had a value.

2) My attempt at solving this was to move the requery to the 'Gotfocus'
event on combo6. This kind of works, but it displays all of the values as
blanks initially for some reason. If I click on the combo6 of a record, it
shows the value and the correct dropdown. When I click the combo6 of a
different record, then that one updates, but the other one goes back to a
blank.

Any ideas on how to work around this?

 

[Guest]

OK.. My bizarro work-around to the blanking out problem was to put a text
editor representing the same field right on top of the text edit portion of
the combo box and disable it for input.

I am still using the after update which means I still have to touch the
"master" combobox before touching the "slave". When I put a gotfocus event
on the slave, the dropdown never occurs for some reason. I also tied going
to the form level and running the requery on the SelectionChange event, but
that didn't seem to work either.

 

[Guest]

OK.. OnSelection is the wrong one.. With the OnCurrent on the form and
AfterUpdate both doing the requery and the TextBox hiding the text portion of
the combobox, I have a reasonably well working form. I'd still like to avoid
using the bogus textbox if possible though.

 

[Ken Snell [MVP]]

Curtis -

It sounds as if you're using an unbound combo box on a continuous forms
view. In this situation, I think you will need to continue using the
"textbox" so that you can display the correct data for each record.

However, I admit that I'm not sure I'm understanding what you're trying to
do here, as you've tagged your posts to a thread that had already been
completed earlier with a different poster.

Perhaps you can provide a more complete explanation about what you're trying
to do.

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

I am working on a school gradebook application. What I have is a form with
an unbound date picker on the left side. On the right is a continous subform
that contains lesson plans that are filtered to the selected date. Generally
about 5 entries from the plans table are visible. In the lesson plans, there
is a 'Subject' ComboBox (History, Math etc.). There is an assignment
category Combo and the list of available categories are dependent on the
selected subject. After a little more thought, my feeling as to what is
happening is that if I am on a 'Math' assignment for example, then the
current value displayed for a 'History' assignment is no longer in the
dropdown list for the combobox so it blanks out. If I select the 'History'
record, then the re-query allows that value to display, but not the value for
'Math' which is no longer valid for the combo box.

The extra textbox has given me a workable solution, but it just seems
strange that it's requried since this doesn't seem like it would be that
uncommon of a setup.

 

[Ken Snell [MVP]]

ACCESS currently requires the use of bound controls in a continuous forms
view if you wish to show different values for each record on the screen.
There is no way around this unless you wish to use a lot of
"under-the-covers" programming to manipulate data and controls and
visibility and recordsources and ....


--

Ken Snell
<MS ACCESS MVP>