combo box choices specific to record

[jayme27]

I want to create a combo box within my form that is record specific. In other words, not everyone has the same list of choices to choose from in the combo box. It is based on each person's category. The combo box draws its data from the [Categories] table which is linked to the [Main Personnel] table. The link is through the field [category]. simple enough, but keep getting an error message.

1) I created the combo box [cmbAwardType]
2) Selecting the combobox properties button, event tab DOES NOT HAVE an "ON CURRENT" button for this field; however, the form properties button, event tab DOES.
3) In this "On Current" property box, it has the following:

Me.cmbAwardType.RowSource = "SELECT [Award Type] from Categories WHERE [Category] = '" & Me![Category] & "'"

(if the formatting doesn't come out right, it is double quote, single quote, double quote, double quote, single quote, double quote int he code above)

4) Also after trial and error, I then posted this statement in visual basic like this:

Sub Form_OnCurrent()
Me.cmbAwardType.RowSource = "SELECT [Award Type] from Categories WHERE [Category] = '" & Me![Category] & "'"
End Sub

5) It still was giving me the error message after that action too.

Is something wrong with the code? Just trying to explore all options here.

 

[jayme27]

More info.
I can get the fields from the combo box into the table where they are stored, but I need the combo box in the form to only display the appropriate list of choices for that record. In other words, sometimes not all people stored in the database will show every choice listed in the combo box--only suppose to show relevent choices restricting the database user to use what's given.
Maybe it is the relationship between the main table and the table in which my combo box draws it's values from? Hummmm

Right now, for every record, every possible choice is listed in the combo box. this shouldn't be the case.