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 (' ')?
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 (' ')?
Re: VB6 - InputBox: How to accept multiple string values.
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"
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)
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"
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.
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?
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?
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.
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:
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
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)
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.