ROW Source question

[VTXscooter]

I have a form with several drop dpwn lists in it. I want the choice of each on to narrow the choices of the next drop down list.

I have see a few examples on this site on how this is done but at this point I dont understand how to write the information. I have taken what another person had posted and dont really get what to do next. Do I paste the info in the rowsource box and then build the query? The example I saw had expr1 and expr2 in the querys so I was lost.
Thanks, Joe

1st drop down list row source
SELECT DISTINCTROW tblProjects.ProjectID, tblProject.Poject FROM tblProjects ORDER BY tblProjects.Project;

2nd drop down list row source
SELECT DISTINCTROW tblLocations.LocationID, tblLocations.Location FROM tblLocations WHERE (((tblLocations.ProjectID)=[Forms]![frmMain]![cboProjects])) ORDER BY tblLocations.Location;

 

[mcadle]

Yes,

Make sure that table/query is selected then in the row source put the SQL.

 

[VTXscooter]

The SQL is the part I dont understand.

In the from I have a project drop down field and then a location field. When I choose one of two projects I only want the locations for that project to show up in the second drop down field
I have a location table with all of the locations in it plus a project column.

I have been back and forth today creating stand alone querys in the database for the location table but when I use that query and choose a project I get all locations?
Thanks, Joe

 

[VTXscooter]

I have made allot of progress on this reading the forums and so examples.

The problem I am having now is my first and second drop down boxes have a number in them instead of descriptions. My second drop down box has only choices 1-6 when in fact there should be 12 choices.


My Project drop down box SQL
SELECT TblProject.ID, TblProject.Project
FROM TblProject;

My Location drop down box SQL
SELECT TblLocation.ID, TblLocation.Location, TblLocation.Project
FROM TblProject INNER JOIN TblLocation ON TblProject.ID = TblLocation.Project
WHERE (((TblLocation.Project)=[Forms]![FrmReviewRecommendations]![Project]));

There are two tables that show in the top half of this one
TblProject
*
ID
Project



TblLocation
*
ID
Project
Location

Under the third category field is Project, Table TblLocation, Criteria [Forms]![FrmReviewRecommendations]![Project]

Any thoughts?
Thanks, Joe

 

[mcadle]

Here is a sample database I just made with your nomencalture. Sorry I just saw your third post. Anyway this works. Make sure that the bound column porperty is set to the column that has the data that you are trying to match. Take a look it should answer your q's.

 

[mcadle]

Ok reviewed what you wrote:

1. To show the description instead of numbers change the order in your SQL statement. ex.

2. When you do this, in that combo boxes properties make sure you set the bound column property to 2 if the id is in the 2nd position. Meaning, the order that you put your SQL in as stated above.

SELECT DISTINCTROW tblLocations.Location, tblLocations.LocationID FROM...

You can do the inner joins, however, probably unnecessary depending on what you are trying to do. Remember, the control source can be set to set the IDS.

Look at your table structure to, check out how mine are setup.

 

[VTXscooter]

Mark, THANK YOU.....
That made a huge difference!

I treally appreciate it!
Joe

Why is is when I open my form the next time it has data in it and wont allow me to get back to my defaults? I want this data stored in a particular table.

Also would I follow the same steps for the next teo drop down lists to have them narrow down choices?
Joe

 

[VTXscooter]

I think it is working well now with all of your sugesstions.

One thing I noticed was if I make a choice in the first drop down it allows me the correct choices in the second.

If I go back to the first as if I had made the wrong choice it then keeps the second from changing to the correct data if that makes sense.
Thanks, Joe

 

[mcadle]

I think everything is working for you from your last post. If it is not please clarify. If it is, GREAT, I am glad I was able to assist. If you have any more questions, please post them. There are many talented people on these forums.

Good Luck!

 

[VTXscooter]

Mark,
I actually still have a problem.
When I make a choice in the first drop down and then a choice in the second drop down, If I go back to the first drop down and make a change it wont let me choose the correct data in the second. I will attach the file.
Thanks, Joe

I would also like to have the third drop down make choices based upon the second drop down from the feature type table. Do I do it the same way in the third drop down as I did in the second?
Thanks

 

[mcadle]

Right you would. I will look at your file on friday. I have travelled back to visit my folks and the computer they have doesn't have access. From what you are saying, it would appear that access is committing the first choice to that field, so on update the original value is still locked in. So when you go to the next combo, it doesn't see the new value. I will look at your file asap. I would try in each of the combo boxes after update property -- docmd.save It might commit the new value, but then again it might not do anything.

 

[VTXscooter]

Thank You.

 

[mcadle]

VTX sorry for the delay. I am actually down in Texas instead of AZ at the moment. My brother-in-law and I looked at it and figured out the issue. For each combo box that you have put this in the afterupdate property:

docmd.save
me.refresh

This will change the other data without issues. I looked at your file. One thing that I would do for the end-user is change the column count to 3 for the project cmbo and set the column widths to 0";0";1.6" that way the end-user sees the names instead of numbers. I would do this for all of the cmbo's Other than that stuff it looked all right. Hopefully this isn't coming to you too late.

 

[Rich]

The correct method is
In TheAfterUpdate event
DoCmd.Runcommand acCmdSaveRecord
Me.Combo2.Requery

in the AfterUpdate of Combo2 it's
Me.Combo3.Requery


Me.Refresh will Refresh the whole form, something you might not want to do

 

[mcadle]

Hey Rich why don't you take a look at his file and see what you can do.

 

[VTXscooter]

Thanks, I got it fixed with SQL
Joe

Project drop down
SELECT TblProject.ID, TblProject.Project FROM TblProject;

Location drop down
SELECT TblLocation.Location, TblLocation.Project FROM TblLocation WHERE (((TblLocation.Project)=Forms!FrmReviewRecommendations!Project));

And I third one.
Feature Name
SELECT TblFeatureName.FeatureName, TblFeatureName.Project FROM TblFeatureName WHERE (((TblFeatureName.Project)=Forms!FrmReviewRecommendations!Project));