AfterUpdate...I Think...

[access_viper]

Hello.

Here is my situation:

1 Table Employees

Columns:
Name, Score, Recommendation1, Recommendation2, Recommendation3

1 Form

On the form, I have drop down box that enables the end-user to select a score (1,2,3). The recommendation fields in the Employees table are pre-populated with static text. When the end-user uses the form and select 1,2, or 3 from the drop down box, I would like only one recommendation to be left in the current record.

Simply, for Employee John Smith, the end-user (his manager) will use the form and select 3 which is linked to the column Score in Employees, the contents of Recommedation1 and Recommendation2 should be deleted. So when a report is printed for the Employees table, only one recommendation is in the table and will get printed.

I am trying to accomplish this with an "After Update" event linked to the drop down list unsuccessfully. Actually, nothing happens and I do not receive any errors or results. Any ideas or recommendations?


Thank you in advance for reading my post.

 

[dayna]

Hmmm, is there a reason why you're using three separate fields for Recommendations rather than one lookup field, like your [Score] combo?

 

[access_viper]

The recommendations fields can be rather long. I set their data type as memo. If I set a lookup list to be of type memo, can I then trigger a certain afterupdate event when the user changes the score?


Thanks.

 

[dayna]

I think I need to better understand how the recommendations are related to the scores.

It sounds like there is only one recommendation associated with each score. Is this correct? (That is, a score of 1 always gets recommendation X, while 2 always gets recommendation Y, etc.) If so, I would store the recommendation options in a single field (memo, if you like) on your Score Lookup table. Then you could delete the Recommendations fields from your Employee table and simply store the Score ID.

Since the recommendation is tied to the Score ID in your Score Lookup table, and your Score look up table is related to your Employee table, when a user updates the score for a record in the Employee table, the recommendation is automatically updated as well, without the need for any code.

Even if there is more than one possible recommendation available for each score, I would still create a separate lookup table to store the recommendations. Then your Employees table would only have three fields: [Employee Name], [Score ID], and [Recommendation ID]. (Hopefully you've got a primary key that you're not mentioning.) Your user could then select a recommendation from a combobox, just like the one it sounds like you're using for your Score field, with only one Recommendation value being saved for each record.

I'm no guru, but most of the ones I know absolutely wince at the idea of multiple fields in a table storing the same kind of information in and Excel-like format. (Mostly because it causes the kind of problems that you are trying to use code to solve.

 

[dayna]

Oooh, it just occurred to me that it may not be possible to index memo fields the way you can with other data types. Then again, it would be the primary key that is indexed, and not the memo field, so maybe this wouldn't be a problem.

Also, obviously, the combobox thing isn't going to work out with a memo field (duh), but maybe you create an additional indexed text field on your lookup table with an abbreviated description of the longer memo field for users to interact with. I've never used memo fields, to tell you the truth, so I'd have to experiment. Let me know how it works out.

 

[access_viper]

Thanks for the response. I would however like to know if it is possible to have VBA code modify a field based on a selection from a drop down list.

I will look into modifying the table design to have a drop down list for the recommendations.


Thanks.

 

[dayna]

See if this works:

Me.ControlName.Value = Null

 

[rolaaus]

Yes, you can make the value of a text base be filled in based on the selection of a combo-box, in the After_update event

Select case me.cbo_Combo_Name
case "First choice"
Me.fld_ControlName = "this is your first choice"
case "Second Choice"
me.fld_ControlName = "This is choice #2"
case else
me.fld_ControlName = "Something else was selected"
end select

Thanks for the response. I would however like to know if it is possible to have VBA code modify a field based on a selection from a drop down list.

I will look into modifying the table design to have a drop down list for the recommendations.


Thanks.

 

[access_viper]

Select case me.cbo_Combo_Name
case "First choice"
Me.fld_ControlName = "this is your first choice"
case "Second Choice"
me.fld_ControlName = "This is choice #2"
case else
me.fld_ControlName = "Something else was selected"
end select

Hello.

My select case is not working properly. The code compiles fine, however after updating the value of the combo box, the recommendation field is not updated/auto-populated. Here is the code that I have (it is in a sub-form that is linked to a table):

Private Sub Score_AfterUpdate()
Select Case Me.Score
    Case 5
Me.Recommendation = "You are doing an excellent job!"
End Select

Am I doing something wrong?

Thanks.

 

[rolaaus]

If your combo box has multiple columns then you may be seeing "5" but the value in the combobox could be different.

Try placing a breakpoint on the Select Case line of code (pressing F9) then go into the Immediate window and do ? Me.Score to see what the actual value is.