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]

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.

[ygs1234]

Thank you soo much for your reply NeoPa.

I looked back at your comments and your guides and they're helpful. Had to do a little googling, but the way you put it explains my situation. i managed to sort of get it working.

Please see attached pics.

pic 1 is my form i created showing u the 2000 and 2010 dropdown combobox.

pic 2 is the combobox for the states (didn't put the state name, but if i wanted to, i could add another column to the table and just add another bound column to show?)

pic 3 is the subform that populates after a state code has been selected.

pic 4 shows the data when a state from 2010 is selected, but for the state code, i had to change it a bit and add something to the end of it. I think b/c if i use the same state for 2000, it might think it's the same and over-write the data. i tried it before and it did over-write my data. or do i have to do something to the properties of the table?

and the last pic (tables) shows the tables and data i created that relate to the form.

THANKS A LOT!!

- edit - i also added a print button so that i can print out only the select state and the list of counties for that state for that year. :)

[NeoPa]

Let me see if I can respond to your various points. Essentially, your only problems are where you differ from my suggestions, but I'll try to deal with your comments individually anyway.

1. This is fine as far as it goes. I cannot check many of the details from this but it looks ok.
   
   You will eventually need to add values for all years that you need data for though.
2. Leaving the full name out is fine. Most people in the States would understand them by their ID anyway.
   
   However, should you need to add it in, see my comments in the previous post on how best to do it. There is no need for a separate control. if you choose to use one it certainly should not be a bound one.
