filter drop down list based on the value of another field - pls help with syntax

[mjvm]

HI,

I have had a search for the answer to this question, but I can't transfer what I am reading to my database. I don't know enough about the language required, but have been able to get my database to do what I want by following your instructions!

I want to filter a drop down list based on the entry of another field - which is also a lookup field. There is a master table of schools, and I have two more tables - one for regions, and then another which lists the networks in each region. So:

tblRegions has 2 fields: ID and Region;
tblNetworks has 3 fields ID, Region and Network.

In a form for schools, when I have entered a region, I want the Network drop down list to give me only those networks that belong in that region.

I have entered the following in the Row Source, but it isn't working - can someone please help me fix it:

SELECT [tblNetworks].[Network] FROM tblNetworks WHERE tblNetworks.Region=Forms![Form1]![Region];

(the form is still called form1 because I am in the process of seeing if I can make this work).

Thanks in advance,

Marc

[Delerna]

I understand what you want to do its just not clear how you are are trying to do it and neither is it clear what is happening or not happening.

Here is what comes to my mind when I read your post.
When you use a combo box like that it often happens that its contents don't automaticaly refresh as you make changes on the form.
I think you might need to find a way to force the combobox to do a requery via VBA, each time you make a change to the record.

If you can provide a bit more detail on how you have constructed the form and the combo box.

[Delerna]

I just threw something together according to what I think you might be doing.

I have a form of view "single form" bound to the schools table
I have a combo box on it selecting from the regions table and saving the ID into the school tables region field.
I have another combo box on it selecting from the networks table with a where clause like you have, and saving the ID into the school tables network field.

Now the bit to make the network combobox requery.

In the onchange event for the "regions" combo put this code

    theNetworkCombo.requery
 

Hope that helps you

[mjvm]

Firstly Delerna - huge apologies for not replying sooner. I appreciate that you took time to answer my question, and then I did not reply to you. I started to reply but other things got in the way of my trying to make sense of your answer.

SO I have finally come back and had another go.

My form has fields named: School, Region and Network. The region field is a combo box that looks up the tblRegions table; the network field has a combo box that looks up the tblNetworks table which also holds the region for each network.

The form is called frmSchools. It is based on tblSchools, and the only relationships are the lookup tables for the comboboxes.

In the Rowsource for Networks I have typed the following (as per my original post) in the hope that when I select a region, it will filter the networks to only those that match the region.

SELECT [tblNetworks].Network FROM tblNetworks WHERE tblNetworks.Region=Forms![FrmSchools]![Region];

However, when I then go into formview, there is nothing in the drop down box.

I also put the following code into the Region On Change even as per your advice:

Private Sub Region_Change()

Network.Requery

End Sub

Initially I pasted your code in literally, and got runtime error, my field is called Network, so I changed it to the above. I think that code is okay, but I haven't solved my problem because of what I have written in the rowsource for Network.

Does that provide you with better information to understand how I have set things up? I am a self taught Access user, and don't have my head around expressions and code very well at all.

Thanks for your help,

Marcella

[Delerna]

Yep my mockup of what you have seems to match your design and It works for me.

So
1) Is the network.requery getting executed when you make a change in the region dropdown?
Put a breakpoint on that line and then run the form and make a change.
The code window should open with execution paused at that line.
2) Is the name of the network dropdown the same as the field it is bound to?
If so change the dropdowns name to cboNetwork and then your code to cboNetwork.Requery

or

Is the dopdown's name something like combo2 ?
If so give it a meaningfull name (cboNetwork perhaps)
and rename it in the code also



NOTE
Always remember to use breakpoints when trying to find out why a piece of code isn't working.
You can hover the mouse over variables to examine its value
watch the flow of your loops and your if conditions
You can even drag the exection point to a different part of you program when the code stops at a breakpoint.
All of this can be done during the execution phase of your form because of this great debugging tool.

It is perhaps the most powerful tool in your arsenal !

[mjvm]

Delerna,

I can't see where it isn't working. I put the break point in, but am not sure of what it is telling me.

My names all appear to be matching, and I have changed the region and network to cboRegion and cboNetwork to no avail.

Can I send or attach the database for you to look at?

Marcella

[Delerna]

If you set a breakpoint the background color of that line turns maroon (the default color)
If you then run the form .
If after making a change to the region dropdown the code window pops up
with your break point now being yellow instead of maroon, yellow being the default color for the next line to be executed when you press F5.

