Filter one combo box to another that populates a child form? [confusing]

[ygs1234]

Hi guys. I'm new to this forum. I had zero knowledge of access when i started interning at my company. it's been only 3 weeks and i'm somewhat getting the hang of it.

however, stuff like this (mentioned in title) can still be confusing for me.

ok so here's my problem.

i would like to create a form that contains the following:
1. a combo box for 2000 and 2010.
2. another combo box for state names (i.e. alabama, arkansas, etc)
3. a child form/spreadsheet or whatever it's called that is put in the form.

and when i type to the spreadsheet/child form, it saves to my table.

hope this is not too confusing.

----

this is what i want it to do:

1. when i select the year 2000, and i select the state New York, i can type in the childform to put in the county and population #.
2. when i select another year, 2010, i can select the same state, New York, but the childform will be blank because i didn't type anything yet. i only typed in the 2000 one.

my boss wants me to make it so that when he can see all the counties in New York state in the year 2000 and 2010.

THANKS!!

[ygs1234]

Sync 2 Combo Boxes

So I need to sync two combo boxes and also add a "child" where i can type my fields and data

1 cbobox would be "Year" (with 2000, 2001, 2002, 2003, etc)

another cbobox would be "State" (with NY, NJ, NH, RI, etc)

let me explain it to the simplest i can.

i want to select [year] with all [states], but when i select [state], it will only populate the stuff for that state. when i select another year, same state, it will populate only that state for that particular year.

how many tables do i have to make?
1 for state codes, 1 for years, and 1 for my data for the child form?

Thanks!

[NeoPa]

I suggest you look through Example Filtering on a Form first. Only the main table would be required. The ComboBoxes could be filled from the values used in the main table.

Welcome to Bytes!

[jdfergus]

1. a combo box for 2000 and 2010.

In the toolbar for the form, choose "combo box" and select "I want the table to look up the values in a table or query", then select the appropiate table and field.

2. another combo box for state names (i.e. alabama, arkansas, etc)

Same as above.

3. a child form/spreadsheet or whatever it's called that is put in the form.

It's called a "subform" and can be found in the toolbar as well. Choose whatever table you need to enter the data, then link your table by both your year field and state field when prompted.

This should work, and your question wasn't too confusing, so hopefully my answer isn't either.

[NeoPa]

YGS,

Please do not double-post your questions. This is against our rules and causes a lot of work to tidy up (thus not making you very popular).

I appreciate you're new to this and quite young I'm guessing, so we can leave it there for now.

Luckily, these threads still makes sense when merged into one as I've done.

[ygs1234]

