Hi everyone. Having a good day with this program - getting things to work the way I need! Yeah!

So I have a more complicated question - I need to create 2 drop down lists for my form of personnel actions and then reasons. (For example: Action/Data Change Reason/Correction - Department)

Typically I would just create the 2 tables and be done. But in this case depending on what Action is selected - there are only certain Reasons that can then be selected. I want to have the second drop down list to show the correct list of Reasons after the user selects the Action

How would I go about getting this done? Thanks.

the answer will depend on the table structure that you have. So, in theory, if you have a table that has the 2 columns of data or a relationship that serves the same purpose. Once the first list has a choice for action then the 2nd list would have a recordsource similar to "select reason from mytable where action = " & list1

This example is just for idea purposes and is not syntax correct

The tables have not yet bee created. So would I create one table and then be able to control what is showing in the 2nd list based on what the user selected from the first?

In a single table I would then have multiple records reflecting a single Action but then the reasons would all be different. (For example: I have 26 reasons for the Action: Position Change)

yes. so, it might look like this?? obviously, call the table whatever works for you as this is just an example. I would also stay away from special characters in the field names

table called tblreason

actiondata reasoncorrection
add person
add department
change person
change department



The first list would be a recordsource like "select distinct actiondata from tblreason" Then in the on-click section of that list you could do.

list2.rowsource = "select resoncorrection from tblreason where actiondata = " & listone

The first list would be a recordsource like "select distinct actiondata from tblreason" Then in the on-click section of that list you could do.

list2.rowsource = "select resoncorrection from tblreason where actiondata = " & listone
[/quote]


Hi Bob,

I am new to this - can you clarify this last little bit? I understand the On-Click - its in the property sheet - but then I am not sure what to type where. I clicked the ... and get the options of what builder to choose.

Also - By creating a single table - I then have 14 "Data Change" to select from in my drop down table. I would rather have just one of each type of action.

It sounds like you dont have the word distinct in the recordsource statement like my example. Please check and see if that fixes the 15 times.

you want to use the code builder.

it would look something like this.

private sub listbox1_on_click()

list2.rowsource = "select resoncorrection from tblreason where actiondata = " & listone

end sub

I'm sorry - I still don't understand. I am looking in the property sheet, I have the drop down list field selected and can not find "RecordSource". I see Control Source - is that the same thing?

would it be possible for you to attach your db??

What type of field are you using?/ listbox? combobox? textbox?

Hi Bob,

I have attached a sample of the database. I believe the drop down is a Combo Box for the Personnel Action. The data in the table does not reflect the new data my HR manager wants me to use (reflecting the different actions along with reasons) I have not yet created the Reason drop down - since I am not sure how to set it up yet.

Thanks for your time.

(And I know about the naming issue on tables/fields - I moving this db from Approach)

no attachment yet

Sorry. Can you see it now?

Click to view attachment

yes. i have it. must have been a timing issue as I now see it twice.

Give me sometime to look at it and see what i can come up with

ok. first part. when you are in the property sheet of the personnel_action combo box. Select the DATA tab, it will make things easier to view as it is a subset. the 2nd item is rowsource. Insert the word distinct .

should look like this.

SELECT DISTINCT [tblPersonnel_Action].[ActionID], [tblPersonnel_Action].[Action_Name] FROM tblPersonnel_Action ORDER BY [Action_Name];


2nd part...

When you create the other data,

you have to remember that your personnel_action combo box has 2 columns (0 and 1) So when you get ready for the code to populate the rowsource of the new drop down it would be..

personnel_action.column(1)

once you have the other data we can work on it

Hi -

I entered Distinct after Select in the Row Source/Data Tab - but the drop down still reflects duplicate actions. I then tried to create another drop down (its actually a List Box - I was wrong) and can't figure out how to show the reasons. I was going to create a duplicate of the first drop down and add the .column (2) notes but it still only shows the action types.
I made sure to include the .column (2) and I made sure the Column Count/Format Tab was 4.

