How do I Print multiple records in a report depending field value

G

Guest

I am trying to print a report depending on a the field "OrderQty". i did
created the code with the print command and the criteria and it looks like
this:

DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

however, it give me an error saying, that "|" field is not found. i looked
through the code and tested the code, by putting 1 or 2 in place of OrderQty
and it prints fine, that means to me that it's not the code and i looked to
through the Query that the source of that Report and find no buggie field
with the name "|" and i looked in the report for the same field and i find
nothing...now i dont know where to look. Does anyone has any idea on where am
going wrong with this.
 
G

Guest

thanks for the reponse Rick,

yes i do have some code that defines, strwhere and strdescrip. Do you think
that's the problem? i went step by step on that code and i dont find anything
that might cause this problem. i substituted [OrderQty], the criteria, by a
number, say 1 or 2 and it prints fine, but when i put the OrderQty as a
number of pages criteria, it gives me that error. And i tried it without any
other code and it gives me the same error. at this point, i am thinking the
query or table that OrderQty comes from, but i dont see any bugg there
either. the code that i used , OrderQty with is bellow.


Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
Dim prm_sched As Control
Dim DBS As Database
Dim rst As Recordset


Set prm_sched = Me![sched]

If prm_sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")

'run through all selected items
For Each varItem In prm_sched.ItemsSelected
rst.AddNew
rst!OrderNo = prm_sched.Column(0)
rst!RequiredDate = prm_sched.Column(1)
rst!CustomerID = prm_sched.Column(2)
rst!ModelNumber = prm_sched.Column(3)
rst!Description = prm_sched.Column(4)
rst!OrderQty = prm_sched.Column(5)
rst!Comment = prm_sched.Column(6)
rst!Bfill = prm_sched.Column(7)
rst!Qfill = prm_sched.Column(8)
rst!Uphfill = prm_sched.Column(9)
rst!Ptfill = prm_sched.Column(10)
rst!Spring = prm_sched.Column(11)
rst!Label = prm_sched.Column(12)
rst!Size = prm_sched.Column(13)
rst!Needle = prm_sched.Column(14)
rst!Pattern = prm_sched.Column(15)
rst!Config = prm_sched.Column(16)
rst!Border = prm_sched.Column(17)
rst!Border = prm_sched.Column(18)
rst!FoamCore = prm_sched.Column(19)
rst!FoamEnc = prm_sched.Column(20)
rst.update
Next
' strDelim = "" 'Delimiter appropriate to field type.
strDoc = "rptProduction"
strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
'strDescrip = "Production Required: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

the other code that i used it in is as follow

Dim strProductID As String
Dim strCriteriaProductID As String
Dim strCriteriaOrderNo As String

If NewRecord Then
MsgBox "This record contains no data." _
, vbInformation, "Invalid Action"
Exit Sub
Else
strProductID = "rptProduction"
DoCmd.OpenReport strProductID, acViewPreview, , "[ID_1]='" &
Forms!frmProduction![ProductID] & "' And [OrderNo] = " &
Forms!frmProduction![OrderNo]
DoCmd.PrintOut , , , , [OrderQty], 0


End If

i dont know where this problem be. thanks for your help
--
need help


Rick B said:
I assume that you have additional code that defines "strWhere" and
"strDescrip"?



--
Rick B



Will G said:
I am trying to print a report depending on a the field "OrderQty". i did
created the code with the print command and the criteria and it looks like
this:

DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

however, it give me an error saying, that "|" field is not found. i looked
through the code and tested the code, by putting 1 or 2 in place of
OrderQty
and it prints fine, that means to me that it's not the code and i looked
to
through the Query that the source of that Report and find no buggie field
with the name "|" and i looked in the report for the same field and i find
nothing...now i dont know where to look. Does anyone has any idea on where
am
going wrong with this.
Click to expand...
Click to expand...
 
G

Guest