That means you now know one thing that you didn't know before.
The change event for the region dropdown is getting called correctly.
If it didn't pop up the code window with execution paused at the breakpoint (yellow line) then you would know that the event isn't even getting called.
Knowing this narrows down where the problem is.
If it does the breakpoint then the problem is likely to be in the code for the change event
If it dosn't do the breakpoint then the problem is likeley to be on the form itself.
The control name not being the same as your code function name for example.


Since it appears that the breakpoint did occur then you need to look more closely at the code within the change event subroutine.
Hover your mouse over each part of the code when the breakpoint occurs and see what tips come up. Is the control name suggesting that you are referencing a control that dosn't exist. etc etc.

You are self taught.....so am I.
If you really want to progress your learning with access, I can tell you that it would really be beneficial for you, not to mention extremely satisfying, if you can track the problem down using debugging methods.
I can confirm to you, what you are trying to do is definitely possible and from what I can see you are on the right track and are very close.


Having said all of that I have attached my mock up database for you to check out.......if you wish.

[mjvm]

Oh I agree - I am so happy when I figure it out! But I don't have any background that helps with code or expressions!

When I do the breakpoint, and hover the cursor, nothing happens. Does this mean that the code is okay?

A difference between your database and mine, is that i have set the region and network fields as lookup at the table level, and then put the Where condition in at form level. Could this be making a difference? You have them linked by the number fields, then pull up the text information. I have now gone and changed all this, so that there is no relationship at table level, and in the form the field becomes a combo box drawing on the tables for the drop down list. The fields at form level are called cboRegion and cboNetwork.

In Row Source for cboNetwork, I have:
SELECT tblNetworks.Network FROM tblNetworks; - this works- the form gives me a list of networks. But when I try to filter, it then draws a blank. SO my problem lies in the Where condition that I put in.
The WHERE is: WHERE tblNetworks.Region=Forms![FrmSchools]![cboRegion]

I hope I am getting closer, but this is starting to do my head in!

Any thoughts?

[John c]

Hi MJVM

I've been successful using the following:

I’m assuming your region is selected from a combo, thus:

Select the region and then let the Region AfterUdate do the work.

Private Sub RegionCombo_AfterUpdate()
Me!NetworkCombo.RowSource=”SELECT [tblNetworks].[Network] FROM tblNetworks WHERE tblNetworks.Region=Me!RegionCombo.Column(1);”

If not then the same applies but from the RegionField_AfterUpdate() and change the where clause to Me!RegionField

[Delerna]

yes I think that
Forms![FrmSchools]![cboRegion]

would return the region name instead of the region ID
so
WHERE tblNetworks.Region=Forms![FrmSchools]![cboRegion]
is trying to match an ID against a name and finds no match
and thats why the list is blank

a simple
msgbox Forms![FrmSchools]![cboRegion]
in the change event would confirm that

or better yet
a break point and hover the mouse over
cboRegion in Forms![FrmSchools]![cboRegion]
and after a couple of seconds its value should get displayed.

When you hover the mouse over something and nothing pops up
That means that the debugger can't find any info to display for you
for the thing you are hovering over. It certainly does not mean that there
is no error.

Do some reading in the Access help files.
Search for debugging

[NeoPa]

I know I'm late to the party but I just followed a link here from another thread.

Anyway, check out Debugging in VBA.

[mjvm]

TO Delerna, NeoPa and JohnC

I have finally got back to working on this problem - just blame the three jobs and the three children! and that this issue was not urgent for any of them.

I started a clean database. I had my main form store the ID numbers for the region and network so my lookups were clearer, the names of the region and network appear in the form, with the networks available for selection filtered! The requery code works perfectly too.

Thank you for all of your advice and your help. This problem is now finally solved!

Regards,
Marcella

[NeoPa]

Thank you Marcella. It's always nice when a poster let's us know they are happy and have managed to resolve the issue (regardless of how helpful we proved in the end).

Anyway, good luck with the three jobs, and good luck with the three children. Sounds like you have plenty on your plate.

[mjvm]

@NeoPa
Aah, but NeoPa, I wouldn't have got there in the end without the help of the forum. I have definitely looked at your link on Debugging - I'm sure I will come back to it at some point in the future.

SO thanks everyone!

[NeoPa]

If I'm honest I must admit I see that as the most enabling of all my articles. It's like teaching the proverbial tribesman to fish. With the understanding that you have that facility available to you, you will always be able to make more progress before posting a problem, as well as post more specific details.

I'm very pleased it's been of interest :)