miltiple Buttons relating to records

[Jako]

Well here is a tricky one for me. Basically what I want to achieve is
this: I'm creating this for a real estate, I have a "window-display"
with multiple properties in them, displaying either properties,
advertisements, profiles or something else. But I need to track what is
currently in the window display, for this to happen I thought of
creating a form with multiple buttons (8 * 5), on it to display the
different displays in the window, which should have as its caption
ether the property name, or whatever is in the display at that moment.
It should be able to change color whenever a property expires (that
will happen when the property is recorded as sold, withdrawn or no
longer listed, this is recorded in another form) or if it has been in
the same spot for too long (15 days) ext.

In other words you have to put into every button what is on the window
at that time all the relevant information needed, and it should notify
you when to change it or do something with it. Whenever something
changed color you will have to click on the button that changed color
and it should bring up the record or table information relating to that
separate button. I was thinking of doing this in 2 tabs, the first
where you select what kind of display it is (property, advertising,
profile, else with a option button) and the second tab is then to enter
the information relating to what your first answer was. e.g. property
from drop down list box which is referenced from another table, the
details about the advertising information, the date it was placed on
the display, when it should be changed or whatever is in the display
with a short caption that will ultimately be displayed as the button's
caption.

The problem however is that to do the coding for 40 buttons will be
disastrous, and I know there must be a simpler way to do this, for
instance some sort of array that links the button to the specific
record that just holds this information.

Any help on this issue will greatly appreciated. And if anyone can
think of a better way to approach this please feel free

 

[Rod Plastow]

Hi Jako,

Is it always 8 * 5 ? If so think in terms of two-dimensional arrays and
construct the control name according to its position. For example btn24 is
row 2 column 4. (Yes I know the standard prefix for a command button is cmd
but I prefer to reserve cmd for ADODB.Command.) Hence you can
predict/calculate the name of a control in a certain cell.

Most (all?) form controls have a tag property where you can store a foreign
key, converting to string format if necessary. This gives you your link to
other information in the database.

I would question your choice of command buttons. There's nothing wrong
except that you have fewer properties that can be altered for highlighting
purposes. The notable omission is that you cannot change backcolor; on the
other hand you can easily put a picture on command buttons. Have you
considered using text boxes instead? You can even cover the text boxes with
an enabled transparent command button if you disable and lock the text box.

Hope this gives you a few design toughts.

Regards,

Rod

 

[jacksonmacd]

In addition to the conventional Event Procedure that a control can
respond to, you are allowed to enter a Function() directly into the
click event of each control. In other words, you would enter something
like =MyButtonHandlerFunctionName() directly into the Click event of
the button's property sheet. Furthermore, the function can include
parameters, so you might have something like
=MyButtonHandlerFunctionName(2,5) in the appropriate button.

This consolidates all the programming into a single function. Your
only "per-button customization" would consist of entering the correct
function name and parameter values into each click event.

HTH

 

[Jako]

Thanks Jack and Rod. Your insight to my question was very good and I
appreciate it heaps. I did have a look at labels, but that came to an
end when I realized that you can't have an empty label, so I must say I
never did consider the text box idea, and it is quite good indeed.

In relation to the array, I understand the whole concept and I did
think of writing a public function, but the problem is that I just
can't see how I can link each individual button to each record?

I believe it will be a for i = 0 to 7 and then also for y = 0 to 5
statement or something similar, but how can I actually track each
record and keep matching it up with the appropriate button or text box?

I hate it if people ask this, because I personally think it is
arrogant, but do any of you guys think there is any example of coding
to complete something like this?

I know it's a lot of trouble and I must say I really appreciate all
the help you guys provided so far. Thank you

 

[Jako]

Thanks Jack and Rod. Your insight to my question was very good and I
appreciate it heaps. I did have a look at labels, but that came to an
end when I realized that you can't have an empty label, so I must say I
never did consider the text box idea, and it is quite good indeed.

In relation to the array, I understand the whole concept and I did
think of writing a public function, but the problem is that I just
can't see how I can link each individual button to each record?

I believe it will be a for i = 0 to 7 and then also for y = 0 to 5
statement or something similar, but how can I actually track each
record and keep matching it up with the appropriate button or text box?

I hate it if people ask this, because I personally think it is
arrogant, but do any of you guys think there is any example of coding
to complete something like this?

I know it's a lot of trouble and I must say I really appreciate all
the help you guys provided so far. Thank you

 

[Rod Plastow]

Hello again.

I'm just about to go home but before I do let me sketch out a function that
you can include in your form to see whether a certain key exists and is
linked to a text box or button. (BTW I forgot to mention that tags are
persistent, that is the value remains between sessions.)

Function Get_Text_Box(r_Key as long) as String

'This function returns the name of a text box related to the given key or
zero length string

dim intRow as integer
dim intCol as integer

Get_Text_Box = ""
for intRow = 1 to 8
for intCol = 1 to 5
if me.controls("txt" & cstr(intRow) & cstr(intCol)).tag = cstr(r_Key) then
Get_Text_Box = me.controls("txt" & cstr(intRow) & cstr(intCol)).Name
exit function
end if
next
next

End Function

You can tell I did not use the VBA editor for that - no time - so there may
be some syntax glitches. However a collection of functions like this will
provide all the functionality you need to interrogate your form controls.

Moreover it's not the only way of using the Controls collection. Doubtless
you will receive other suggestions.

I'll check again when I get home.

Regards,

Rod