Control sourced from another control will not update field in table

[AmishLou]

I am relatively new to Access. I have a database with one form and a few tables. In my personnel table, I have fields for a person's Grade and a person's Rank. These are different ways of saying the same thing, but required in the table. Examples of grade and rank are grade (O-3), and rank (Captain). I have a Grade table with GradeID, Grade, and Rank fields with all the possible grades and ranks. On my form, I currently have combobox controls for Grade and Rank. Since a specific grade will always match a specific rank in my case, I want the form to work so when you select the Grade from the drop down list, the Rank control will either auto-fill or default to the associated Rank. I have managed to get this to work in a number of ways, but in every case the Personnel table's Rank field will not be updated or filled in with the data from the Rank control on my Personnel form, it remains blank. I have tried a few different things on the Event tab of my Rank control, trying to have cbo3 = [Rank] (using the expression builder to select the Rank control and the Rank field on my Personnel table) but I must be doing something wrong. I tried entering that same expression in more than one Event, entering data in a form, saving, moving to a new record, closing the form, then going to the Personnel table and refreshing, all to no avail. I do not really understand code or VBA just yet, but can anyone help me or suggest a solution to this problem?


The Rank control does not have to be on the form at all, if there was a way to have the Grade control work with the GradeID to then enter the correct Rank into the Rank field on my Personnel table.


Thanks.

[maximus]

I have tried replicating your problem and I will give u a simple example here:

I have two tables:

Table: tblmaster {ID PK; Employeename;Grade;Rank;Comments}
Table: tblgrade {GradeID;Grade;Rank}

Grade and rank fields are lookup Fields with the RecordSource Table:tblmaster

Now I make a form (Form1) using tblmaster as the Recordsource.

I enter this Expression in the criteria for GroupIID of the RecordSource query of RanK:
Forms!Form1!Grade

Now I enter this simple code in the AfterUpdate Event of Grade Combobox:

Private Sub Grade_AfterUpdate()
Me.Rank.Requery
Me.Rank = Me.Rank.ItemData(0)
End Sub

What happens:
When you select a Grade in the Grade Combobox the subsequent Rank for the Grade displayed in the Rank Combobox.


I am attaching an example:

download the sample
click new record on navigation bar of the startup form
select a grade and see the rank apper in the rank combobox

[RuralGuy]

I would be *very* reluctant to use Lookup Fields: http://www.mvps.org/access/lookupfields.htm

[DNRTech]

Hi, everyone! This solution is very close to what I need to use to solve my problem. I also need to populate controls on a form based on an entry in one control.

I was hoping Maximus that you could explain a few things about your solution so That I might port it to my database as well.

What does the Criteria code do for the first column of the Record source query of Rank. Does it pull the value of GradeID based on the value of Grade from the form? Not sure so I thought to ask. Also, what is the function of the second column in that query? It appears to be both hidden and unbound?

I was also hoping that you might explain what the vb script does and how it is composed. I see a lot of code using those expression themes but rarely any explanation of what the expressions mean or do.

Private Sub Grade_AfterUpdate()
Me.Rank.Requery
Me.Rank = Me.Rank.ItemData(0)
End Sub

In understand that the first line applies to the event type, although the "Private Sub" portion eludes me.

In line 2 the "Me.Rank.Requery" is curious. Does the "Me." portion refer to the current entry in the control? while "Rank." is which control we find the current entry? What does "Requery" in that statement do?

Also I understand that in line 3 you are defining the value of "Me.Rank"?, but I'm not sure what "ItemData(0)" is doing.

I know there are a lot of questions here. Thanks for your help!

[DNRTech]

In a day of messing with the sample database and my own I answered many of my own questions.

The criteria code placed under GradeID for the RowSource query for rank calls the row that is queried in the Grade control on the form. It seems that the reason there are unbound and hidden columns in both RowSource queries for grade and rank is so that each of the controls have identical queries that include information needed in both RowSources, but are bound by and are displaying different columns from the query. I guess this makes sense because Rank control value is dependent the entry in the Grade control.

I'm still not sure what the ins and outs of the vb code, but I was able to alter it enough to fit my needs. I needed three controls filled instead of only one. The three fields I need to fill we Identifier, Context, and RSType. It was as easy as repeating the code for each field name.

Private Sub Characteristic_AfterUpdate()

Me.Identifier.Requery
Me.Identifier = Me.Identifier.ItemData(0)

Me.Context.Requery
Me.Context = Me.Context.ItemData(0)

Me.RSFType.Requery
Me.RSFType = Me.RSFType.ItemData(0)

End Sub

In my database I am not using LookupFields and it worked fine. I just needed to make sure that the RowSource query was pulling records from the same table. I had trouble pulling records from different tables in the query but I bet it could work.

I should also mention that if one wants to embed the form they will need to edit query criteria and include the parent form/forms, i.e. "[Forms]![Parentform]![embeddedform]![Grade]"

Great solution, thanks again!

[maximus]

hello DNRTech good to see that you are willing to learn things on your own the hard way. Here is some more food for thought:

You will be able to relate to this as you have already gone throught the sample. The Table tblGrade has three columns GradeID Grade and Rank

Now in the form tblm I can do this:

Make Rank a text box

and add the Code to the AfterEvent of Grade Combo:
Me.Rank = Me.Grade.Column(2)

So I don't have to use another combo for rank

[DNRTech]

and add the Code to the AfterEvent of Grade Combo:
Me.Rank = Me.Grade.Column(2)

So, this will pull the third column from the Grade Query ( I understand that sometimes things are ordered starting with zero ) and place that value into the text field. Good to know this is another option!