Can I base button caption on a formula?

[Big Pat]

Hi,
I've got a database with a DCount on the opening screen showing the user how many "current" patients there are.

Alongside, I have a button marked "current patients", which opens a form showing these records. So the display looks something like:

25 [Current patients]

This works fine, but I think it would be more elegant to have the value on the button itself, so it looked like:

[ 25 current patients ] ... but obviously the value would need to change.

I've tried entering =DCount("[PatientID]","qryCurrentPatients")&" current patients" as the button caption, but it just displays that formula as text.

Is what I'm trying to do possible? I'm only vaguely familiar with coding, but is there something like Form.button.caption = DCount etc etc.?

The other possible approach is to put a transparent button over the both the DCount value and a label, but I'd really like to make it look and feel like a button.

 

[JohnLee]

Hi,

Yes it is possible to do, In my database I do the following in the form on load event:

========
Start Code
========

Dim TotalEmployees 'Declare the TotalEmployees variable

' Count the total number of employees in the tblEmployees table
TotalEmployees = DCount("*", "tblEmployees")

'Assign the value of the TotalEmployees variable to the command
'button cmdOpenfrmEmployeeUpdate Caption
Me![cmdOpenfrmEmployeeUpdate].Caption = TotalEmployees & " " & Me![cmdOpenfrmEmployeeUpdate].Caption

============
End Code
============

So what the above does is on opening up the form where the command button "cmdOpenfrmEmployeeUpdate" resides is to count the total number of records in my employee table and then assigns that value to the Caption property of that button.

You just replace my "tblEmpoyee" table with the required query.

and hey presto.

I hope this helps

John

 

[Big Pat]

Thank you !!

Brilliant. Worked first time!

OK second time really. I misunderstood your last line of code for a moment but could see where I had gone wrong.

That's going to be REALLY useful in some other projects too. Thank you so much.

Pat.

 

[JohnLee]

Hi Pat,

Glad I could be of help, Just noticed that you live in Nuneaton too

John

 

[Big Pat]

What do you mean "too"? Where are you then?

 

[JohnLee]

Well, I live in Attleborough, but I'm currently at work in Bedworth

 

[Big Pat]

Wow...small world.

I live on Attleborough Road and I'm working at the George Eliot Hospital

 

[JohnLee]

Cool, nice to meet you, I work at Dometic & General, certainly is a small world.

John

 

[Big Pat]

My wife worked there for a little while several years ago, just doing some data-entry etc. Just part-time a couple of days a week I think it was. She said there was such a turnover of staff (in that dept. anyway) that she'd been there about a month and she was one of the more senior ones.

Well, it's totally bizarre running into someone from round the corner online. Stay in touch and meanwhile have a great Christmas and New Year.

 

[JohnLee]

Oh right, Well still have a lot of parttime staff, but turnover of staff has improved significantly, Advent has just purchased us, so more changes to come at somepoint, as these things tend to do when takeovers happen.

If I can be of any further assistance with your DB, I will try to assist, but most others on here are more expert and you get lots of assistance, a great forum.

You have a good christmas and new year too.

John