3. This seems fine as far as it goes. i would expect to see the State ID shown too though. It will be necessary later on for when you are entering new records. Again, see post #15.
4. This problem is entirely due to you not implementing what I suggested. You should certainly not ever need to create state aliases for different years. That indicates your setup is fundamentally flawed.
5. [tblStates] should not have any reference to a Year in it. This makes no logical sense and is not what I suggested.
   
   [Population #1] needs to have the State code included. The State and the Year are both required to define a unique compound index. Maybe I didn't mention this before. I'm afraid I took this to be obvious, which is not too clever on my part. Please include this requirement into the overall spec.

I strongly suggest you go through your solution and compare it, point by point, with my post #15. Where they differ you need to give serious thought to how you can bring your project into line.

[ygs1234]

1. for the years, i think they only need the census records every 10 years. and probably 10 years later, they won't need this database anymore, but who knows :)

2. I will look closely at post #15 on how to add the full name to the states code. i duplicated my database and saved it to another location for testing purposes. when i added a state name column in the table, and then changed the column count to 2, there was an error (either the query or something else).

3. it's there, but i just dragged it and hid it. :) when i enter a new record, the state code section automatically populates itself, as if it knows. (like u know how the autonumber populates by itself? well so does my state code for each respective state)

4. Yeah, i knew that it would be a problem as it doesn't make sense to see two different names for state codes. will look into it and refer back to post #15.

5. i think that's what caused the problem in point #4. i think i might have switched the order around by puttig the year in [tblstates] and not in [population #1]. once again, will refer back to post #15.

closing commennts: Thanks for pointing out my errors one step at a time. i will re-read post #15 over. even though that form/table is flawed, for now, it works, but not the way it should properly work.

[ygs1234]

ok

i'll be going thru your points from post #15 one by one.

i duplicated my database so i can test it and make corrections before i do it to my real database.

Data (see attached image).

I removed all other flawed columns, etc and changed it the way you mentioned in post #1, which was:

Data
You will need tables for :
[got it] States. Each record would contain the abbreviation and the full name of the state.
[left this, put it in pop #1 table] 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.
[got it] 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.
[got it] Census. Each record would contain County, State, Year and Population.

[ygs1234]

point #2 (please see attached)

your original part in post #2
"Form
Header Section (or main form in your current approach)
In this section you would need the filtering controls.

[created a cbobox using wizard, selecting only year (sort ascending)] The Year would be selected from the Years table. Simple ComboBox.
[created] 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."

[ygs1234]

Point #3

"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. [created subform - see attached]

-- a bit confusing --. how to bound the cbobox to the field in subform?
The County code would be selected from a ComboBox, bound to the County field (didn't have the county table - you said it was optional :P). 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."

[ygs1234]

sorry for triple posting.. so far from your post #15, DATA and FORM are good for me. stuck on DETAIL. see post #21

thanks

[NeoPa]

In the picture in your post #21 there is no year or state selected. In that situation you would expect all the records to show.

One important issue from post #15 seems to be absent from any of your posted comments, and that is the CODE. Have you covered this aspect? It is certainly critical. Miss this out and you cannot expect to see things working.

PS. Well done for getting up to speed with the pictures. That saves me chunks of time :)

[ygs1234]

yeah i didn't use any code yet (no afterupdate or filter). i just got the "barebones" of everything you mentioned :P that's why no state or year was selected. the subform just shows everything on it.

yeah there was no code mentioned. i tried googling it, but didn't know what direction to go.

first of all, i need to know the code for AfterUpdate for Year, so that when i select a year, it updates to the state.

can you explain to me what components i need for the code?

thanks.

[NeoPa]

I can do that. Yes. I will be mainly duplicating what is already available in the article I linked you to in post #3, but it wouldn't be exactly the same. There is certain info I need though.

Are you planning to maintain the subform setup? This complicates things for me, but also for you. You will need to provide extra data - the names of the two forms and the name of the SubForm control.

I see you have Year and State controls in your grid. What are their names?

What are the names, and the Row Source values for the controls that filter the Year and the State.

[NeoPa]

ygs: first of all, i need to know the code for AfterUpdate for Year, so that when i select a year, it updates to the state.

It doesn't make sense to filter the State list by the year selected. The Year and the State together make a filter grid. Each State should be available in each Year. If this isn't what you're looking for then some of your earlier statements were misleading. This being the main reason why this process is anything but basic and straightforward.

ygs: can you explain to me what components i need for the code?

I can go into more detail with answers to my last post available, but the components of what is required have already been outlined in post #15.

[ygs1234]

do you mind if i just copy a part of my database and then post it up so you can check it for me?

im at home right now and don't have access to the DB, but i can copy the tables and forms that you will need to a separate DB so you can work from there.

is that a problem for u?

[NeoPa]

I'd be happy to do that, but it's nearly midnight here now and I can only do downloads from home (IE It may not get looked at until tomorrow night).

I'll just post below some instructions I often use to help members to attach databases to their posts.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[ygs1234]

Yeah no problem NeoPa. sorry to keep you up. it's 8 pm EST here in NY. so yeah, ill just copy it tomorrow.

[NeoPa]

Don't think you were keeping me up. I was (and generally am) up then and after. I just didn't relish starting a potentially big job at that point is all.

I'll look out for the file :)

[ygs1234]

hey. sorry for the late reply.

removed all unneccessary tables and queries from db.

compiled the database and the VB u mentioned in your instructions.

attached the database as a zip.

the form name in the db is called "test"

[NeoPa]

Excellent.

I'll send a note home to upload it tonight.

[ygs1234]

thanks! you've been a great help!!

[NeoPa]

The first problem I notice is that Population.Year is connected to tblCategories.strYear. It should be pointed out that while the [Year] field is numeric, the [strYear] field is text. Big problem.

[NeoPa]

The first thing I did, which seems to have fixed most/all of your problems, was to go through your table and relationship setups and make them consistent. There were various impossible settings that caused Access to be unable to behave in the way you expected. Suffice to say it seems to now. I'm going to check the form now to see if there is more to be done, but when I'm finished I'll certainly post my amended version. One field was renamed. txtYear is not a good name for a numeric field, which it is now of course.

[NeoPa]

Looking at the form I can see where much of the confusion is coming from.

There are two alternative ways to handle indexing in a table :

1. Create a specific AutoNumber PK.
2. Use existing fields which are known (and guaranteed) to be unique.

Your database appears to be using both (which doesn't work). It's as if you weren't sure which to use so used bits of both.

Normally, I'd recommend the AutoNumber route, but in this case I feel it will only make life a little more complicated for you. Bear in mind for future reference that this approach is more flexible and powerful, and in some situations is the only viable alternative.

For now though, I will set up my copy to lose all trace of these AutoNumber PK fields and use the two short [State] & [Year] fields for the PKs and linked indexing. That is, I will do that for the linked tables only. The [ID] field in [Population] may still be useful so I'll leave it in place.

It seems there is a fair bit more to come still, having now looked at your form [Test].

[NeoPa]

I've renamed your form [Test] to [frmPopulation].

I started by putting the two ComboBox filter controls in the Header section of the form where they logically belong.

I then added the code I'd talked about earlier in post #15 and all was well, although I spent a long time getting it right due to the form/subform complexity. It was just so wrong I missed the problem for a long time.

Anyway, here it is.

[NeoPa]

As a quick bonus - It didn't take very long - I whipped up the version of the form that doesn't use a subform.

By the way, the call to .Requery was not required. I tested both with and without so was able to confirm what I'd suspected.

The code for this one is :

Option Compare Database
Option Explicit
 
Private Sub cboYear_AfterUpdate()
    With Me
        .Year.DefaultValue = .cboYear
        Call DoFilter
    End With
End Sub
 
Private Sub cboStates_AfterUpdate()
    With Me
        .State.DefaultValue = "'" & .cboStates & "'"
        Call DoFilter
    End With
End Sub
 
Private Sub DoFilter()
    Dim strFilter As String
 
    With Me
        strFilter = ""
        If .cboYear > "" Then _
            strFilter = strFilter & " AND ([Year]=" & .cboYear & ")"
        If .cboStates > "" Then _
            strFilter = strFilter & " AND ([State]='" & .cboStates & "')"
        If strFilter > "" Then strFilter = Mid(strFilter, 6)
        .Filter = strFilter
        .FilterOn = (strFilter > "")
    End With
End Sub

[ygs1234]

OMG Thank you sooo much NeoPa!!

It is much better and is 1000% better than before.

What would you suggest now if I wanted to replicate what you did in this sample db into my main db?

can i just import the tables and forms into my original one?

ill re-read what you did, look over the tables and look over the code u used.

Thanks a bunch!!

[ygs1234]

ok i managed to redo what you did into my main database.

but somehow for the "detail" section, you used "text fields" and linked their respective control sources. i did that too, but mine doesnt' "expand" to show all counties. yours does for some reason. mine only shows one line. hmmm.... did you add something else?

in VB, i only saw 3 codes, two after updates and one dofilter.

-EDIT-
nvm, you used "continuous forms" :D

Thanks a bunch!!

[NeoPa]

I would have suggested importing all the objects (including the Relationships) from my version into yours and deleting the old versions of the same objects.

As long as you have it working though is all that really matters :)

[ygs1234]

thanks. :D