VBA to Copy the value from the Table/field to a textbox on a form that is used as a visual 1

[tav1035]

I have a database that the tables are populated by odbc (external oracle database), via a push button running a refresh macro and a creating a few make tables, charts and reports.

I have a form called "Main_Form"
on that form I have a few textboxes that I'm using as a stoplight chart.
One of the Textbox is called "Color2" which has a conditional formatting set up so if the value is "G" the box turns Green. If it's "R" the box turns Red and so on....
I would like the Textbox "Color2" to copy the value from the Table called "METRICS_COMBINED" and Field called "Color" which the value will either be "R" or "Y" or "G".
In the table "METRICS_COMBINED" there is always only one row of data.

I need some VBA to grab that value and place it into the textbox after my other vba runs. I have tried it conventionally using the Form record source and the Data Control Source, but that way locks up my make tables with an error 3211 "database engine could not lock table 'METRICS_COMBINED' because it is already in use by another person or process.



Form called "Main_Form"
Textbox called "Color2"

Table called "METRICS_COMBINED"
Field called "Color"

Thanks
tav

 

[PHV]

Something like this ?
Forms!Main_Form!Color2 = DLookUp("Color", "METRICS_COMBINED")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tav1035]

PHV, Not sure where to add this, but I added it to the textbox Control Source and it returns #Name?
thanks
tav

 

[PHV]

You asked for VBA, so I suggested VBA code.
If you prefer a control source formula:
=DLookUp("Color","METRICS_COMBINED")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tav1035]

Ok, actually they both work great.
Thanks.
tav