Solved If combo box = ____, then autofill another combo box auto to = ______ (1 Viewer)

[dawsonrhodes]

Good afternoon/evening all!

I am back again, I am moving forward with my project wonderfully! I have run into another area that just makes the user experience easier and allows for more consistency in records.

I've attached a screenshot of what I am trying to do. In my form, I have a form that tracks training completion. In "name of training" there are several options. The second screenshot shows the possible selections, which are added through another form popup.

The "training overview" form has 2 dropdowns, which retrieve the data from the table I have taken a screenshot of, and list it through the dropdown. So if I select "first aid policy" in the "training overview" form, and move to "category", I am met with the following options: [Type, Sign-Off, CONNECT Training, etc.]

What I am looking to do is:

If "Name of Training" selected is "First Aid Policy", I want the "Category" to autofill to "Sign Off" as that is what it has been assigned to in the table.

I hope this makes sense, I very much appreciate your support!

 

[theDBguy]

Hi. Just trying to understand your form... If the user selects "First Aid Policy" from the "Name of Training" dropdown, you want to automatically assign "Sign Off" in the "Category" dropdown, correct? Let's say we managed to do that. Is the user allowed to change "Sign Off" to something else by manually selecting from the "Category" dropdown after selecting a name in the other dropdown?

 

[Micron]

I think you will need to use a DLookup on the After Update combo event because if you hard code values then change table values the code will be out of sync.
Me.Category = DLookup("tableNameHere", "TypeOfTraining", "ID=" & Me.[Name of Training])

 

[dawsonrhodes]

Hi. Just trying to understand your form... If the user selects "First Aid Policy" from the "Name of Training" dropdown, you want to automatically assign "Sign Off" in the "Category" dropdown, correct? Let's say we managed to do that. Is the user allowed to change "Sign Off" to something else by manually selecting from the "Category" dropdown after selecting a name in the other dropdown?

Exactly what I am looking to do! I am going to probably disable it from being clicked and edited, if that doesn't interfere with anything.

 

[dawsonrhodes]

I think you will need to use a DLookup on the After Update combo event because if you hard code values then change table values the code will be out of sync.
Me.Category = DLookup("tableNameHere", "TypeOfTraining", "ID=" & Me.[Name of Training])

Thanks for your reply!

No luck with
Me.Category = DLookup("tblTrainingSelections", "TypeOfTraining", "ID=" & Me.[Name of Training])

I tried adding it to the after update field on both name, and category

 

[theDBguy]

Exactly what I am looking to do! I am going to probably disable it from being clicked and edited, if that doesn't interfere with anything.

Okay, one more question then. This sounds like you are storing duplicate information between the table you have for the form and the table you have for the dropdown. If the user selects First Aid and you auto-assign Sign Off, but then later go to the dropdown table and change the category for First Aid to something else, would you need to go back to the form and make sure the Sign Off is changed to match the new category?

 

[dawsonrhodes]

Okay, one more question then. This sounds like you are storing duplicate information between the table you have for the form and the table you have for the dropdown. If the user selects First Aid and you auto-assign Sign Off, but then later go to the dropdown table and change the category for First Aid to something else, would you need to go back to the form and make sure the Sign Off is changed to match the new category?

I'm not quite understanding...

Basically the table I screenshotted is just the info table, where it gets stored is seperate, the options are just selected from the table I screenshotted.

The categories will never be changed as they are what they are, if the policy is a sign off it will never be changed to "in-class" for example, I would just create a new "training module" meaning the name, type, and description.

 

[theDBguy]

I'm not quite understanding...

Basically the table I screenshotted is just the info table, where it gets stored is seperate, the options are just selected from the table I screenshotted.

The categories will never be changed as they are what they are, if the policy is a sign off it will never be changed to "in-class" for example, I would just create a new "training module" meaning the name, type, and description.

Here's what I mean. Let's say your form is bound to Table1, your training dropdown is bound to Table2, and let's say your category dropdown is also bound to Table2 (although it may be bound to Table3 - you tell us). So first, I am wondering if you have a primary key in Table2 and if you're using it to store in Table1. Let's say you don't, and you store the actual training name and category in Table1. So, you could have something like this:

