DoCmd.OpenReport (Where condition with array)

[Moritz83]

Hi,

I have to create a simple report (multiple smaller cards per sheet -- see the picture attached -- red circle represents the next record)
but I'm struggling to implement a good way to create more than one report per button click.

My idea is to open a MsgBox, the user can enter some integer (example: 1,5,9,10,12) and the VBA command
generates a report with "ID=1", "ID=5" and so on ... so I have the at maximum 8 smaller reports per A4 sheet

What I have so far is this:

DoCmd.OpenReport "kan_Utilities", acViewPreview, , "ID=" & 2

This works correctly what do I have to change to include more "IDs"? I could go on like this:

Dim answer As String
answer = MsgBox("Please enter the values?", vbQuestion + vbYesNo + vbDefaultButton2, "Report")

and if the user uses a syntax like this "1,5,9,10,12" I could remove the commas, create an array and use it within the where clause.
Unfortunately I did not worked with VBA for over a year so I forgot a lot of the useful stuff. Maybe you can help as I guess it's not a difficult problem and
could be solved easily.

Thank you in advance!

Best regards
Moritz

PS: I attached my little project as well so it might be easier to follow my problem (talking about the kan_Utilities report)

PSS: This is the SQL query I use to generate the report

'    SELECT Utilities.ID, Utilities.Description, Utilities.Size, Supplier.Supplier, Utilities.OrderNumber, Utilities.Comment, Cabinet.Cabinet, Utilities.WorkingLength, Utilities.InActive, Utilities.Price, Utilities.OrderSize, Utilities.Minimum, Utilities.Comment, Utilities.OrderNumber, Utilities.Regrind, Untergrenze.Untergrenze AS Ausdr1
'    FROM Category, Supplier INNER JOIN (Floor INNER JOIN (Cabinet INNER JOIN Utilities ON Cabinet.ID = Utilities.CabinetFK) ON Floor.ID = Utilities.FloorFK) ON Supplier.ID = Utilities.SupplierFK
'    WHERE (((Utilities.ID)=2 Or (Utilities.ID)=3));

 

[pbaldy]

I wouldn't rely on users entering in the correct format, I'd probably use a multiselect listbox. To go your way you'd use InputBox and use the resulting string:

DoCmd.OpenReport "kan_Utilities", acViewPreview, , "ID IN(" & YourVariableName & ")"

 

[Moritz83]

Been thinking about at multiselect listbox as well but I have 400, maybe 500 differents IDs so I thought a MsgBox might be the way to go ... but yeah you're right: Never rely on a user input.

Gonna try your idea first as it might be the better way.

 

[pbaldy]

That would be a lot to scroll through. The technique would look like:

http://www.baldyweb.com/multiselect.htm

I suppose you could let them type into an InputBox or textbox and check their entry with code before opening the report.

 

[Moritz83]

Used your link and some code I found to create a working version:

function to determine if a string is "valid"

Function ValidateNumber(tempVal As String) As Boolean
    Dim result As Boolean
    tempVal2 = Replace(tempVal, ",", "")
    If (Not IsNumeric(tempVal2) And (tempVal <> "")) Then
        result = False
    Else
        result = True
    End If
    ValidateNumber = result
End Function

sub bound to a button

Private Sub btn_multipleKanban_Click()
    Dim strWhere As String
    strWhere = InputBox("Please enter IDs", "Report")
    Dim check As Boolean
    check = GeneralFunctions.ValidateNumber(strWhere)
    If check = True Then
        DoCmd.OpenReport "kan_Utilities", acViewPreview, , "ID IN(" & strWhere & ")"
    Else
        MsgBox (strWhere & " isn't a valid input. Please use only numbers and commas!" & vbNewLine & "Example: 2,3,5,9")
    End If
End Sub

not pretty but working except the following case:

2,3,12,,

<-- mind the second "," at the end. It's not that important, I'm just curious
Is there a way to check if a string looks like this?
2,3,12 (starts and ends with a number and uses a comma as separator). The problem might be that the number can between 1 and 4 digits (301,23,1,480).

Anyway, thanks for your help!

 

[isladogs]

Try editing the string used for the case. Something like

If Right(TempVal,2)= ",," Then TempVal = Left(TempVal, Len(TempVal)-2)