Results 1 to 17 of 17

Thread: VB6 - InputBox: How to accept multiple string values.

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    VB6 - InputBox: How to accept multiple string values.


    Please can someone show me a more efficient way of coding inputbox to accept multiple string values:

    in my example below, the user may enter only a single string value, or may enter multiple string values.

    Problem: the user may forget to enter the single quotes around the string value. How can I code this so that the user can enter only the string value R and not worry about the quotes (' ')?

    Thanks.
    Giftx.

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - InputBox: How to accept multiple string values.

    Please can someone show me a more efficient way of coding inputbox to accept multiple string values:

    in my example below, the user may enter only a single string value, or may enter multiple string values.
    Code:
    Unioncode = InputBox("Please Enter Union Codes for this report. Enter each value enclosed in single quotes. if you have more than one union code, separate them with commas (,):Example: '01','00'")

    Problem: the user may forget to enter the single quotes around the string value. How can I code this so that the user can enter only the string value R and not worry about the quotes (' ')?

    Thanks.
    Giftx

  3. #3
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: VB6 - InputBox: How to accept multiple string values.

    Quote Originally Posted by Giftx
    Please can someone show me a more efficient way of coding inputbox to accept multiple string values:

    in my example below, the user may enter only a single string value, or may enter multiple string values.
    Code:
    Unioncode = InputBox("Please Enter Union Codes for this report. Enter each value enclosed in single quotes. if you have more than one union code, separate them with commas (,):Example: '01','00'")

    Problem: the user may forget to enter the single quotes around the string value. How can I code this so that the user can enter only the string value R and not worry about the quotes (' ')?

    Thanks.
    Giftx
    Simply do not even prompt them to supply quotes. Require the codes to be comma or space delimited.

    When the inputbox is closed, you format the delimited text for your SQL query. Never rely on the user to do the formatting for you.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: VB6 - InputBox: How to accept multiple string values.

    An alternative way would be to use a loop:
    Code:
    Dim strInput as String
    Do 
      strInput = InputBox("Please Enter a Union Code for this report.  If you have no more values to enter, simply press OK")
      If strInput <> "" Then
        Unioncode = Unioncode & "'" & strInput & "', "
      End If
    Loop While strInput <> ""
    If Unioncode = "" Then
      Msgbox "No Union Code values entered, report cancelled"
      Exit Sub
    End If
    Unioncode = Left(Unioncode, Len(Unioncode)-1)

  5. #5
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: VB6 - InputBox: How to accept multiple string values.

    Yet another option is to use a multiselect listbox. Query your database so it returns all the codes in the sort order you want. Add the recordset items to the listbox. Let the user select the codes they want, then format the selected items with single quotes. This way the user cannot type in incorrect codes and is more user-friendly in my opinion.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - InputBox: How to accept multiple string values.

    LaVolpe,
    for the sake of user-friendliness as you pointed out,
    can you give me a taste of how to accomplish this if I have to load values from the database into the listbox or combobox, then how I can format it before I can use it in my query (sql).

    for example, I have to load "CheckStatus" to the combo/listbox, then load "Unioncode" to combo/listbox. Then the user can multi-select values from the combo/listbox.

    Thanks.

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: VB6 - InputBox: How to accept multiple string values.

    To fill the Listboxes from the database, see the article about it in the "Classic VB - ADO" section of our Database Development FAQs/Tutorials

    Then for the listboxes set the Style property to CheckBox. The user can then tick the one(s) they want.


    When you want to use the values, use a loop to build the string. Here is an example of finding the items that have been ticked:
    Code:
    Dim intLoop As Integer
    For intLoop = 0 To List1.ListCount - 1
      If List1.Selected(intLoop) Then
        MsgBox List1.List(intLoop)
      End If
    Next intLoop

  8. #8
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: VB6 - InputBox: How to accept multiple string values.

    The attached example will programmatically create an SQL IN Clause based on selections from a checked, multi-select, ListBox. This is what you mean, right?
    Attached Files Attached Files

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - InputBox: How to accept multiple string values.

    Good morning gentlemen.

    Hack, in response to your question, NO. My objective is for the user to select either a single value '00' (unioncode) or multiple values '00','01','32' (unioncode).

    Then the selected values (unioncode) will be used in my sql.

    The same scenario will also apply to "Checkstatus". However, Checkstatus will be a single selection comming from a combobox.

    My Concern: In addition, how will I format the selected values because it has to be recognized as a string value in the sql?

    Thanks.
    Giftx.

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: VB6 - InputBox: How to accept multiple string values.

    In that case your answer should have been Yes, as what Hack posted does exactly that for UnionCode - it builds that part of the SQL statement based on what the user has ticked (as string values, as it uses the ' character around the values).

    Checkstatus is easier, as you simply need to use something like this: Checkstatus = "'" & cboCheckstatus.Text & "'"


    To fill the listbox & combobox with the values from the database, see the article I referred to at the start of my previous post.

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - InputBox: How to accept multiple string values.

    Yes, Si.
    I modelled after reading thru the article you recommended.

    Below is the code I wrote based on the example from the article. However, when I ran it, I got error: Too few parameters. Expected 1.

    Code:
    'Define Database path:
    '---------------------
    DbPath = "\\Livctrls03-08\Data1\APP\UNCLAIMW\UCCTest\Databases\"
    DbName = "Unclaimed Checks.mdb"
    
    'Open Database/Recordset:
    '------------------------
    Set Dbs = OpenDatabase(DbPath & DbName)
    Set rslstbxitem = Dbs.OpenRecordset("EmployeeInfo", dbOpenDynaset)
    Set rscboitem = Dbs.OpenRecordset("tblUnclaimed", dbOpenDynaset)
    
    strSQL = "SELECT EmployeeInfo.Union_code " _
           & "From EmployeeInfo " _
           & "Where EmployeeInfo.Union_code <> vbEmptyString; "
           
    Set rslstbxitem = Dbs.OpenRecordset(strSQL)  <---- Error when it hit this line.
    
    'populate Union codes Listbox:
    '-----------------------------
    With lstbxucde
         .Clear
         Do While Not rslstbxitem.EOF
            .AddItem rslstbxitem.Fields("Union_code").Value
            rslstbxitem.MoveNext
         Loop
    End With

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: VB6 - InputBox: How to accept multiple string values.

    That would be because you have put vbEmptyString (a VB constant) into the SQL statement, and as always the SQL statement knows nothing about what is in VB - what you should do is use an SQL string value with no length, ie:
    Code:
           & "Where EmployeeInfo.Union_code <> ''; "
    If you really wanted to you could append the value of vbEmptyString, but it would give the same result as the above:
    Code:
           & "Where EmployeeInfo.Union_code <> '" & vbEmptyString & "'; "

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - InputBox: How to accept multiple string values.

    Si,
    my program ran okay and populated both the listbox and combobox.

    However, regarding the formating of the string values that will be selected from the controls, how does this code that Hack suggested fit into my sql?

    This is the INClause module:
    Code:
    If lstbxucde.SelCount = 0 Then
       Exit Sub
    End If
    
    For i = 0 To lstbxucde.ListCount - 1
        If lstbxucde.Selected(i) Then
           strCode = lstbxucde.List(i)
           INClause = INClause & "'" & strCode & "'" & ","
        End If
    Next
    
    INClause = Left(INClause, (Len(INClause) - 2))
    
    INClause = "(" & INClause & ")"
    This is the module that populates my controls:
    Code:
    strSQL = "SELECT EmployeeInfo.Union_code " _
           & "From EmployeeInfo " _
           & "Where EmployeeInfo.Union_code <> ''; "
           
    Set rslstbxitem = Dbs.OpenRecordset(strSQL)
    
    'populate Union codes Listbox:
    '-----------------------------
    With lstbxucde
         .Clear
         Do While Not rslstbxitem.EOF
            .AddItem rslstbxitem.Fields("Union_code").Value
            rslstbxitem.MoveNext
         Loop
    End With
    
    'Populate Check Status Combobox:
    '-------------------------------
    strSQL = vbEmptyString
    
    strSQL = "SELECT tblUnclaimed.Status " _
           & "From tblUnclaimed " _
           & "Where tblUnclaimed.Status <> ''; "
           
    Set rscboitem = Dbs.OpenRecordset(strSQL)
    
    With cbochkstat
         .Clear
         Do While Not rscboitem.EOF
            .AddItem rscboitem.Fields("Status").Value
            rscboitem.MoveNext
         Loop
    End With

  14. #14
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: VB6 - InputBox: How to accept multiple string values.

    Quote Originally Posted by Giftx
    ... how does this code that Hack suggested fit into my sql
    Change Hack's INClause variable to your UnionCode variable.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    341

    Re: VB6 - InputBox: How to accept multiple string values.

    Gentlemen,
    I am gradually inching closer to my goals, thanks to all your expertise.

    In my Form_Load module, both my controls(listbox and combo) loaded okay with the data from my tables.

    Running the main body of the program, I got this error:
    RunTime error 3141: The Select statement includes a reserved word or an argument name that is misspelled or missing or the punctuation is incorrect"

    Below is the Debug.Print output:
    Code:
    SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.ReIssued_Check_Amount, FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) WHERE (((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In ((('45)'45)) AND ((tblUnclaimed.Status) In ('R'))) AND ((Len([EmployeeInfo].[Status]))=1)) AND tblUnclaimed.ReIssued_Check_Date Between #03/1/2008# And #03/31/2008# ORDER BY tblUnclaimed.PassNumber;
    Note: the union codes (45 and 45) are not well formated.


    Form_Load Module - Populates the controls:

    Code:
    Private Sub Form_Load()
    'Maximize window when program runs:
    '----------------------------------
    If MainForm.WindowState <> vbMaximized Then
       MainForm.WindowState = vbMaximized
    Else
       MainForm.WindowState = vbNormal
    End If
    
    'Define Database path:
    '---------------------
    DbPath = "\\Livctrls03-08\Data1\APP\UNCLAIMW\UCCTest\Databases\"
    DbName = "Unclaimed Checks.mdb"
    
    'Open Database/Recordset:
    '------------------------
    Set Dbs = OpenDatabase(DbPath & DbName)
    Set rslstbxitem = Dbs.OpenRecordset("EmployeeInfo", dbOpenDynaset)
    Set rscboitem = Dbs.OpenRecordset("tblUnclaimed", dbOpenDynaset)
           
    strSQL = vbEmptyString  'Refresh strSQL:
    
    strSQL = "SELECT EmployeeInfo.Union_code " _
           & "From EmployeeInfo " _
           & "Where EmployeeInfo.Union_code <> ''; "
          
    Set rslstbxitem = Dbs.OpenRecordset(strSQL)
    
    'populate Union codes Listbox:
    '-----------------------------
    With lstbxucde
         .Clear
         Do While Not rslstbxitem.EOF
            .AddItem rslstbxitem.Fields("Union_code").Value
            rslstbxitem.MoveNext
         Loop
    End With
    
    
    'Populate Check Status Combobox:
    '-------------------------------
    strSQL = vbEmptyString   'Refresh strSQL:
    
    strSQL = "SELECT tblUnclaimed.Status " _
           & "From tblUnclaimed " _
           & "Where tblUnclaimed.Status <> ''; "
           
    Set rscboitem = Dbs.OpenRecordset(strSQL)
    
    With cbochkstat
         .Clear
         Do While Not rscboitem.EOF
            .AddItem rscboitem.Fields("Status").Value
            rscboitem.MoveNext
         Loop
    End With
    Main Module with Run Time Error:
    Code:
    Private Sub cmdExecproc_Click()
    
    'If lstbxucde.SelCount = 0 Then
    '   Exit Sub
    'End If
    
    For i = 0 To lstbxucde.ListCount - 1
        If lstbxucde.Selected(i) Then
           strCode = lstbxucde.List(i)
           Unioncode = Unioncode & "'" & strCode & "'" & ","
        End If
    Next
    
    Unioncode = Left(Unioncode, (Len(Unioncode) - 2))
    
    Unioncode = "(" & Unioncode & ")"
    
    If cbochkstat = "" Then
       MsgBox "Please Select a Check Status, E,N,P,R Or U, to continue", vbInformation, "Unclaimed Checks System"
       cbochkstat.SetFocus
       Screen.MousePointer = vbNormal
       Exit Sub
    End If
                 
    CheckStatus = "'" & cbochkstat.Text & "'"
    
    'Example2
    strSQL = "SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.ReIssued_Check_Amount, "
    strSQL = strSQL & "FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) "
    strSQL = strSQL & "AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) "
    strSQL = strSQL & "WHERE (((EmployeeInfo.Status) Like 'N*') "
    strSQL = strSQL & "AND ((EmployeeInfo.Union_Code) In (" & Unioncode & ") "
    strSQL = strSQL & "AND ((tblUnclaimed.Status) In (" & CheckStatus & "))) "
    strSQL = strSQL & "AND ((Len([EmployeeInfo].[Status]))=1)) "
    strSQL = strSQL & "AND tblUnclaimed.ReIssued_Check_Date Between #" & Begindate & "# And #" & Enddate & "# "
    strSQL = strSQL & "ORDER BY tblUnclaimed.PassNumber;"
    
    Debug.Print strSQL
        
    Set rsin = Dbs.OpenRecordset(strSQL)
    I do appreciate all the help.
    Giftx.

  16. #16
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: VB6 - InputBox: How to accept multiple string values.

    Before you start your loop that creates the UnionCode string, add this to reset it:
    UnionCode = vbNullString
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  17. #17
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: VB6 - InputBox: How to accept multiple string values.

    Also think about what this line is doing:
    Code:
    Unioncode = Left(Unioncode, (Len(Unioncode) - 2))
    ..this is causing a problem because the number is not right for what it is supposed to be doing (removing the last comma), as you have removed the space that was in Hacks version.

    Note also that the line after that ( Unioncode = "(" & Unioncode & ")" ) is not needed, as you add the brackets when making strSQL.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width