I still don't see where you expect to get OrderQty from. I would expect you
to set a variable or use something like Me.OrderQty if OrderQty is a field
from your form's record source or a control name on your form.

--
Duane Hookom
Microsoft Access MVP


Will G said:
thanks for the reponse Rick,

yes i do have some code that defines, strwhere and strdescrip. Do you think
that's the problem? i went step by step on that code and i dont find anything
that might cause this problem. i substituted [OrderQty], the criteria, by a
number, say 1 or 2 and it prints fine, but when i put the OrderQty as a
number of pages criteria, it gives me that error. And i tried it without any
other code and it gives me the same error. at this point, i am thinking the
query or table that OrderQty comes from, but i dont see any bugg there
either. the code that i used , OrderQty with is bellow.


Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
Dim prm_sched As Control
Dim DBS As Database
Dim rst As Recordset


Set prm_sched = Me![sched]

If prm_sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")

'run through all selected items
For Each varItem In prm_sched.ItemsSelected
rst.AddNew
rst!OrderNo = prm_sched.Column(0)
rst!RequiredDate = prm_sched.Column(1)
rst!CustomerID = prm_sched.Column(2)
rst!ModelNumber = prm_sched.Column(3)
rst!Description = prm_sched.Column(4)
rst!OrderQty = prm_sched.Column(5)
rst!Comment = prm_sched.Column(6)
rst!Bfill = prm_sched.Column(7)
rst!Qfill = prm_sched.Column(8)
rst!Uphfill = prm_sched.Column(9)
rst!Ptfill = prm_sched.Column(10)
rst!Spring = prm_sched.Column(11)
rst!Label = prm_sched.Column(12)
rst!Size = prm_sched.Column(13)
rst!Needle = prm_sched.Column(14)
rst!Pattern = prm_sched.Column(15)
rst!Config = prm_sched.Column(16)
rst!Border = prm_sched.Column(17)
rst!Border = prm_sched.Column(18)
rst!FoamCore = prm_sched.Column(19)
rst!FoamEnc = prm_sched.Column(20)
rst.update
Next
' strDelim = "" 'Delimiter appropriate to field type.
strDoc = "rptProduction"
strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
'strDescrip = "Production Required: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

the other code that i used it in is as follow

Dim strProductID As String
Dim strCriteriaProductID As String
Dim strCriteriaOrderNo As String

If NewRecord Then
MsgBox "This record contains no data." _
, vbInformation, "Invalid Action"
Exit Sub
Else
strProductID = "rptProduction"
DoCmd.OpenReport strProductID, acViewPreview, , "[ID_1]='" &
Forms!frmProduction![ProductID] & "' And [OrderNo] = " &
Forms!frmProduction![OrderNo]
DoCmd.PrintOut , , , , [OrderQty], 0


End If

i dont know where this problem be. thanks for your help
--
need help


Rick B said:
I assume that you have additional code that defines "strWhere" and
"strDescrip"?



--
Rick B



Will G said:
I am trying to print a report depending on a the field "OrderQty". i did
created the code with the print command and the criteria and it looks like
this:

DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

however, it give me an error saying, that "|" field is not found. i looked
through the code and tested the code, by putting 1 or 2 in place of
OrderQty
and it prints fine, that means to me that it's not the code and i looked
to
through the Query that the source of that Report and find no buggie field
with the name "|" and i looked in the report for the same field and i find
nothing...now i dont know where to look. Does anyone has any idea on where
am
going wrong with this.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hey Duane,

haft of the problem is fixed, thanks a lot.

what i wasnt doing is what you said, not specifying the control. i had to
declare a variable and called it Quantity and equaled it to
me.sched.column(5).....the sched is the list box on the form that contains
OrderQty at that column location. now it prints according to my first pick.
this is the new problem that came up. it prints now, according to the first
item selected from the multiselect box. cant get it to print, according to
each OrderQty from each selection. example of the problem......i pick order
number 4001 and since i have the criteria to be Order Number, so all Order
number 4001 will come out on different page of the report. but each 4001 has
it's own OrderQty, now it's printing according to the first 4001. i hope you
understand what just wrote, it's a little scrambled. i am just trying to be a
little clear.

