How program multiples command button at once?

[Niroth]

Hi,

I have a form with many buttons, one for each room, and when I click on the button, it will open another form, go to a field (roomNumber) and enter the room number in that field. I got the code for the action down, but I'm stuck at how to program all the buttons on the form (the form contains only buttons, no other type of controls) to execute the code with just one function or sub statement. Can any one help? If I have to go into each button onClick property to do it, it will take too much time, and frankly I'm lazy.

Thanks.

 

[DCrake]

Can you post a smaple copy of what you are attempting to do either as an mdb or an image.

 

[ezfriend]

See the example attached for my lazy way of calling different forms from different button.

 

[ezfriend]

If you are too lazy to create the onlick event, I suggest you pay a visit to Mr. MS Excel to help you generate those click events

and then change the OpenLazyForm() in the MDB in previous post to something like below so that it can account for form number 100, 2000, etc....

Private Sub OpenLazyForm(ByVal btn As CommandButton)
    
    'take out the "cmdLazy" and only append the form number to the form name
    DoCmd.OpenForm "frm" & Right(btn.Name, Len(btn.Name) - 7)

End Sub

 

[Fifty2One]

Wouldn't it be easier to have a ComboBox with all the room numbers listed and a single button to open the form for the room number selected in the ComboBox?

Hi,

I have a form with many buttons, one for each room, and when I click on the button, it will open another form, go to a field (roomNumber) and enter the room number in that field. I got the code for the action down, but I'm stuck at how to program all the buttons on the form (the form contains only buttons, no other type of controls) to execute the code with just one function or sub statement. Can any one help? If I have to go into each button onClick property to do it, it will take too much time, and frankly I'm lazy.

Thanks.

 

[Niroth]

Well, the users have problems with combo box, and for visual reasons (the layout of the rooms) I want to use button or anything that they can click on to take them to the checkIn form and fill in the roomNumber.

My connection's too slow to upload a file, so here's a sample code of what I would have the function or sub do:

sub/function???

dim destField as control

set destField = forms!checkIn!roomNumber

dim cmdButton as control

set cmdButton = ... ???????????????? (here's what I'm unsure what to do, I tried Me.ActiveControl but it didn't work)

docmd.openform "checkIn"

select case

case 1 cmdButton.name = "r101"
destField.value = 101

case 2 cmdButton.name = "r102"
destField.value = 102

...

end select

end sub/function???

So what I would like to do is to deploy these codes to all the button on the form, about 60 of them, without going into each button's onClick property.


Hey, ezfriend, thanks for the sample db but it's kind of not what I'm looking for, how do I do what I want to do in Excel?

 

[DCrake]

I just don't see the point of this exercise! You are actually using more code this way than doing it direct from the OnClick Event. Also you cannot pass any conditional arguments as target forms may be different and have different controls.

Your methodology means that naming conventions are not strickly enforced as buttons must me named according to the form names or vice versa.

David

 

[Niroth]

I think you're mistaken about my example. The are only two forms involved. When I open the new form, I'll add a new record and fill in the roomNumber and do more procedures. If I do it through the onClick event, I have to do 60 onClick events for 60 buttons. I simply want to avoid having to do 60 onClick events, the code for the actions and the name of the buttons is not a problem.

 

[ezfriend]

Niroth,

You can use the example that I have above, but modify the code to set the room number based on the room number.

Private Sub OpenLazyForm(ByVal btn As CommandButton)
    
    'take out the "cmdLazy" and only append the form number to the form name
    DoCmd.OpenForm "frm" & Right(btn.Name, Len(btn.Name) - 7)
    Forms("frm" & Right(btn.Name, Len(btn.Name) - 7)).txtRoomNumber = Right(btn.Name, Len(btn.Name) - 7)


End Sub

Excel can help you generate a lot of code by appending cells together.
you can have the word

"cmdButton"

in row a1:a10 and 1 to 10 for b1:b10.

Using that, you can do something like

In column C1 type

="Private Sub " & A1 & B1 & "_Click()" & char(13) & "End Sub"

copy this formula for row c2:c10 and that click event is automatically create for you.

 

[Niroth]

Thanks, I think that's the only choice I have.

 

[DCrake]

Letting my normally logical mind wander for the moment and visualise what you are attempting to achieve, it has deduced that you have one main form with about 60 buttons on the form. Each of the buttons has a caption denoting the room number. Room 101, Room 102, etc.

And when any of the buttons is clicked it opens up a common form requesting information about the booking. And in the form header or somewhere else it says "Room 101" (if the user clicked on the button Room 101).

You may have also passed other information to the form but that is not relevant at the moment. Going back to the main form, you are saying that you do not want put any code behind the OnClick Events of the 60 or so buttons whilst designing your form. Each OnClick Event only needs 1 line of code

Call WhoAmI(Me.ActiveControl.Name)


Your Sub WhoAmI will decifer which button called the sub routine via the controls name.

Private Sub WhoAmI(RoomNumber As String)

Dim StrRoomNumber As String

StrRoomNumber = "Room " & Right(RoomNumber,3)

DoCmd.OpenForm "FrmReservations",,,,,StrRoomNumber

End Sub

So when the form opens is will use the Open Arguments to populate the caption of the label on your reservations form.

This should take about 5 mins to do. a bit repetative I admit but this is a once only event. And considering how much time you have spent researching this solution it could have been done 100 time over.

Should and changes be required as to the procedure calls then you only need to modify the Private Sub as everything else feeds of it.

David