That is correct, John. Don't be confused by the term "cascading combo
boxes", it is a concept or technique rather than a technical capability of
Access. All it means is that the second combo (name) uses the value of the
first combo (year) to filter the rows it will present.
Your row source for the year combo should be a query based on the table you
are using as the record source of your form. It should be a totals select
query with only one column, the year. It will then present one row for each
year for which you have data.
The row source for the names should be a select query with two columns, the
year and the name. The critera row for the year column should reference your
year combo box like: [forms]![MyFormName]![cboTheYear]
So in the After Update event of the Year combo, you should requery the name
combo so it will present a list of names that have data in the selected year.
Now, after you have selected a name, in the After Update event of the name
combo, you will have all the data you need to populate the form.
John C said:
Thanks Klatuu. I am not a pro at doing this kinda stuff in access so you
lost me in the first part. How would I set the combos to cascade? If I read
it right i need to run a query to sort out the ones that do not have figures
for a specific year.
:
No problem, John. It will not be that hard. For example purposes, I will
assume you select the year in the combo first, then the name. I would
suggest you use cascading combos in case our old friend Smith is in 2005, but
not in 2004, etc.
Set the row source for the name combo so that it is filtered by year and has
a column for each field you want to autofill. Then, in the After Update
event of the year combo, requery the name combo so it includes only names
for the selected year. Now, in the After Update event of the name combo, set
the controls on the form for the fields you want to auto fill:
Me.txtFirstFieldToFill = Me.cboName.Column(0)
Me.txtNextFieldToFill = Me.cboName.Column(1)
etc.
The column number index above is only for demo purposes, you will have to
match them to the correct controls. Remember that combo box column numbers
always start at 0.
:
Yes that is correct. In order to keep the name part from having duplicates I
plan to use first initial. Also there will be multiple entries of year in
this table. Sorry I was not more clear last post
:
That is what I thought you wanted, but you did not so state in your previous
post. So let me see if I have it correctly now. You are on a new record in
your form. The data source for your form is a table or query, but the data
you want to autofill from is in a different table. The user is required to
enter a year and a name in the combo boxes before the auto fill code will
execute. And, what if in the source table for the autofill (the one you are
getting data from) has more than one smith, how will you know which one to
use?
Please let me know if I correctly understand you, and we can come up with a
solution for you.
:
But I want the text boxs to populate based on the input in the year and name
combo box. So if someone puts in "2005" and "smith" from table year/name it
will autopopulate some of the fields on the form from data that is present on
the row that "2005" and "smith" are in.
:
Your question is a little vague, but if what you are asking for is to fill
text boxes on the form from the combo boxes, then you just have to assign the
values from the combo box to the text box in the After Update event of the
combo box:
Me.txtTheYear = Me.cboTheYear
:
I have a form that needs to have feilds populated based on to drop down
boxes. One drop down box will have a 4 digit year in it and the other will
have a name. I want to auto populater serveral fields based on the data that
is chosen by the combo box's. So if the user chooses "2005" and the name
"smith" I want the form to auto populate fields based on that data. Is there
a way to do this? What is the code?