Table1
DateRecorded: Yesterday
TrainingName: First Aid
Category: Sign Off
DateRecorded: Today
TrainingName: First Aid
Category: Sign Off

Table2
TrainingName: First Aid
Category: Sign Off

So, in Table1, there are multiple records with Sign Off in them when that data is already in Table2. Since you said a category will "never" change, then it's even more practical not to save that information in Table1 - because you can always "look it up" from Table2.

I don't know if I am making sense. Please let us know if you're familiar with the Normalization Rules.

 

[dawsonrhodes]

Here's what I mean. Let's say your form is bound to Table1, your training dropdown is bound to Table2, and let's say your category dropdown is also bound to Table2 (although it may be bound to Table3 - you tell us). So first, I am wondering if you have a primary key in Table2 and if you're using it to store in Table1. Let's say you don't, and you store the actual training name and category in Table1. So, you could have something like this:

Table1
DateRecorded: Yesterday
TrainingName: First Aid
Category: Sign Off
DateRecorded: Today
TrainingName: First Aid
Category: Sign Off

Table2
TrainingName: First Aid
Category: Sign Off

So, in Table1, there are multiple records with Sign Off in them when that data is already in Table2. Since you said a category will "never" change, then it's even more practical not to save that information in Table1 - because you can always "look it up" from Table2.

I don't know if I am making sense. Please let us know if you're familiar with the Normalization Rules.

hello again,

I am not familiar with normalization rules.

I've attached 3 screenshots of each table.

Table 1 = Types (which will never change as things will not be added)
Table 2 = The record source for adding training types (typeoftraining reads from table 1's data)
Table 3 = the record source for the form, which simply just stores the data for each entry.

The dropdowns on the form are not sourced from table 3, they are "row source" table 2 - which is names and categories.

 

[theDBguy]

hello again,

I am not familiar with normalization rules.

I've attached 3 screenshots of each table.

Table 1 = Types (which will never change as things will not be added)
Table 2 = The record source for adding training types (typeoftraining reads from table 1's data)
Table 3 = the record source for the form, which simply just stores the data for each entry.

The dropdowns on the form are not sourced from table 3, they are "row source" table 2 - which is names and categories.

Ah, okay. First off, I would recommend storing the primary keys (ID) in the related tables. This allows you to change the "names" without having to go back and update the other related tables. So, for example, you could have something like this:

tblTrainingTypes

CategoryID    Category
1             Sign Off

tblTraining

TrainingID    TrainingName    CategoryID
1             First Aid       1

tblData

ID    DateRecorded    TrainingID
1     3/26/2020       1

This structure minimizes errors and enforce data integrity. So, if a training was comleted/recorded for First Aid, you can always find out its Category.

 

[theDBguy]

Oh, by the way, here's a primer for Normalization Rules. Cheers!

Normalization

This month, I decided to go back to basics. I know there are already plenty of articles regarding this subject. But since I am about to give...

thedbguy.blogspot.com

 

[dawsonrhodes]

Oh, by the way, here's a primer for Normalization Rules. Cheers!

Normalization

This month, I decided to go back to basics. I know there are already plenty of articles regarding this subject. But since I am about to give...

thedbguy.blogspot.com

Thanks!

I will read up and adjust accordingly.

Now just to figure out the code for the autofills....

 

[dawsonrhodes]

Automatically fill in Access text boxes based on a combo box selection | TechRepublic

You can greatly enhance a form by having Access supply information based on the user's selection. Here's an example that will fill in an employee's first name and phone number when the user chooses that person's last name from a list.

www.techrepublic.com

Might be what I am trying to do...

 

[theDBguy]

Automatically fill in Access text boxes based on a combo box selection | TechRepublic

You can greatly enhance a form by having Access supply information based on the user's selection. Here's an example that will fill in an employee's first name and phone number when the user chooses that person's last name from a list.

www.techrepublic.com

Might be what I am trying to do...

Hi. Maybe. But once you understand and follow normalization rules, you may also realize this code is not necessary. For example, take a look at the table I proposed for storing the completed training, you won't see the category in there. The table doesn't need it, but you can still display it on the form.

 

[Micron]

but is the bound column of this combo Me.[Name of Training] bound to the ID field? Didn't work doesn't help much.
I think you are in good hands with the DB Guy and I'm falling behind so I'll bow out.