thanks again.
--
need help


Duane Hookom said:
I still don't see where you expect to get OrderQty from. I would expect you
to set a variable or use something like Me.OrderQty if OrderQty is a field
from your form's record source or a control name on your form.

--
Duane Hookom
Microsoft Access MVP


Will G said:
thanks for the reponse Rick,

yes i do have some code that defines, strwhere and strdescrip. Do you think
that's the problem? i went step by step on that code and i dont find anything
that might cause this problem. i substituted [OrderQty], the criteria, by a
number, say 1 or 2 and it prints fine, but when i put the OrderQty as a
number of pages criteria, it gives me that error. And i tried it without any
other code and it gives me the same error. at this point, i am thinking the
query or table that OrderQty comes from, but i dont see any bugg there
either. the code that i used , OrderQty with is bellow.


Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
Dim prm_sched As Control
Dim DBS As Database
Dim rst As Recordset


Set prm_sched = Me![sched]

If prm_sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")

'run through all selected items
For Each varItem In prm_sched.ItemsSelected
rst.AddNew
rst!OrderNo = prm_sched.Column(0)
rst!RequiredDate = prm_sched.Column(1)
rst!CustomerID = prm_sched.Column(2)
rst!ModelNumber = prm_sched.Column(3)
rst!Description = prm_sched.Column(4)
rst!OrderQty = prm_sched.Column(5)
rst!Comment = prm_sched.Column(6)
rst!Bfill = prm_sched.Column(7)
rst!Qfill = prm_sched.Column(8)
rst!Uphfill = prm_sched.Column(9)
rst!Ptfill = prm_sched.Column(10)
rst!Spring = prm_sched.Column(11)
rst!Label = prm_sched.Column(12)
rst!Size = prm_sched.Column(13)
rst!Needle = prm_sched.Column(14)
rst!Pattern = prm_sched.Column(15)
rst!Config = prm_sched.Column(16)
rst!Border = prm_sched.Column(17)
rst!Border = prm_sched.Column(18)
rst!FoamCore = prm_sched.Column(19)
rst!FoamEnc = prm_sched.Column(20)
rst.update
Next
' strDelim = "" 'Delimiter appropriate to field type.
strDoc = "rptProduction"
strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
'strDescrip = "Production Required: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

the other code that i used it in is as follow

Dim strProductID As String
Dim strCriteriaProductID As String
Dim strCriteriaOrderNo As String

If NewRecord Then
MsgBox "This record contains no data." _
, vbInformation, "Invalid Action"
Exit Sub
Else
strProductID = "rptProduction"
DoCmd.OpenReport strProductID, acViewPreview, , "[ID_1]='" &
Forms!frmProduction![ProductID] & "' And [OrderNo] = " &
Forms!frmProduction![OrderNo]
DoCmd.PrintOut , , , , [OrderQty], 0


End If

i dont know where this problem be. thanks for your help
--
need help


Rick B said:
I assume that you have additional code that defines "strWhere" and
"strDescrip"?



--
Rick B



I am trying to print a report depending on a the field "OrderQty". i did
created the code with the print command and the criteria and it looks like
this:

DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

however, it give me an error saying, that "|" field is not found. i looked
through the code and tested the code, by putting 1 or 2 in place of
OrderQty
and it prints fine, that means to me that it's not the code and i looked
to
through the Query that the source of that Report and find no buggie field
with the name "|" and i looked in the report for the same field and i find
nothing...now i dont know where to look. Does anyone has any idea on where
am
going wrong with this.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I'm not sure how you can expect to print different quantities with only one
DoCmd.OpenReport...

