Object name as variable

[Mark Cuthbertson]

Hello,

I need to use a button's name as a variable but I do not
know the syntax to do this (I am not a particularly
accomplished coder!).

Is it possible to assign the name of the button the user
has just clicked to a variable in the 'on click' property
of the button?

Thank you for any help you can offer,

Mark

 

[Allen Browne]

It is probably Form.ActiveControl or Screen.ActiveControl.

That's assuming the user actually clicked it, i.e. the code was not fired
as:
Call MyButton_Click

 

[Mark Cuthbertson]

Hi Allen,

Thank you for this. Can I therefore say:

Dim myVariable as String
myVariable = Form.ActiveControl

?

Thank you,

Mark

 

[Allen Browne]

Almost. To assign the name of the control to a string:
myVariable = Form.ActiveControl.Name

 

[Guest]

Allen,
First of all, thank you for all of the help you have given me in the past.

To expand on this point a bit further, is there an example database that
puts this into action?

I have recently created a monster database because I could not dynamically
create queries and forms.

(Simple example, but actual is more comlex)
My database has thousands records that have one of 4 values in field1.
I created a switchboard with 4 buttons that launched 4 different forms with
4 different queries. The values were somewhat complex so I could not just
use one form with one paremeter query. I tried to have the caption of bttn1
be caption1 and the appropriate query named qry_caption1 and the appropriate
form to be named frm_caption1.

Back to reality, I ended up creating 20 buttons on the switchboard, 20
seperate forms and 20 seperate queries. Was there a better way to accomplish
this without creating the seperate forms and seperate queries?

Davi

 

[Allen Browne]

You're still struggling with the ActiveControl thing?

You can refer to it as a Control object:
Dim ctl As Control
Set ctl = Screen.ActiveControl

Or you can refer to it as a string:
Dim strCtl As String
strCtl As String
strCtl =Screen.ActiveControl.Name

Your further question is a large one. Sounds like you need to be able to
build query strings on the fly. You do know that you can mock up a query,
switch it to SQL View, and copy what you see there? Beyond that, it's
important to include the # as the delimiter for date fields, and the " as
the delimiter for text fields, or perhaps BuildCriteria() can do that for
you.

For the example you specified, if you have a combo on the Switchboard form,
and the user can select 1 of the 4 text values, you would do something like
this in the Open event of your form:

Private Sub Form_Open(Cancel As Integer)
Dim strSql As String
Const strcStub = "SELECT * FROM MyTable "
Const strcTail = " ORDER BY SomeField;"

If CurrentProject.AllForms("Switchboard").IsLoaded Then
With Forms("Switchboard")!MyCombo
If Not IsNull(.Value) Then
strSql = strcStub & "WHERE [SomeField] = " & .Value &
strcTail
End If
End With
End If

If Len(strSql) = 0 Then
strSql = strcStub & strcTail
End If

Me.RecordSource = strSql
End Sub


Note that if SomeField is a Text type field (not a Number type field) it
needs the extra quotes around the value:
strSql = strcStub & "WHERE [SomeField] = """ & .Value & """" & strcTail