Changing Content of Control (Control Filled from a Query)

[nick404]

I have a subform based on a query. It displays the top 3 results (a select top 3 union query) from multiple queries. The subform displays a Category and a Count. No problems here. The issue is that the current "category" content is not informative to people. The actual content is much longer in some cases and I didn't want to run the risk of running out of characters in the table(s), so it was shortened into a series of "checks". I would now like to display the full names for each category.

However it has been a while since I last used Access, and I cannot remember how to change the content of the textboxes. For example if the query returns "check1" for the Category I would like it to actually display "Case Sticker in Place / Correct" in the textbox.

So far I tried some vba along the lines of

If Me.Category.Value = "check 1" Then
Me.Category.Value = "Case Sticker in Place / Correct"



and a variation using Select Case statements. No luck.

Any ideas? Any missing information please ask.

[Micron]

Often, a query that provides records for a form cannot be updated due to table joins or aggregate functions in one or more queries, which in your case may be a good thing. Otherwise, you'd be editing the record when your post reads as if you want to display more information related to a record field.
Sounds like you need to go back to the query/queries that provide the records and adjust them to get the values you want, then ensure your form controls point to those query fields. If the values are not in the table, then you have a problem that can only be resolved by introducing hacks, such as having locked and unbound textboxes on a form wherein expressions in those controls (or vba) provide a value based on a related control value. If the form is a continuous form, then you may be out of luck as far as using that hack is concerned. Not sure why you'd be concerned about running out of table room. Have you researched table, field and database size limitations?

[nick404]

Thanks for the response micron.
Looks like I will have to edit the applicable queries. I was hoping there was an easier way since there is a lot of sql to sift through in the union. Oh well, thank you for the information!