Pass Listbox parameters to pull multiple separate reports (1 Viewer)

A

AmandaD

New member

Local time

Today, 14:44

Joined

Nov 21, 2013

Messages

4

• Nov 24, 2013

• #1

Hi there,

I am a beginner coder/programmer, and apparently I am trying to accomplish something that is beyond my abilities.

I have a form that the user can add Work Order numbers to a text box and pass them to a listbox to collect 1 or more values. Each of which need a separate report with the labour hours for each Work Order.

I am having issues figuring out how to get it to pass them to a query or filter the reports.

I have tried many different examples and nothing seems to work.

Help please!!!
Thanks in advance

 

S

Surka

IT dude

Local time

Today, 17:44

Joined

Aug 5, 2013

Messages

41

• Nov 24, 2013

• #2

When you use the Query Wizard, it asks for a Source, there you should select the object from where you want to feed the Query

 

A

AmandaD

New member

Local time

Today, 14:44

Joined

Nov 21, 2013

Messages

4

• Nov 25, 2013

• #3

I cant select a form object as my source from the Query Wizard though. As far as I know I have to write it in VBA to do what I want. I am just unsure how to do this correctly.

 

A

AmandaD

New member

Local time

Today, 14:44

Joined

Nov 21, 2013

Messages

4

• Nov 25, 2013

• #4

My goal is to base a report on the query that will look like the following:

Work Order Number _____

TimeSlip Date Employee Name Code Hours Description
-
- Listing as many as there is per Work Order
-


My Tables are set up as per the following.

WorkOrderInfo

GeneralID - AutoNumber
TimeSlipID - Number
WorkOrder - Text
Code - Text
Hours - Number
Description - Text

TimeSlipInfo

TimeSlipID - Relationship with above
Employee Name - Text
Date of Work - Date/Time


This is the code I am using, however when the query opens it is blank, so I am unsure of what I am missing.


Private Sub cmdOpenReport_Click()
Dim ctl As Control
Dim varItem As Variant
Dim iCurrentRow As Integer
Dim lst As ListBox
Dim db As DAO.Database
Dim qDef As DAO.QueryDef
Dim SQL As String
Dim sCriteria As String

'Select All Items in Listbox
If MyList.ItemsSelected.Count = 0 Then
For iCurrentRow = 0 To MyList.ListCount - 1
MyList.Selected(iCurrentRow) = True
Next iCurrentRow

End If


' loop through list box selections to build criteria string
For Each varItem In MyList.ItemsSelected

sCriteria = sCriteria & ",'" & MyList.ItemData(varItem) & "'"


Next varItem

sCriteria = Mid(sCriteria, 2) ' remove leading comma.
sCriteria = " [WorkOrder] in (" & sCriteria & ")"

' build SQL Statement.
SQL = " SELECT * " & _
" FROM WorkOrderInfo " & _
" WHERE " & sCriteria

Set db = CurrentDb

' delete query qryWorkOrder if already exists.
On Error Resume Next
db.QueryDefs.Delete "qryWorkOrder"
On Error GoTo 0

' create and run query qryWorkOrder.
Set qDef = db.CreateQueryDef("qryWorkOrder", SQL)
DoCmd.OpenQuery "qryWorkOrder"

Set qDef = Nothing
Set db = Nothing
End Sub

 

J

JHB

Have been here a while

Local time

Today, 22:44

Joined

Jun 17, 2012

Messages

7,732

• Nov 26, 2013

• #5

Could you post a stripped version with some sample data of your database, (zip it if you don't have post 10 post)?