Allen Browne has some solutions in his "Serious ... Reports" section at
http://www.allenbrowne.com/tips.html. Check out the one pertaining to
multiple quantities of labels.
--
Duane Hookom
Microsoft Access MVP


Will G said:
Hey Duane,

haft of the problem is fixed, thanks a lot.

what i wasnt doing is what you said, not specifying the control. i had to
declare a variable and called it Quantity and equaled it to
me.sched.column(5).....the sched is the list box on the form that contains
OrderQty at that column location. now it prints according to my first pick.
this is the new problem that came up. it prints now, according to the first
item selected from the multiselect box. cant get it to print, according to
each OrderQty from each selection. example of the problem......i pick order
number 4001 and since i have the criteria to be Order Number, so all Order
number 4001 will come out on different page of the report. but each 4001 has
it's own OrderQty, now it's printing according to the first 4001. i hope you
understand what just wrote, it's a little scrambled. i am just trying to be a
little clear.

thanks again.
--
need help


Duane Hookom said:
I still don't see where you expect to get OrderQty from. I would expect you
to set a variable or use something like Me.OrderQty if OrderQty is a field
from your form's record source or a control name on your form.

--
Duane Hookom
Microsoft Access MVP


Will G said:
thanks for the reponse Rick,

yes i do have some code that defines, strwhere and strdescrip. Do you think
that's the problem? i went step by step on that code and i dont find anything
that might cause this problem. i substituted [OrderQty], the criteria, by a
number, say 1 or 2 and it prints fine, but when i put the OrderQty as a
number of pages criteria, it gives me that error. And i tried it without any
other code and it gives me the same error. at this point, i am thinking the
query or table that OrderQty comes from, but i dont see any bugg there
either. the code that i used , OrderQty with is bellow.


Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
Dim prm_sched As Control
Dim DBS As Database
Dim rst As Recordset


Set prm_sched = Me![sched]

If prm_sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")

'run through all selected items
For Each varItem In prm_sched.ItemsSelected
rst.AddNew
rst!OrderNo = prm_sched.Column(0)
rst!RequiredDate = prm_sched.Column(1)
rst!CustomerID = prm_sched.Column(2)
rst!ModelNumber = prm_sched.Column(3)
rst!Description = prm_sched.Column(4)
rst!OrderQty = prm_sched.Column(5)
rst!Comment = prm_sched.Column(6)
rst!Bfill = prm_sched.Column(7)
rst!Qfill = prm_sched.Column(8)
rst!Uphfill = prm_sched.Column(9)
rst!Ptfill = prm_sched.Column(10)
rst!Spring = prm_sched.Column(11)
rst!Label = prm_sched.Column(12)
rst!Size = prm_sched.Column(13)
rst!Needle = prm_sched.Column(14)
rst!Pattern = prm_sched.Column(15)
rst!Config = prm_sched.Column(16)
rst!Border = prm_sched.Column(17)
rst!Border = prm_sched.Column(18)
rst!FoamCore = prm_sched.Column(19)
rst!FoamEnc = prm_sched.Column(20)
rst.update
Next
' strDelim = "" 'Delimiter appropriate to field type.
strDoc = "rptProduction"
strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
'strDescrip = "Production Required: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

the other code that i used it in is as follow

Dim strProductID As String
Dim strCriteriaProductID As String
Dim strCriteriaOrderNo As String

If NewRecord Then
MsgBox "This record contains no data." _
, vbInformation, "Invalid Action"
Exit Sub
Else
strProductID = "rptProduction"
DoCmd.OpenReport strProductID, acViewPreview, , "[ID_1]='" &
Forms!frmProduction![ProductID] & "' And [OrderNo] = " &
Forms!frmProduction![OrderNo]
DoCmd.PrintOut , , , , [OrderQty], 0


End If

i dont know where this problem be. thanks for your help
--
need help


:

I assume that you have additional code that defines "strWhere" and
"strDescrip"?