so sorry. :( didn't read the forum rules, which i should have done.

Thanks for the advice and tips! Will look into that link.

P.S: I'm only 17. :)

--
I get the concept of it, but just don't know how to like do the coding part in the 'after update'

so, the 'year' cbobox DRIVES the 'state' cbobox which then brings up the subform.
But it's not really a filter, since I need to put all the states for 2000 and all the same states for 2010. so i'm copying the states for both years, but the data in the subform will be different. Does that make sense?

[NeoPa]

It doesn't seem to make much sense :s

Why do you feel it's not related to filtering? Each ComboBox can cause a filter, or extra level of filtering if you prefer, to both the contents of the form itself and the other ComboBox. In case more understanding is required please check out Cascading Combo/List Boxes. I personally find the first one makes more sense, but then I wrote that one so may well be biased.

If this doesn't help, and especially as you're just starting out, let us know and I'll see what I can do about taking it step by step for you.

[ygs1234]

let me to try explain what my project is.

so i'm working at a research department and i know that the statistics for the census 2010 is not out yet. so as of now, my boss only wants me to put the population # for each county in every state in the year 2000 for ALL states.

so far so good?

right now, i have one combo box that lists all the states. i also have a text field/label that automatically matches the state code. so for example, when i select NY from the combobox, the field/label will show up as New York (state name).

so far so good?

so when i pull up New York, the subform will only show the population # for new york counties. if i select another state, say NJ, the subform refreshes (i think it's called requery) and i only see the counties for NJ.

now what I want to do is add another combo box with only two fields (the year 2000 and the year 2010). so when i click on 2000 > then state > it will show population for those counties in that state in 2000. but when i click on 2010 > the same state i just clicked > it will show the population for that those counties (but there are no statistics yet, that's why it will be blank).

i hope this is better.

if not, i can try and put this in a graphical way using Paint.

[NeoPa]

So if a record doesn't exist yet you would like it to be shown anyway. That's a little more tricky than I'd understood before I must admit. You really want it to do two jobs, but make it look like just the one - and do it smoothly too. Interesting.

You'll need a table for the years and a table for the states (to populate your ComboBoxes). Years should be simple enough. States would need code and name fields.

The main table would need a compound index built of two fields which are FK (Foreign Key) to the Years & States tables. This compound index could be your PK (Primary Key) but certainly needs to be unique. It would need other fields too to hold the data of course.

The form would need unbound ComboBoxes for the years and the states, and bound TextBoxes for the year and state of the current record. Other data can have whatever bound controls are appropriate. In the AfterUpdate() events of each of the unbound ComboBoxes, a procedure should be called to set the current record of the form (This can be done easily by filtering) as well as the .DefaultValues of the bound Year & State TextBoxes. The bound Year & State TextBoxes should not be updatable by the operator. If/when other data is entered into the form, then the record becomes active (dirty) and will be saved automatically when navigated away from.

Does this more closely match your requirement.

[ygs1234]

wow neopa! that sounds complicated lmao.

when i make the form, do i make it blank, as in no tables in the field list.

before, when i just had states and a subform, i used the table from states to make the form.

ok so what if i just add the county names for 2010, that would be data/records in the new table.

i'm only 17 and they expect me to understand such complicated stuff. :(

[NeoPa]

17 is fine, but we have to be able to communicate using simple English.

I can understand you having difficulty with my post, but when you explain your position using terms that make sense only within your project, and you haven't explained them, then I can only try to guess what you mean. This is not a reliable way to proceed, so I'll give the ball back to you. Communicate with me. Either express yourself relative to what I've already said, or do it using terms that are generally meaningful and don't require detailed knowledge of your project (at least until you have explained enough of it to give the context).

[ygs1234]

Hi NeoPa, i drew a diagram explaining what i have to do. hope you can understand it :)

[ygs1234]

i could also get a copy of my database at work and copy out just the tables related to this.

adding another combo box that filters another one can be quite confusing.

what i had was just a combo box that filters a subform based on selection (something about child and master fields, state name and code). that's because i based my form off of the state code and name table.

[NeoPa]

Let me start by apologising for my prolonged absence from this thread. It's always difficult to find the right answer when the question isn't very clear, but I feel I should have given it more attention before now. So apologies are due certainly.

As for the difficulty you're having, your last posts have been helpful in explaining the situation (Thank you) but there will often be times when I, or another expert, will want to clarify items that you may not appreciate. When this happens, it is advisable, where at all possible, to respond directly to the questions posed. If I ask you to confirm that A < 5, then don't tell me A = 1. Just Yes is all I'm looking for. That may not be an easy metaphor to understand, but I hope you get the gist. The chances are we will ask something couched in terms that are important. If your reply re-explains in another way, rather than addresses the question directly, we end up short of the information we require.

I say all that because it's very important, but I do appreciate that sometimes you cannot reply directly due to a lack of understanding of the subject. If you have no idea what I'm on about, giving a direct answer can be difficult.

I hope all these explanations haven't put you off. I'm not trying to criticise you, simply explaining things that will help us going forward (and likely in other situations too) to get to the heart of the matter quickly.

While I give your problem some more thought, let me first say that what you have is possibly a little more complicated for you than it need be. I see no reason here for a subform at all. A more straightforward way of handling reviewing of filtered records is to have your unbound controls (that determine the filter) in the header section of your main form and the data being shown and filtered in the Detail section of that same main form. Although similar things may be done in both scenarios the references for subforms are generally more complex so you end up doing more, and more complex, code. Let me know if you feel changing the design at this stage is something you'd consider (while I look further at your situation).

[NeoPa]

I will try to keep my instructions relative to both approaches until I here from you which you choose to use.

If there is anything I say that you do not, and cannot with a little checking, understand, then let me know. Everything is a foundation for everything that comes after it, and if you don't get one bit then others may be confusing too, as they're related.

Data
You will need tables for :

1. States. Each record would contain the abbreviation and the full name of the state.
2. Counties within States. This is optional, but could be helpful when entering the data. It could also help to avoid some data entry errors. I'll include it in the design anyway for now.
   
   Each record would contain a valid County code and State code. It would also have the description, or full name, of the County.
3. Years. Seems simple, but the data in here would need to be populated before we could either display, or enter, new census data for that year.
4. Census. Each record would contain County, State, Year and Population.

Form
Header Section (or main form in your current approach)
In this section you would need the filtering controls.

The Year would be selected from the Years table. Simple ComboBox.
The State would be selected from the States table. This would be a multi-column ComboBox. It would have two columns. The code and the full name. The Bound Column would be the State code, but when selected both the code and the full name would display.
There is no need for a separate State Name control.

Detail section (or Subform in your current approach)
This would be much as you have it displayed in your attached diagram, but including the Year to be shown also. The Record Source would be the Census table, but it would generally be shown only after filtering had been applied.

The County code would be selected from a ComboBox, bound to the County field. The ComboBox would have a Row Source filtered to show just the valid counties for the current State. It would have two columns. One for the code and one for the full name. The first column would be the Bound Column, but when selected both the code and the full name would display.
The State code should not be accessible by the operator. This is populated by the code (See below).
The Year. Pretty similar to the State code above.
The Population. This is entered when there is data to go in it. Only this and the County code are selected in this section by the operator.

Code
When changes are made to either the Year or the State in the filtering section, then code is run to reflect the changes.

Each AfterUpdate event procedure would have specific and general work to do. The general work should be put into a separate procedure to do.

The specific work would consist of setting the DefaultValue property of the related control in the Detail section (or subform) to match the value selected (from the filter control).

The general work would consist of setting up the Filter property of the form (or subform) to show only those items that match both filter controls.

Comments
Unless I still misunderstand you, this setup should exactly meet your needs.