?
Solved

Calling Input box -- with dynamic control name extraction

Posted on 2005-05-11
18
Medium Priority
?
337 Views
Last Modified: 2010-05-18
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

0
Comment
Question by:TomBock2004
  • 5
  • 5
  • 3
  • +3
18 Comments
 
LVL 12

Accepted Solution

by:
Sayedaziz earned 2000 total points
ID: 13984207
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
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 13984227
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
 
LVL 39

Expert Comment

by:stevbe
ID: 13984243
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

Expert Comment

by:stevbe
ID: 13984267
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
 
LVL 26

Expert Comment

by:dannywareham
ID: 13984377
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
 
LVL 4

Expert Comment

by:d_a_h
ID: 13984514
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
 
LVL 39

Expert Comment

by:stevbe
ID: 13984708
<IT IS A HISTORICAL COUNT>
If you decompile then repair/compact I *think* the count gets reset.

Steve
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13985763
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
 

Author Comment

by:TomBock2004
ID: 13987019
Thanks, Experts!

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

Tom
0
 

Author Comment

by:TomBock2004
ID: 13987317
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
 
LVL 17

Expert Comment

by:Arji
ID: 13988875
TomBock2004,

Glad to see the solution.  It was so easy I couldn't see it.  I'm glad I was wrong.... :-)
0
 
LVL 12

Expert Comment

by:Sayedaziz
ID: 13989795
Thx Tom and ..... Arji u were not wrong u had different approach.

Aziz
0
 

Author Comment

by:TomBock2004
ID: 13990331
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
 
LVL 39

Expert Comment

by:stevbe
ID: 13990398
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
 

Author Comment

by:TomBock2004
ID: 13991434
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
 
LVL 39

Expert Comment

by:stevbe
ID: 13996934
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
 

Author Comment

by:TomBock2004
ID: 13998884
Steve:

that's perfect!


Thanks,
Tom
0
 
LVL 17

Expert Comment

by:Arji
ID: 13999073
Glad InputBox could work for you.  It's not as 'weak' as many people think. It's actually pretty versatile.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question