--
Rick B



I am trying to print a report depending on a the field "OrderQty". i did
created the code with the print command and the criteria and it looks like
this:

DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

however, it give me an error saying, that "|" field is not found. i looked
through the code and tested the code, by putting 1 or 2 in place of
OrderQty
and it prints fine, that means to me that it's not the code and i looked
to
through the Query that the source of that Report and find no buggie field
with the name "|" and i looked in the report for the same field and i find
nothing...now i dont know where to look. Does anyone has any idea on where
am
going wrong with this.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

sorry for not being further clear on what i have and what want to do.
what i have: a list box on a for...list box contains items such as OrderNo,
ModelNumber, RequiredDate, Description, OrderQty...etc.....
or

List box
OrderNo|ModelNumber|RequiredDate|Description|OrderQty
4001 A3 04/17/07 ANY DESC 3
4001 A4 04/17/07 any desc1 2
4002 A3 04/17/07 ANY DESC 3
4003 A5 04/18/07 ANY DESCS 1

I select one or more of this items and click print.....i get a report
that contains does items on the list and more...........so i select 4001,
with the code that i have which is far from perfect or even good, i get all
4001s, but each on a different page. each order comes in a different page. if
i just,
DoCmd.OpenReport "rptProduction", acViewPreview, WhereCondition:=strWhere
i do get each order selected and the other specs on different page. but when
i docmd.printout,,,,quantity, it prints out according to the quantity on the
first page on the report, it ignore the other pages.

i am thinking that it's not having time to generage the other pages. if
there was a way to pause it before print?

sorry man, i am very new at this stuff and it's giving me hard time...thanks
though, below is my code, i hope it'll help:

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
Dim prm_sched As Control
Dim DBS As Database
Dim rst As Recordset
Dim quantity As Double

quantity = Me.sched.Column(5)

Set prm_sched = Me![sched]
If prm_sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")
'run through all selected items
For Each varItem In prm_sched.ItemsSelected
rst.AddNew
rst!OrderNo = prm_sched.Column(0)
rst!RequiredDate = prm_sched.Column(1)
rst!CustomerID = prm_sched.Column(2)
rst!ModelNumber = prm_sched.Column(3)
rst!Description = prm_sched.Column(4)
rst!OrderQty = prm_sched.Column(5)
rst!Comment = prm_sched.Column(6)
rst!Bfill = prm_sched.Column(7)
rst!Qfill = prm_sched.Column(8)
rst!Uphfill = prm_sched.Column(9)
rst!Ptfill = prm_sched.Column(10)
rst!Spring = prm_sched.Column(11)
rst!Label = prm_sched.Column(12)
rst!Size = prm_sched.Column(13)
rst!Needle = prm_sched.Column(14)
rst!Pattern = prm_sched.Column(15)
rst!Config = prm_sched.Column(16)
rst!Border = prm_sched.Column(17)
rst!Border = prm_sched.Column(18)
rst!FoamCore = prm_sched.Column(19)
rst!FoamEnc = prm_sched.Column(20)
rst.update
Next
rst.Fields("ProductionDate") = Me.Combo68
rst.Fields("Type") = Me.Combo72
rst.update
' strDelim = "" 'Delimiter appropriate to field type. See note 1.
strDoc = "rptProduction"
strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
'strDescrip = "Production Required: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If
'Omit the last argument for Access 2000 and earlier.
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , quantity
need help


Duane Hookom said:
I'm not sure how you can expect to print different quantities with only one
DoCmd.OpenReport...

Allen Browne has some solutions in his "Serious ... Reports" section at
http://www.allenbrowne.com/tips.html. Check out the one pertaining to
multiple quantities of labels.
--
Duane Hookom
Microsoft Access MVP


Will G said:
Hey Duane,

haft of the problem is fixed, thanks a lot.

