Modify function (CurrentDb.QueryDefs)

[Tom]

I need some help w/ modifying the function (below ***s). More specifically,
I need to
make adjustments to the line the begins w/ "CurrentDb.QueryDefs".

Right now, when I click on textbox A1Q1, the control name "A1Q1" is being
read into the
"CurrentDb.QueryDefs" (& ctl.Name &).

However, instead of "A1Q1", I want the following being read into the
"ctl.Name":
- Strip "A1" from "A1Q1"
- Add "Score" in front of "Q1"

.... essentially, I want to replace the control name "A1Q1" with "ScoreQ1"
without actually changing the control name in the textbox. "Score" wil be
static...
while "Q#" can be x.

Is that possible? If yes, how would I go about it?


******************************

Private Sub A1Q1_Click()

Dim ctl As Control
Set ctl = Screen.ActiveControl
CurrentDb.QueryDefs("Query1").SQL = "Select " & ctl.Name & "
From tblTest;"
DoCmd.OpenQuery "Query1"
Set ctl = Nothing

End Sub

******************************



Thanks so much in advance,
Tom

 

[MacDermott]

You can write a function which will take a string as an argument and return
the string you want.
For example:

Private Function FieldName(CtlName as String) as String
FieldName="Score" & Right(CtlName,2)
End Function

This will work fine as long as what you want to retrieve from the control
name is always the last 2 characters. If there's a chance you'll need 3
characters (e.g. the number is bigger than 9), you'll need a more complex
function.

BTW -
The design you have - a series of numbered fields and matching buttons -
is not especially flexible and may cause you problems if the number of
fields ever changes.

 

[Tom]

Hello...

thanks for your prompt reply.

I'm not sure if I described the problem properly. I have 16 textboxes (all
of their control names are exactly 4 characters). Their control names are
sequential in logic.

I have 4 different "areas" (designated with "A") and 4 "quarters"
(designated with
"Q). So, I will have 16 functions that have an OnClick event. The sub's
names are:
- A1Q1, A1Q2, A1Q3, A1Q4
- A2Q1, A2Q2, A2Q3, A2Q4
- A3Q1, A3Q2, A3Q3, A3Q4
- A4Q1, A4Q2, A4Q3, A4Q4

For each of the 16 OnClick events (functions), I want to call a "generic"
line of code
(CurrentDb.QueryDefs("Query1").SQL = "Select " & FieldName & " From
tblTest;")
that basically replaces the "A1, A2, A3, A4" with "Score"; at the same time
it must
get the control names's last 2 characters. The will always be either "Q1,
Q2, Q3, Q4".

I tried your suggestion. I doubt I have implemented it properly. Your
solution (what I refer to "1st Solution" below throws me an error (see error
message below).

I then tried to modify it and put the "FieldName" into the 2nd solution...
that also throws me an error.

Do you have any additional pointers as to how I can make the
"CurrentDb.QueryDefs" dynamic and get the proper value of e.g. ...

"CurrentDb.QueryDefs("Query1").SQL = "Select " & ScoreQ1 & " From tblTest;"

.... when clicking onto A1Q1?


Thanks so much,
Tom


===================================================================

Error of "1st Solution:
"Compile Error: Argument not optional"


Error of "2nd Solution:
"Run-time error '91'. Object variable or With block variable not set"



*** BEGIN 1st SOLUTION ****************

Private Function FieldName(CtlName As String) As String

FieldName = "Score" & Right(CtlName, 2)

End Function

******************************

Private Sub A1Q1_Click()

Dim ctl As Control

Set ctl = Screen.ActiveControl
CurrentDb.QueryDefs("Query1").SQL = "Select " & FieldName & "
From tblTest;"
DoCmd.OpenQuery "Query1"
Set ctl = Nothing

End Sub

*** END 1st SOLUTION ****************





*** BEGIN 2nd SOLUTION ****************

Private Sub A1Q1_Click()

Dim ctl As Control
Dim FieldName As String

FieldName = "Score" & Right(ctl.Name, 2)

Set ctl = Screen.ActiveControl
CurrentDb.QueryDefs("Query1").SQL = "Select " & FieldName & "
From tblTest;"
DoCmd.OpenQuery "Query1"
Set ctl = Nothing

End Sub

*** END 2nd SOLUTION ****************

 

[MacDermott]

Your second solution should work fine if you move the line
Set ctl=...
to be the first line after the Dim statements.

In your version, the code doesn't know what ctl is until after you ask for
its name.

HTH
- Turtle

 

[Tom]

Again, thanks so much for your prompt feedback... it works great!

I now realize that I would like to make 2 more modifications to the
query/routine though.

1) is there a way to pull the 2nd character of the control name and include
it in a WHERE clause.

For instance, if the text box's control name = "A2Q1" I then want to pull
the the value "2", "StoreIt" somewhere and use that the "StoreIt" value in
the
string such as:

CurrentDb.QueryDefs("Query1").SQL = "Select " & ctl.Name & " From tblTest
WHERE PrimaryKey = "StoreIt" (meaning.... WHERE PrimaryKey = "2")


2) Also, I currently have the 16 OnClick events that hold the same code,
such as:

***************************
Private Sub A1Q1_Click()

Dim ctl As Control
Dim FieldName As String

Set ctl = Screen.ActiveControl

FieldName = "Score" & Right(ctl.Name, 2)
CurrentDb.QueryDefs("Query1").SQL = "Select " & FieldName & " From
tblTest;"

Set ctl = Nothing

End Sub

***************************

Private Sub A1Q2_Click()

Dim ctl As Control
Dim FieldName As String

Set ctl = Screen.ActiveControl

FieldName = "Score" & Right(ctl.Name, 2)
CurrentDb.QueryDefs("Query1").SQL = "Select " & FieldName & " From
tblTest;"

Set ctl = Nothing

End Sub

***************************

repeating the same function an addtional 14 times......


Now, instead of using it 16 times, I'd rather keep the actual function by
itself and simply call it 16 times. I thought that the below code might
work, but unfortunately it doesn't.


***************************
Function GetControlName(CtlName As TextBox) As String

Dim ctl As Control
Dim FieldName As String

Set ctl = Screen.ActiveControl

FieldName = "Score" & Right(ctl.Name, 2)
CurrentDb.QueryDefs("Query1").SQL = "Select " & FieldName & " From
tblTest;"

Set ctl = Nothing

End Function

***************************

Private Sub A1Q1_Click()

GetControlName

End Sub

***************************

Private Sub A1Q2_Click()

GetControlName

End Sub

***************************

and so on....

***************************

Do you have a recommendation as to how I can put the few lines of code into
a function which then is called by the individual OnClick events?

Again, thanks for your help on this!!!


Tom

 

[MacDermott]

Hi, Tom!

Glad it's working for you.
Looks as if you need to go to the Access Help file (or whatever instrument
of learning you prefer) and get familiar with the Left(), Right(), and Mid()
functions.

You can use them as you build your SQL string.

HTH
- Turtle

 

[Tom]

Thanks again for helping me out on this. I appreciate it.