Calling Input box -- with dynamic control name extraction

In a previous post, expert "Arji" has helped me to determine as to how I can use an "Input Box".   Thank you Arji!

Now, I'd like to find out if someone knows how to make the InputBox a bit more dynamic since I have multiple command buttons on a form.   And for each InputBox, I have a corresponding textbox.

Here's what I currently have:

Module -- with the following function
=======================

Public Function GetUserInput() As String

    Dim strInput As String
    strInput = InputBox("Please enter a comment.")
    If Not IsNull(strInput) Then
       GetUserInput = strInput
    End If
   
End Function


Form -- contains:
- currently 10 command buttons (their control names are "CommandTemp1", "CommandTemp2", "CommandTemp3", etc.
- currently 10 textboxes (their control names are "TempText1", "TempText2", "TempText3", etc.

Note:  For both, command buttons and textboxes, control name length for those that fall in the range "10-99" is one additional character and for that are greater than 100 two additional 2 characters


Now, in the OnClick event property, each of the 10 command buttons calls the same identical function via the following:
=TransferText()

Code of function "TransferText":
**********************************
Function TransferText() As Boolean

    Dim ctl As Control
    Dim FieldName As String
         
    Set ctl = Screen.ActiveControl
         
    'For command buttons with numeric suffix "1-9"
    If Len(ctl.Name) = 12 Then
        FieldName = "TempText" & Right(ctl.Name, 1)
        '[FieldName] = GetUserInput()
        [& FieldName &] = GetUserInput()   '----- put a breakline here and test value "Fieldname" 2 lines above from any of the command buttons
   
    'For command buttons with numeric suffix "10-99"
    ElseIf Len(ctl.Name) = 13 Then
         FieldName = "TempText" & Right(ctl.Name, 2)
        '[FieldName] = GetUserInput()
        [& FieldName &] = GetUserInput()   '----- put a breakline here and test value "Fieldname" 2 lines above from any of the command buttons
   
    'For command buttons with numeric suffix "100 and more"
    ElseIf Len(ctl.Name) = 14 Then
         FieldName = "TempText" & Right(ctl.Name, 3)
        '[FieldName] = GetUserInput()
        [& FieldName &] = GetUserInput()   '----- put a breakline here and test value "Fieldname" 2 lines above from any of the command buttons
   
    End If
     
    Set ctl = Nothing
   
End Function
**********************************


Okay, what I want to achieve with the  "GetUserInput" and "TransferText" function:
1. Be able to click on any of the 10 (later 125) command buttons
2. TransferText function extracts the numeric portion of the command button's control name that was clicked
3. Concatenate variable "FieldName" with numeric portion of the clicked command button
4. Then use e.g. concatenated alias to reference that the Input Box's text must be "transferred" into the corresponding textbox on the main form

For instance, the following occurs:
a. I click on command button "CommandTemp3"
b. Input box opens and I enter "This is a test."
c. Function "TransferText" extracts "3" and add it to "FieldName".   So I'll get variable "FieldName3".
d. Function "GetUserInput" now sets "[FieldName3] = GetUserInput()"
e. Once I click OK on Input Box, the string "This is a test." ends up in textbox "TempText3"


Now, here's the problem:
As of now, function TransferText is not "smart enough" to interpret the alias in brackets [] and set it = GetUserInput().   Does anyone know how to trick it so that I can pass entered text into the appropriate textbox based on whatever command button was pressed?

If you feel comfortable to download a sample file (InputBoxTest.zip), please go to the following URL:
http://tombock2004.i8.com/Test/


Thanks in advance,
Tom

TomBock2004Asked:
Who is Participating?
 
Sayad Aziz AhmadConnect With a Mentor Commented:
Would u please chk with the following :

Take a variable FieldName1 as string and modify ur codes as follows :

If Len(ctl.Name) = 12 Then
        FieldName1 = "TempText" & Right(ctl.Name, 1)
         [FieldName] = GetUserInput
         Me(FieldName1) = FieldName
    'For command buttons with numeric suffix "10-99"
Elseif   ' for other buttons

Hope this will work for u, i have already tested at my end.

cheers

Aziz
0
 
Sayad Aziz AhmadCommented:
Here is the complete modified code just paste in ur vb

Function TransferText() As Boolean
    Dim ctl As Control
    Dim FieldName As String, FieldName1 As String
         
    Set ctl = Screen.ActiveControl
    If Len(ctl.Name) = 12 Then
        FieldName1 = "TempText" & Right(ctl.Name, 1)
        [FieldName] = GetUserInput
        Me(FieldName1) = FieldName
    ElseIf Len(ctl.Name) = 13 Then
        FieldName1 = "TempText" & Right(ctl.Name, 2)
        [FieldName] = GetUserInput()
        Me(FieldName1) = FieldName
    ElseIf Len(ctl.Name) = 14 Then
         FieldName1 = "TempText" & Right(ctl.Name, 3)
        [FieldName] = GetUserInput()
        Me(FieldName1) = FieldName
    End If
    Set ctl = Nothing
End Function
********************

Aziz
0
 
stevbeCommented:
Function TransferText() As Boolean
    Dim strIn As String
   
    strIn = InputBox("Please enter comments")
    If Len(strIn) > 0 Then
       Me.Controls("TempText" & Mid(Me.ActiveControl.Name, 12)).Value = strIn
    End If

End Function

Steve
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
stevbeCommented:
strIn = InputBox("Please enter comments")
    If Len(strIn) > 0 Then

I would do this in the code directly rather than calling a seperate function, which has a problem because a variable declared as a String can never be Null so even if they press Cancel it is going to wipe out their comments. If this is what you want then you could assign it directly with 1 line ...


Me.Controls("TempText" & Mid(Me.ActiveControl.Name, 12)).Value = InputBox("Please enter comments")


Steve
0
 
dannywarehamCommented:
Just as a seperate noet, have you considered ditching teh inputbox and usnig a form?
This way, you can control the input, the appearance and controls of the form (things that you cannot really do with an input box).

:-)
0
 
d_a_hCommented:
Tom,

Having looked through your last few posts, I see that the form will contain 125+ comboboxes, 125+ buttons and 125+ textboxes, plus I presume some other things.  First:  Remember that there is a limit in Access of just over 700 controls for a form THIS INCLUDES CONTROLS THAT YOU PLACED AND THEN DELETED.  IT IS A HISTORICAL COUNT.  I do not want you to nearly finish building and then hit the limit that cannot be changed!

Secondly:  Have you though about having a temp table that you write the answers to and then when the survey has been finished, the answers are written to the permanent table?  This would mean that you could have a form with a combobox for question numbers and when the question has been answered there is a button for next question and the form refreshes with the new question writing the answer to the temp table.  Would have thought this to be easier both visualy as well as for your sanity!

Any way good luck.
0
 
stevbeCommented:
<IT IS A HISTORICAL COUNT>
If you decompile then repair/compact I *think* the count gets reset.

Steve
0
 
dannywarehamCommented:
As an alternative to d_a_h's secnod option, how about having an unbound form.
The user fills in and is asked to save.
If they click yes, an Append query runs and pastes into the relavent table(s).

BTW, I still stand by my "sack the inputbox and use a form" comment....
0
 
TomBock2004Author Commented:
Thanks, Experts!

Lots of great input from everyone... I got to "digest" every input and will post my questions/comments soon.

Tom
0
 
TomBock2004Author Commented:
All:

The posted question/problem was "answered" by Sayedaziz's solution.   Therefore, it's only fair to award him the full amount of points.   Sayedaziz, thank you, this works great!

Now, all additional comments -- particularly those indicating that the InputForm -- may not be the best choice are greatly appreciated.   And, since posting my question, I have come to reconsider my initial approach.  

Although, the InputForm works great now, I realize the drawbacks of it.   As far as I can see it right now, it's the following:

1. Once some "text" was entered into the InputForm and then passed to another field on the mainform (this field will have a property of "Visible = False"), I won't be able to edit it.   In other words, once I click the same command button again, I get a blank InputForm again and the originally entered text will be overwritten.    

2. I would have the need to have additional controls (e.g. combo box) on a form.   So, in addition to one's comment (nothing else but an "opinion"), I'd like to have an additional standardized method to query for alike answers.   The combo would do such thing.


Until I have built this form, I have one follow-up question.   Once a user clicked a command button and entered text into the InputForm (which then is transferred to the main form), is there a chance to bring up that same text again once the command button is clicked again?   Basically, I need to edit previosly entered text.


Thanks again.   And yes, Sayedaziz, I will use your suggestion in another approach.   Your modified method works great and will come in handy for some other stuff.


Tom
0
 
ArjiCommented:
TomBock2004,

Glad to see the solution.  It was so easy I couldn't see it.  I'm glad I was wrong.... :-)
0
 