what i wasnt doing is what you said, not specifying the control. i had to
declare a variable and called it Quantity and equaled it to
me.sched.column(5).....the sched is the list box on the form that contains
OrderQty at that column location. now it prints according to my first pick.
this is the new problem that came up. it prints now, according to the first
item selected from the multiselect box. cant get it to print, according to
each OrderQty from each selection. example of the problem......i pick order
number 4001 and since i have the criteria to be Order Number, so all Order
number 4001 will come out on different page of the report. but each 4001 has
it's own OrderQty, now it's printing according to the first 4001. i hope you
understand what just wrote, it's a little scrambled. i am just trying to be a
little clear.

thanks again.
--
need help


Duane Hookom said:
I still don't see where you expect to get OrderQty from. I would expect you
to set a variable or use something like Me.OrderQty if OrderQty is a field
from your form's record source or a control name on your form.

--
Duane Hookom
Microsoft Access MVP


:

thanks for the reponse Rick,

yes i do have some code that defines, strwhere and strdescrip. Do you think
that's the problem? i went step by step on that code and i dont find anything
that might cause this problem. i substituted [OrderQty], the criteria, by a
number, say 1 or 2 and it prints fine, but when i put the OrderQty as a
number of pages criteria, it gives me that error. And i tried it without any
other code and it gives me the same error. at this point, i am thinking the
query or table that OrderQty comes from, but i dont see any bugg there
either. the code that i used , OrderQty with is bellow.


Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
Dim prm_sched As Control
Dim DBS As Database
Dim rst As Recordset


Set prm_sched = Me![sched]

If prm_sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")

'run through all selected items
For Each varItem In prm_sched.ItemsSelected
rst.AddNew
rst!OrderNo = prm_sched.Column(0)
rst!RequiredDate = prm_sched.Column(1)
rst!CustomerID = prm_sched.Column(2)
rst!ModelNumber = prm_sched.Column(3)
rst!Description = prm_sched.Column(4)
rst!OrderQty = prm_sched.Column(5)
rst!Comment = prm_sched.Column(6)
rst!Bfill = prm_sched.Column(7)
rst!Qfill = prm_sched.Column(8)
rst!Uphfill = prm_sched.Column(9)
rst!Ptfill = prm_sched.Column(10)
rst!Spring = prm_sched.Column(11)
rst!Label = prm_sched.Column(12)
rst!Size = prm_sched.Column(13)
rst!Needle = prm_sched.Column(14)
rst!Pattern = prm_sched.Column(15)
rst!Config = prm_sched.Column(16)
rst!Border = prm_sched.Column(17)
rst!Border = prm_sched.Column(18)
rst!FoamCore = prm_sched.Column(19)
rst!FoamEnc = prm_sched.Column(20)
rst.update
Next
' strDelim = "" 'Delimiter appropriate to field type.
strDoc = "rptProduction"
strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
'strDescrip = "Production Required: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

the other code that i used it in is as follow

Dim strProductID As String
Dim strCriteriaProductID As String
Dim strCriteriaOrderNo As String

If NewRecord Then
MsgBox "This record contains no data." _
, vbInformation, "Invalid Action"
Exit Sub
Else
strProductID = "rptProduction"
DoCmd.OpenReport strProductID, acViewPreview, , "[ID_1]='" &
Forms!frmProduction![ProductID] & "' And [OrderNo] = " &
Forms!frmProduction![OrderNo]
DoCmd.PrintOut , , , , [OrderQty], 0


End If

i dont know where this problem be. thanks for your help
--
need help


:

I assume that you have additional code that defines "strWhere" and
"strDescrip"?



--
Rick B



I am trying to print a report depending on a the field "OrderQty". i did
created the code with the print command and the criteria and it looks like
this:

DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

however, it give me an error saying, that "|" field is not found. i looked
through the code and tested the code, by putting 1 or 2 in place of
OrderQty
and it prints fine, that means to me that it's not the code and i looked
to
through the Query that the source of that Report and find no buggie field
with the name "|" and i looked in the report for the same field and i find
nothing...now i dont know where to look. Does anyone has any idea on where
am
going wrong with this.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