Does it matter since it is a List Box - not a Combo Box on how to get this done?

Thanks.

can you copy and paste your rowsource here so we can take a look?

Good Morning,

Here is my Row Source

SELECT DISTINCT [tblPersonnel_Action].[ActionID], [tblPersonnel_Action].[ActionName] FROM tblPersonnel_Action ORDER BY [ActionName];

i am guessing that the table you are using is different from the one in the db that you uploaded?

My fault completely - I had stripped all the sensitive data previously and thought that I had everything on that stripped down sample. Will do again. So sorry.

Well - I have tried to clear out all data and then comppress the file and it is a little too big. Don't know how else to trim it down so I can upload. At a loss how to proceed now.

open a new blank database.

import your table into it.

then upload that.

I think this worked....hopefully. Thanks



Click to view attachment

the reason the DISTINCT didn't work is that you have the ID field in your select statement. If the listbox is just going to be the action then it could look like this which provides just 1 of everything.

SELECT DISTINCT tblpersonnel_action.[ActionName] FROM tblpersonnel_action ORDER BY [ActionName];

Hi.

So I corrected the Row Source - and when I ran the Query Builder - it did in fact pull one of each of the Personnel Actions. Yeah!
But when I go into Form View - my drop down is now completely blank.
Any ideas?

check the bound column and the number of columns in the properties

I set the column count to 4 and checked the bound column. I tried the bound column at 0 and at 1 and neither option reflected the list in the form view.

4 ?? can you post the rowsource you are using on the listbox.

check your column width properties as well. There might be an 0"; in there

OK - changed the column count back to 2. ( I had used 4 in a past list so was starting with what I knew)


RowSource is:
SELECT DISTINCT tblpersonnel_action.[ActionName] FROM tblpersonnel_action ORDER BY [ActionName];

based on this row source there is only one column. So, column count is 1, bound column is 1, and your column width should be something like 1.66" Have to adjust for font and stuff.

Changed both to reflect 1 and I now see one of each of the Actions listed. But when I go to the Form View and select an action - I get the following error:

The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger that the FieldSize setting permits.

What does that mean?

need to remove the controlsource and have this as an unbound control

The 2nd listbox should have the rowsource of..

SELECT tblPersonnel_Action.ActionReasonDescription FROM tblPersonnel_Action WHERE (((tblPersonnel_Action.ActionName)=[forms]![frmactiondetails]![personnelaction]));

and you would also need to have this. where list33 should be changed to the name of the 2nd listbox/combobox

Private Sub PersonnelAction_Click()
List33.Requery
End Sub

Ummm - when I make it an unbound control (I deleted the Control Source field in the Property Sheet) and then try to select an action in Form View - the new error is:

Microsoft Office Access can't find the object 'ActionReason'
If 'ActionReason' is a new macro or a macro group, make sure you have saved it and that your have typed its name correctly

ActionReason - is the name of the next field on this subform.. ???


And then in your notes:
where list33 should be changed to the name of the 2nd listbox/combobox

Private Sub PersonnelAction_Click()
List33.Requery
End Sub

The list33 (?)- where does that go and am I to be replacing it with something or is that how it needs to be entered?


P.S. - (Does everyone invest in a jumbo size bottle of headache medicine?!)

the actionreason error might be a mispelling of the table. Have to check that. On your form you have 2 dropdowns. The first one you choose the action then based on that you want to populate the 2nd dropdown with the appropriate descriptions for that choice. The name of the 2nd dropdown is what you would put instead of list33.
In theory, you would choose from a list in the first dropdown and it passes that value to a query that populates the 2nd query. Now lets say you would want to do that again. You would want to have the 2nd drop down requery itself to make sure it has the proper values.

You are very close to having this working. It works on my copy but I have trimmed it down a bit and don't want to post it back.

Headache medicine?

We have all been at the head banging stage before and future.