Sayad Aziz AhmadCommented:
Thx Tom and ..... Arji u were not wrong u had different approach.

Aziz
0
 
TomBock2004Author Commented:
Aziz:

Do you mind asking you a final question... any ideas as to how I can bring up text that was entered.   For instance:

1. I click on CommandButton5
2. InputBox is brought up with "nothing" in the textfield
3. I enter some text (e.g. "Hello World.") and click OK
4. Now, I click on CommandButton6 and do the same
5. Then, I realize that I should change text in Textfield5.  
6. I click on CommandButton5 again.   At this time, "Hello World" is shown and can be edited.

Any suggestions?

Tom
0
 
stevbeCommented:
Function TransferText() As Boolean
   
    With Me.Controls("TempText" & Mid(Me.ActiveControl.Name, 12))
        .Value = InputBox("Please enter comments", .Value )
    End With

End Function

Steve
0
 
TomBock2004Author Commented:
Steve:

Hmh, that doesn't seem to work.... here are the problems I have encountered:

1. when clicking on the CommandButton1 for the 1st time, I received the error "Invalid Use of Null".
2. once I actually typed something in TempText1 textbox (e.g. "abc") and pressed CommandButton1 again, the now "Microsoft Access" dialog box label has the label "abc".   The actual field of the InputForm, however, is empty.

So, I don't need to have a dynamic label of the dialog box.   I simply need to be able to enter something in the InputForm and be able to edit the text.

Makes sense?

Tom
0
 
stevbeCommented:
Yes ...
1. The default value argument for an InputBox needs to be a string and if there is no value yet it is getting null, I fixed by & vbNullString which equates to an empty string.

2. I was putting the previous value in the Title aregument instead of the Default argument of InputBox. This should work now.

Steve

Function TransferText() As Boolean
    With Me.Controls("TempText" & Mid(Me.ActiveControl.Name, 12))
        .Value = InputBox("Please enter comments", , .Value & vbNullString )
    End With
End Function
0
 
TomBock2004Author Commented:
Steve:

that's perfect!


Thanks,
Tom
0
 
ArjiCommented:
Glad InputBox could work for you.  It's not as 'weak' as many people think. It's actually pretty versatile.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.