You will either need to perform DoCmd.OpenReport multiple times or use the
information on Allen's site. Allen's information might be the solution if the
quantity you want to print is (or can be) a field in the report's record
source.
--
Duane Hookom
Microsoft Access MVP


Will G said:
sorry for not being further clear on what i have and what want to do.
what i have: a list box on a for...list box contains items such as OrderNo,
ModelNumber, RequiredDate, Description, OrderQty...etc.....
or

List box
OrderNo|ModelNumber|RequiredDate|Description|OrderQty
4001 A3 04/17/07 ANY DESC 3
4001 A4 04/17/07 any desc1 2
4002 A3 04/17/07 ANY DESC 3
4003 A5 04/18/07 ANY DESCS 1

I select one or more of this items and click print.....i get a report
that contains does items on the list and more...........so i select 4001,
with the code that i have which is far from perfect or even good, i get all
4001s, but each on a different page. each order comes in a different page. if
i just,
DoCmd.OpenReport "rptProduction", acViewPreview, WhereCondition:=strWhere
i do get each order selected and the other specs on different page. but when
i docmd.printout,,,,quantity, it prints out according to the quantity on the
first page on the report, it ignore the other pages.

i am thinking that it's not having time to generage the other pages. if
there was a way to pause it before print?

sorry man, i am very new at this stuff and it's giving me hard time...thanks
though, below is my code, i hope it'll help:

Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
Dim prm_sched As Control
Dim DBS As Database
Dim rst As Recordset
Dim quantity As Double

quantity = Me.sched.Column(5)

Set prm_sched = Me![sched]
If prm_sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")
'run through all selected items
For Each varItem In prm_sched.ItemsSelected
rst.AddNew
rst!OrderNo = prm_sched.Column(0)
rst!RequiredDate = prm_sched.Column(1)
rst!CustomerID = prm_sched.Column(2)
rst!ModelNumber = prm_sched.Column(3)
rst!Description = prm_sched.Column(4)
rst!OrderQty = prm_sched.Column(5)
rst!Comment = prm_sched.Column(6)
rst!Bfill = prm_sched.Column(7)
rst!Qfill = prm_sched.Column(8)
rst!Uphfill = prm_sched.Column(9)
rst!Ptfill = prm_sched.Column(10)
rst!Spring = prm_sched.Column(11)
rst!Label = prm_sched.Column(12)
rst!Size = prm_sched.Column(13)
rst!Needle = prm_sched.Column(14)
rst!Pattern = prm_sched.Column(15)
rst!Config = prm_sched.Column(16)
rst!Border = prm_sched.Column(17)
rst!Border = prm_sched.Column(18)
rst!FoamCore = prm_sched.Column(19)
rst!FoamEnc = prm_sched.Column(20)
rst.update
Next
rst.Fields("ProductionDate") = Me.Combo68
rst.Fields("Type") = Me.Combo72
rst.update
' strDelim = "" 'Delimiter appropriate to field type. See note 1.
strDoc = "rptProduction"
strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
'strDescrip = "Production Required: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If
'Omit the last argument for Access 2000 and earlier.
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , quantity
need help


Duane Hookom said:
I'm not sure how you can expect to print different quantities with only one
DoCmd.OpenReport...

Allen Browne has some solutions in his "Serious ... Reports" section at
http://www.allenbrowne.com/tips.html. Check out the one pertaining to
multiple quantities of labels.
--
Duane Hookom
Microsoft Access MVP


Will G said:
Hey Duane,

haft of the problem is fixed, thanks a lot.