That is the wondeful thing about UA, someone is always around that can help based on knowledge and experience. They aren't just book smart people, they are all people who have built DBs and have run into issues before.

I must really be starting right at it.....and not seeing it
What or where is List33? OR Where does the private sub personnelaction_click().... go?

That's it...I can't see straight any more. This will have to wait until morning.....

Hope to talk to you then..... Thanks again for the help today. I know I am close to getting this to work!

Good Morning,

I was looking back through the posts to try and figure out why this is not working yet....came across the On Click note. I know you said you got your version working - I am wondering if/what you have this set for.
I did delete what I had in the on-click function in order to get my list to appear in the first field.
Would be great to get this working today....

i think the best thing to do is take a step back and see what works and what doesn't.

You have 2 listboxes on your form. The first one, when selected, would be the criteria for the 2nd.

Do both of those work?


Edit:

I have attached my copy of your DB. If you look at the form you will see what I have. I would NOT copy it into your DB as the control names might be different than yours and could cause issues. However, if you look at the code and rowsource you can modify yours accordingly.
If you use mine as its own you can see how the dropdown boxes work and we can go from there.

While in Form View my "Action List" correctly shows only one of each of the actions a user can select. It works when selected as well - no error messages appear. This list also works correctly in the main form.

My Action Reason List is not currently reflecting anything in the drop down list in the form - only reflects a blank line. In the main form when I click on the list I get an odd pop-up to enter a Parameter Value: Forms!frmActionDeatils!PersonnelAction (I believe that is somewhere in the query builder that I need to delete)

when it prompts for this..... Forms!frmActionDeatils!PersonnelAction is the spelling as displayed??? that would mean DETAILS is spelled wrong and just need to fix it in the rowsource of the 2nd dropdown.

No - I just can't type this morning. It is spelled correctly.

OK - I finnally figured out a PERIOD was missing and that is why I could not see the drop down list after I selected a value from the first drop down.

So - the final step - when I open the main form (where this one is a subform) I click on the Action - and it works. But I still get that odd pop-up when I click on the Reasons list. I don't want that pop-up - I looked in the query builders of the main form (since it does not happen on the sub form) but did not find anything.

Should I delete the sub form from the main - and re-drop it in to the main form since it is now working correctly?

Sorry to say that I do not use main/sub forms so I can not answer that question.

Hopefully, others are watching and will jump in.

In the main form - when the pop-up asks for the user to enter a parameter value - I typed in the value I just selected from the first drop down field and then the Reason drop down list correctly showed the options that should be available.


Understood - thanks so much for your help up to this point. I never would have figured this out without you!

are both dropdowns unbound ??

did you look at the sample I posted ??

Yep and Yep - that is how I found the missing period in my code.

and you are getting a prompt that says..... forms!whatsmyname!myfield ???


what is the exact prompt
what is the name of your first dropdown box

The pop-up exactly:

Enter Parameter Value
Forms!frmActionDetails!PersonnelAction

My first drop down box is named: PersonnelAction

And when I enter the parameter value (I type what option I just selected from the first drop down) I do get the correct options in drop down #2

i ran into that myself and worked it out in my copy. please check the properties of my 2nd dropdown to your version of the 2nd dropdown.

Almost there..

I looked at the main form on your copy and get the same results - when I click on the drop down for the second drop down list - I get the pop-up request. I don't get this pop-up request when I am only working with the subform - its only when I have the main form open. I created sub-form as a stand alone form then dropped it into the main.

Sorry to say this is where my knowledge ends. The 2 dropdowns work as coded. It is only when the subform is in the mainform that you have this issue. It has something to do with beging able to capture the 1st dropdown value as the criteria for the 2nd when the subform is "attached" the main form.

I will try and see what I can come up with.

Maybe someone else will chime in.

EUREKA !!!!

I think I have it !!!

on the 2nd dropdown rowsource. Remove the part of the line that has forms!formname

The last part of it should be something like this...

Action.ActionName=[1st dropdown name]


I tested it from the main form and it works