Full Version: Getting Caption Text On A Form From A Field, Within A Table
UtterAccess Forums > Microsoft® Access > Access Forms
jhayes
Is it possible to Get the Caption Text for a button on a Form from a Field, within a Table? Such as a form haing 10 buttons, each wtih Names for example Button1, Button2 and so on... Then a table for example as below:
uttonNo Button Text
1 T Shirt Blue 12.99
2 T Short Green 14.99
And show how for the button text (caption) to lookup the text in the table and for example button 1's caption on the displayed from to read "T Shirt Blue 12.99".
The reason for all this I want people to be able to adjust the description and price in a table and the form to change, rather than having to show then how to eneter the form design and change the caption.
Alan_G
Hi
You could use a simple DLookup() to do that in whatever form event suits your needs. For example
CODE
Me.Button1.Caption = DLookup("[Button Text]","TableName","ButtonNo = 1")

For multiple buttons, a loop would take care of it for you <
jhayes
Kool, where do I put this code? Sorry very thick! I guessing not within the Button Properties under caption (ie where I would normally type the text I would like to appear on the button)? You are from Devon, I'm Somerset, just up the road!
Alan_G
Yep, certainly not far away <
epends on your set up as to where the code would go. I'd imagine the On Load event procedure of the form would be a likely place. Don't forget to change TableName I've used in the example to the actual name of your table
jhayes
Sorry and yes have more than one button on the form, one form as 15 buttons, so how would I loop it?
Alan_G
Hi
If you call your command buttons cmd1, cmd2, cmd3 etc etc, then set the tag property of each button you want the caption setting for to the word Check (for any other command buttons you have on your form that don't need the caption changing, leave the Tag property blank), copy and paste the following into your forms code module
CODE
Private Sub sSetCaptions()
    Dim ctl As Control
    Dim intX As Integer
    For Each ctl In Me.Controls
        If ctl.ControlType = acCommandButton Then
            If ctl.Tag = "Check" Then
                intX = CInt(Mid(ctl.Name, 4))
                ctl.Caption = DLookup("ButtonText", "TableName", "ButtonNo = " & intX)
            End If
        End If
    Next ctl
End Sub
Private Sub Form_Load()
sSetCaptions
End Sub
jhayes
Hi
get a Run Time error, when opening the form at the "intX = CInt(Mid(ctl.Name, 4))" point, with Error 13 Type Mismatch.
Any idea's?
jhayes
Sorry don't know if it matters the field with contains the button number is text, can change to a numerical field if necessary.
Alan_G
Hi
It would make more sense to use a numerical datatype to store the button number as long as it's just going to be a number. Integer should be fine. I was assuming that the ButtonNo was a numerical one with this expression
DLookup("ButtonText", "TableName", "ButtonNo = " & intX)
so for my example to work 'as is' then the table field would need to be numeric.
That wouldn't cause the error at the line you indicated though. Have you named (not the Caption, the actual controls names) your command buttons cmd1, cmd2, cmd3...etc etc with no spaces ?
jhayes
I failed you on both accounts, yes was text rather than a number field and hadn't changed the Name. Now changed it "Works like a Dream", one final question! Have played around with it, it would be nice to have the Price (cost) on the button as well as the text, so changed you code from/to...
tl.Caption = DLookup("ButtonText", "SalesItems", "ButtonNo = " & intX)
ctl.Caption = DLookup("ButtonText", "SalesItems", "ButtonNo = " & intX) & " £" & DLookup("cost", "SalesItems", "ButtonNo = " & intX)
Which works, but because it is a currency field (cost) some of the prices look OK, eg £12.99 but other appear as "£12.5" for £12.50 and "£0.5" for 50p, anyway I can code it, so it adds the last zero on, eg so I get £0.50 rather than £.5?
cheekybuddha
Hi, you can probably do all you need with one pass of the DLookup()
tl.Caption = DLookup("ButtonText & ' £' & Format(cost, '0.00')", "salesItem", "ButtonNo= " & IntX)
The Format() function will output the cost correctly.
hth,
d
jhayes
Sorry doesn't work. I get an error in the code.
cheekybuddha
Hmm, strange ...
id you copy and paste exactly? The single / double quotes are important, and must be maintained as I posted.
Otried it on a similar table in one of my dbs and it worked.
If it doesn't work you can revert to 2 DLookup()'s
CODE
ctl.Caption = DLookup("ButtonText", "SalesItems", "ButtonNo = " & intX) & " £" & _
              Format(DLookup("cost", "SalesItems", "ButtonNo = " &  intX), "0.00")

(Note, this time you need double quotes in the Format() function)
hth,
d
jhayes
Really sorry, I must have mis-typed! Can I also ask.... I want to run a query when a button is click, but the button name be the query name, for example you guys have got the button number for me ie 10 from Ctr10, I now need to pass it as "docmd.openquery "Sellxx" the xx being 10, 11, 12 etc.
cheekybuddha
Hi, try:
!--c1-->
CODE
DoCmd.OpenQuery "Sell" & Mid(Me.ActiveControl.Name, 4)

hth,
d
jhayes
wow thanks between you both, Alan_G and cheekybuddha you have solved everything, well for now! Thanks again.
Alan_G
Hi
ou're very welcome, both David and I are happy to help and glad you got things working <
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.