Hi all,

I just wanted some advice about a problem I am having in the development 
of a report. I want the user to be able to input an order number and then 
the details relating to the order he/she has asked for should be inputted 
onto a report. The user may wish to enter 5-10 order numbers before 
printing out the report. I cannot think of a way Access will allow you to 
do this.

Any ideas?

Thanks
James

I'm thinking of a way that might work. Create a pop up form with 5 or 10 text boxes where the user can
input the order numbers and then use the text box names and values in your query or SQL statement

Kenny

----- Original Message -----
From: James Potter
Sent: Thursday, November 28, 2002 5:54 AM
To: Access
Subject: [access] Advice Needed

Hi all,

I just wanted some advice about a problem I am having in the development  
of a report. I want the user to be able to input an order number and then  
the details relating to the order he/she has asked for should be inputted  
onto a report. The user may wish to enter 5-10 order numbers before  
printing out the report. I cannot think of a way Access will allow you to  
do this.

Any ideas?

Thanks
James
to unsubscribe send a blank email to Get more from the Web.  FREE MSN Explorer download : http://explorer.msn.com

Hi James, 

Give this a try, I've used it for something very similiar, having to pull 
up reports on multiple building numbers without having to keep closing and 
opening the report.  I found this out on the net somewhere and can't 
recall exactly where, but it works very well for my needs.  You'll have to 
tweak it out a bit for your needs of course :)  

On the first form (frmRoofSelection), which is a popup form, the user 
selects (via a listbox called SelectBuildings) the building numbers she 
wishes to work with.  She then selects the "Print Reports" button which 
contains the following code:

-------------------------------
Private Sub PrintLetters_Click()
On Error GoTo Err_PrintLetters_Click

    Dim stDocName As String
    Dim stFilter As String
    
'Create filter using form name, list box name, field name
    stFilter = BuildWhereClause
("frmRoofSelection", "SelectBuildings", "Building")
    stDocName = "frmRoofReportsAndLetters"
    DoCmd.OpenForm stDocName, , , stFilter
    
Exit_PrintLetters_Click:
    Exit Sub

Err_PrintLetters_Click:
    MsgBox Err.Description
    Resume Exit_PrintLetters_Click
End Sub
-------------------------------

The following is the BuildWhereClause which happily resides in its own 
module:

-------------------------------
Public Function BuildWhereClause(ByVal pstrFormName As String, ByVal 
pstrCurrentListBox As String, ByVal pstrFieldName As String) As String

Const MaxSelection As Integer = 10 'Maximum items that can be selected
On Error GoTo ErrorHandler

Dim varItemSelected As Variant
'astrItemsToRemove must be variant or it gives a null value error
Dim astrItemsToRemove(MaxSelection) As Variant
Dim intItemCounter As Integer
Dim intLastArrayItem As Integer

'Set intial variables
intItemCounter = -1

'Count the number of items selected and kick out a message box if more 
than the allowed are selected
For Each varItemSelected In Forms(pstrFormName).Controls
(pstrCurrentListBox).ItemsSelected
    If intItemCounter < MaxSelection - 1 Then
        intItemCounter = intItemCounter + 1
        astrItemsToRemove(intItemCounter) = Forms(pstrFormName).Controls
(pstrCurrentListBox).ItemData(varItemSelected)
    Else
        MsgBox "Only " & MaxSelection & " " & pstrFieldName & "s can be 
selected at one time. Only the first " & MaxSelection & " will be 
opened.", vbInformation, "Selection Error"
        Exit For
    End If

Next varItemSelected
    intLastArrayItem = intItemCounter

'Add "OR" to the end of each item selected except for the last one
For intItemCounter = 0 To intLastArrayItem - 1
    BuildWhereClause = BuildWhereClause & pstrFieldName & " = '" & 
astrItemsToRemove(intItemCounter) & "' OR "

Next intItemCounter
    BuildWhereClause = BuildWhereClause & pstrFieldName & " = '" & 
astrItemsToRemove(intLastArrayItem) & "'"

Exit Function

ErrorHandler:
    Select Case Err.Number
        Case 9
            Resume Next
        Case Else
            MsgBox Err.Number & Chr(10) & "Eep! " & Err.Description & " 
in " & Err.Source
    End Select

End Function
-------------------------------

In frmRoofReportsAndLetters there is a text box called BuildingList, the 
control source points to the form filter ("=[Filter]").  BuildingList can 
then be used as a filter to pass through to another command or as a where 
construct in an sql command.

I'm not sure if this will work for your needs, as you might have too large 
an amount of invoice numbers to place in the initial list box, but it 
should give you a place to start.

HTH,

Cristina




> Hi all,

> I just wanted some advice about a problem I am having in the development 
o> f a report. I want the user to be able to input an order number and 
then 
t> he details relating to the order he/she has asked for should be 
inputted 
o> nto a report. The user may wish to enter 5-10 order numbers before 
p> rinting out the report. I cannot think of a way Access will allow you 
to 
d> o this.

> Any ideas?

> Thanks
J> ames