query result as button label

[Guest]

Is it possible to have the result of a query as a caption on a buttion.
Ie. I have a query which returns a numeric value and would like this number
presented on the button

Thanks in advance

 

[Guest]

On the load event of the form (assuming that you want it there) write the code
Me.ButtonName.Caption = dlookup("FieldName","QueryName")

 

[Guest]

Thanks Ofer ... I perhaps didnt explain right ... I already have code for
adding up rows in queries which provides the numbers I want ... Im looking
for a way to put this number into the label

Ie A sum of all employees is returned at the moment to a text box ... I am
looking for a way of puting this figure on a button so it highlights issues
and when it is pressed the query information is displayed ... not just the
count ??

Many thanks again

 

[Douglas J. Steele]

That's exactly what Ofer gave you: code to set the caption of a button to
the value of a field in a query.

There are only 2 ways to programmatically get values from a query: open a
recordset and retrieve the fields, or use DLookup.

 

[Guest]

many thanks to all
now works as expected

Thanks agai n

 

[IT-1957]

Hello, I know this is a past topic, but is there a way to set the captioning of
Me.ButtonName.Caption to a specific record?

For example:

Me.Command1.Caption = ---record1
Me.Command2.Caption = ---record2
Me.Command3.Caption = ---record3

etc...

Thank you.

 

[John W. Vinson]

Hello, I know this is a past topic, but is there a way to set the captioning of
Me.ButtonName.Caption to a specific record?

For example:

Me.Command1.Caption = ---record1
Me.Command2.Caption = ---record2
Me.Command3.Caption = ---record3

etc...

Thank you.

Since there's only one button on a continuous form - repeated many times - and
since Access doesn't have record numbers, No.

Please explain what you're trying to *accomplish*.

 

[IT-1957]

I'm creating a little POS, I'm trying to create a form with let's say 20
command buttons, this will represent the 20 most sold products, (this will
come from a query) each product has its own UPC code, the most sold will be
first and so on, so the buttons on the form will be like this:

Command1= The first record on this query, wth the UPC or DESCRIPTION od the
product as it Caption.

Command2= The second record on this query, wth the UPC or DESCRIPTION od the
product as it Caption.

and so on, untill the 20th

By clcicking each button the product will be added to the Order screen (I
can do this)

I need help with the Captioning of each command button.

Thank you.

 

[John W. Vinson]

I'm creating a little POS, I'm trying to create a form with let's say 20
command buttons, this will represent the 20 most sold products, (this will
come from a query) each product has its own UPC code, the most sold will be
first and so on, so the buttons on the form will be like this:

Command1= The first record on this query, wth the UPC or DESCRIPTION od the
product as it Caption.

Command2= The second record on this query, wth the UPC or DESCRIPTION od the
product as it Caption.

and so on, untill the 20th

If you REALLY REALLY need the Command Button interface then you'll just need
to create twenty command buttons... but might I suggest a Listbox as an
alternative? If you base it on a query showing the Description, with the UPC
as the bound column, you can do this with very little or no code.

With command buttons you'll need VBA code (in the form's Load event probably)
to open a recordset based on the query, loop through it, and set the Caption
property of each of 20 command buttons (named cmdProduct1, cmdProduct2 and so
on). Timeconsuming, and it would prevent you from using the preferred MDE
format for the frontend since you can't (IIRC) make form design changes in a
MDE.

 

[IT-1957]

I really wanted to use the command buttons just becouse it looks more
profesional, I been working with a form, on which I have created 20 command
bottons, Button0,Botton1,Button3 and so on...
I have a query with 20 records, with 2 columns on the query, UPC and
DESCRIPTION
I wanted to name the buttons using record 1 value on the UPC column as the
description of Button0...and so on.
Thanks to other members of this forum I have this code :

Private Sub Form_Load()
Dim rs As DAO.Recordset 'Requires reference to DAO 3.6
Dim i As Integer
Set rs = CurrentDb.OpenRecordset("myquery", dbOpenSnapshot)
i = 0
While Not rs.EOF
Me.Controls("Button" & i).Caption = rs!Caption
i = i + 1
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub

But I got an error on the "Me.Controls("Button" & i).Caption = rs!Caption"

This code suppose to look up the value and set the caption of the button but
is not working,

Any help?

 

[John W. Vinson]

I really wanted to use the command buttons just becouse it looks more
profesional, I been working with a form, on which I have created 20 command
bottons, Button0,Botton1,Button3 and so on...
I have a query with 20 records, with 2 columns on the query, UPC and
DESCRIPTION
I wanted to name the buttons using record 1 value on the UPC column as the
description of Button0...and so on.
Thanks to other members of this forum I have this code :

Private Sub Form_Load()
Dim rs As DAO.Recordset 'Requires reference to DAO 3.6
Dim i As Integer
Set rs = CurrentDb.OpenRecordset("myquery", dbOpenSnapshot)
i = 0
While Not rs.EOF
Me.Controls("Button" & i).Caption = rs!Caption
i = i + 1
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub

But I got an error on the "Me.Controls("Button" & i).Caption = rs!Caption"

This code suppose to look up the value and set the caption of the button but
is not working,

As noted in the other newsgroup (hint: it's considered impolite to ask the
same question separately in multiple groups, it wastes the volunteers' time),
is there a field in myquery named Caption? Your code assumes that there is.
Instead of rs!Caption use whatever the actual fieldname is: rs!UPC.

 

[IT-1957]

Thank you, it worked.
Thank you again.
I'm sorry about that.