Multiple Entries in a Textbox

[Carly]

I currently have a textbox on a form which is linked to a query by [Forms]![FrontPage]![ProductGroup]. What I want to do is enter multiple entries into this textbox so that the query will run for more than one product group?

i.e. Like typing in 33 or 43 or 45 etc into the criteria row in a query

I have tried just one product group and the query ran fine so it is nothing to do with the calling of the textbox, but if I try typing in 33 or 43 or 45 into the textbox it doesn't recognise it (also tried "33" or "43" or "45")

Any ideas?

Regards
Carly

 

[Mile-O]

Wouldn't a mult-select listbox be better?

 

[Carly]

Sounds good but I've never heard of that before.

Could you point me in the right direction?

Thanks
Carly

 

[Mile-O]

Put a listbox on your form; treat it like a combobox with respect to Columns, RowSource, etc.

Change it's Multi-Select property to Simple.

Then, we build a QueryDef, and I'll give an example:

Private Sub MyButton_Click()

    On Error GoTo Err_MyButton_Click

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim varList As Variant
    
    If Me.MyList.ItemsSelected.Count = 0 Then Exit Sub
    
    Set db = CurrentDb
    
    On Error Resume Next
    db.QueryDefs.Delete "MyQuery"
    On Error GoTo Err_MyButton_Click
    
    strSQL = _
        "SELECT * " & _
        "FROM MyTable " & _
        "WHERE MyField In ("
        
    For Each varList In Me.MyList.ItemsSelected
        strSQL = strSQL & Me.MyList.Column(0, varList) & ","
    Next
    
    strSQL = Left(strSQL, Len(strSQL) - 1)
    strSQL = strSQL & ");"
    
    Set qdf = db.CreateQueryDef("MyQuery", strSQL)
    
Exit_MyButton_Click:
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
    
Err_MyButton_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_MyButton_Click
    
End Sub

 

[ifstar]

If you need to specifically type something in - rather than be able to have a preset list box then you could do it in code fairly easily.

On the AfterUpdate event for the text box -
You would need to go through the text box using a loop and strip out each set of criteria then add it onto a string ie.


dim sSQL as string, sCriteria as string

sSQL = whatever the sql for your query is - minus any parts about the WHERE clause onwards

check the text box isn't null
if not then
scriteria = " WHERE ProductGroup = '" & first part of the text box & "' "
end if
while not end of string
scriteria = scriteria & " OR ProductGroup = '" & part of string & "' "
next bit of string
wend
scriteria = scriteria & ";"


combine sSQL and sCriteria
ssql = ssql & scriteria
run the query from code -
db.execute ssql

Thats a quick summary -
you need to work out how best to go through the string - i would tend to do it by working out the number of spaces (ie 33 35 38 39) is 3 spaces but 4 options. So you maybe want switch the while loop to take this into account etc.

Iain

 

[Mile-O]

I would argue, however, that ifstar's solution runs the risk of creating excess database bloat every time that the SQL is executed as the SQL is not a stored query definition. Such a process would necessitate more frequent compacting and repairing of a database.

 

[Carly]

Mile-O-Phile,

I edited your code so that it would work with my data as follows:

Private Sub MyButton_Click()

    On Error GoTo Err_MyButton_Click

    If Me.ProductGroup.ItemsSelected.Count = 0 Then Exit Sub
    
    Set db = CurrentDb
    
    On Error Resume Next
    db.QueryDefs.Delete "MyQuery"
    On Error GoTo Err_MyButton_Click
    
    strSQL = "SELECT dbo_tCustMatRollup.salesoffice, dbo_tCustMatRollup.prodgrp, Sum(IIf(dbo_tCustMatRollup!salesperiod Between Forms!Frontpage!Start And Forms!Frontpage!Current,dbo_tCustMatRollup!linesellvalue,0)) AS [CP Sales], Sum(IIf(dbo_tCustMatRollup!salesperiod Between Forms!Frontpage!Start And Forms!Frontpage!Current,dbo_tCustMatRollup!linecostvalue,0)) AS [CP Cost], Sum(IIf(dbo_tCustMatRollup!salesperiod Between Forms!Frontpage!StartPY And Forms!Frontpage!CurrentPY,dbo_tCustMatRollup!linesellvalue,0)) AS [PY Sales], Sum(IIf(dbo_tCustMatRollup!salesperiod Between Forms!Frontpage!StartPY And Forms!Frontpage!CurrentPY,dbo_tCustMatRollup!linecostvalue,0)) AS [PY Cost] INTO 1500 " & _
             "FROM dbo_tCustMatRollup " & _
             "WHERE dbo_tCustMatRollup.salesperiod Between [Forms]![Frontpage]![StartPY] And [Forms]![Frontpage]![Current]" & _
             "GROUP BY dbo_tCustMatRollup.salesoffice, dbo_tCustMatRollup.prodgrp " & _
             "HAVING dbo_tCustMatRollup.SalesOffice = [Forms]![FrontPage]![SalesOffice] " & _
             "AND dbo_tCustMatRollup.ProdGrp IN ("
    
    For Each varList In Me.ProductGroup.ItemsSelected
        strSQL = strSQL & Me.ProductGroup.Column(0, varList) & ","
    Next
    
    strSQL = Left(strSQL, Len(strSQL) - 1)
    strSQL = strSQL & ") "
    strSQL = strSQL & "ORDER BY dbo_tCustMatRollup.prodgrp;"
       
    Set qdf = db.CreateQueryDef("Qry 1a 1500", strSQL)
    
Exit_MyButton_Click:
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
    
Err_MyButton_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_MyButton_Click
    
End Sub

My problem now is my listbox is created by linking to an excel spreadsheet which has a text format product group. When this code creates my query it puts the selected product groups in as number (i.e. 01 and 09 are selected, but the query says IN (1,9).

Can this be changed so that it stays as text format?
Regards
Carly

 

[Mile-O]

Change this line:

strSQL = strSQL & "'" & Me.ProductGroup.Column(0, varList) & "',"

 

[Carly]

That's work brilliantly

Thanks for all your help

Regards
Carly

 

[ifstar]

I was going under the suggestion that it would not be a pre determined list and that the user would be able to type whatever they wanted - hence why i suggested that method.

 

[Mile-O]

I was going under the suggestion that it would not be a pre determined list and that the user would be able to type whatever they wanted - hence why i suggested that method.

I know but it's still possible to build a stored QueryDef from your code which is better than building and executing a SQL string.