what i wasnt doing is what you said, not specifying the control. i had to
declare a variable and called it Quantity and equaled it to
me.sched.column(5).....the sched is the list box on the form that contains
OrderQty at that column location. now it prints according to my first pick.
this is the new problem that came up. it prints now, according to the first
item selected from the multiselect box. cant get it to print, according to
each OrderQty from each selection. example of the problem......i pick order
number 4001 and since i have the criteria to be Order Number, so all Order
number 4001 will come out on different page of the report. but each 4001 has
it's own OrderQty, now it's printing according to the first 4001. i hope you
understand what just wrote, it's a little scrambled. i am just trying to be a
little clear.

thanks again.
--
need help


:

I still don't see where you expect to get OrderQty from. I would expect you
to set a variable or use something like Me.OrderQty if OrderQty is a field
from your form's record source or a control name on your form.

--
Duane Hookom
Microsoft Access MVP


:

thanks for the reponse Rick,

yes i do have some code that defines, strwhere and strdescrip. Do you think
that's the problem? i went step by step on that code and i dont find anything
that might cause this problem. i substituted [OrderQty], the criteria, by a
number, say 1 or 2 and it prints fine, but when i put the OrderQty as a
number of pages criteria, it gives me that error. And i tried it without any
other code and it gives me the same error. at this point, i am thinking the
query or table that OrderQty comes from, but i dont see any bugg there
either. the code that i used , OrderQty with is bellow.


Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String
Dim prm_sched As Control
Dim DBS As Database
Dim rst As Recordset


Set prm_sched = Me![sched]

If prm_sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If
Set DBS = CodeDb
Set rst = DBS.OpenRecordset("SELECT * FROM POhistory")

'run through all selected items
For Each varItem In prm_sched.ItemsSelected
rst.AddNew
rst!OrderNo = prm_sched.Column(0)
rst!RequiredDate = prm_sched.Column(1)
rst!CustomerID = prm_sched.Column(2)
rst!ModelNumber = prm_sched.Column(3)
rst!Description = prm_sched.Column(4)
rst!OrderQty = prm_sched.Column(5)
rst!Comment = prm_sched.Column(6)
rst!Bfill = prm_sched.Column(7)
rst!Qfill = prm_sched.Column(8)
rst!Uphfill = prm_sched.Column(9)
rst!Ptfill = prm_sched.Column(10)
rst!Spring = prm_sched.Column(11)
rst!Label = prm_sched.Column(12)
rst!Size = prm_sched.Column(13)
rst!Needle = prm_sched.Column(14)
rst!Pattern = prm_sched.Column(15)
rst!Config = prm_sched.Column(16)
rst!Border = prm_sched.Column(17)
rst!Border = prm_sched.Column(18)
rst!FoamCore = prm_sched.Column(19)
rst!FoamEnc = prm_sched.Column(20)
rst.update
Next
' strDelim = "" 'Delimiter appropriate to field type.
strDoc = "rptProduction"
strLabel = "rptProductionLabel"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
'strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
'strDescrip = "Production Required: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport "rptProduction", acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.PrintOut , , , , [OrderQty]

the other code that i used it in is as follow

Dim strProductID As String
Dim strCriteriaProductID As String
Dim strCriteriaOrderNo As String

If NewRecord Then
MsgBox "This record contains no data." _
, vbInformation, "Invalid Action"
Exit Sub
Else
strProductID = "rptProduction"
DoCmd.OpenReport strProductID, acViewPreview, , "[ID_1]='" &
Forms!frmProduction![ProductID] & "' And [OrderNo] = " &
Forms!frmProduction![OrderNo]
DoCmd.PrintOut , , , , [OrderQty], 0


End If

i dont know where this problem be. thanks for your help
--
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

print report according to value field 3
HELP PLEASE 7
List Box item as criteria 4
Add code to handle All jobs FROm dropdown list if None selected- 5
CrossPosat - Multiple Criteria in a List Box 2
Works, But Doesn't Filter 1
sttting report margins on print 1
Multiselect Listbox selections don't show up in report 9

Top