List Box item as criteria

[bigwillno2 via AccessMonster.com]

Hello, if anyone can help with this will be greatly appreciated.

i have a big problem with my code and list box.

Query: qrProduction............ModelNumber, OrderNo, RequiredDate, CustomerID,
Desc, OrderQty, Spring, Border, Panel, So on......... for every Model Number
there is a Spring, Border, Panel so on...this are the items that we Order
from Vendors.

Goal: to produce a Purchase Order for a company.

Listbox: sched has a rowsource of qrProduction
OrderNo| RequiredDate| CustomerID | ModelNumber | Desc | OrderQty
011 04/27/07 BBM G200M ANY
3
011 04/27/07 BBM W100M ANY
2
012 04/27/07 WMD G200M ANY
3

Report: PO has a rowsource of qrProduction


i am not very experienced in this, but i gathered some code to have the
multiselect list work and produce that report. i currently have OrderNo, an
item in the listbox, as the only criteria to the report printout. i like to
have the criteria by every line selection, and not an item only.

Problem: i know that code bellow is set to have just OrderNo as the criteria
strWhere, i am having difficulties having the whole line being the criteria.
i want it by selection, not by OrderNo. Can anyone help with this.....?

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
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If
DoCmd.OpenReport "rptProduction", acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip
Resp = MsgBox("Print??????", vbYesNo)
If Resp = vbYes Then
DoCmd.PrintOut
Else
DoCmd.OpenReport "rptProduction", acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip
End If

 

[Guest]

I am no expert either, but if I were doing it, I would use the Report's Query
and just have as many criteria as I needed. You can type in your criteria or
use combo boxes on your form.

If you need help setting up the query or combo boxes I can help.

You can post any questions here or email me at pwood57 at gmail dot com.

Hunter57
http://churchmanagementsoftware.googlepages.com

Hello, if anyone can help with this will be greatly appreciated.

i have a big problem with my code and list box.

Query: qrProduction............ModelNumber, OrderNo, RequiredDate, CustomerID,
Desc, OrderQty, Spring, Border, Panel, So on......... for every Model Number
there is a Spring, Border, Panel so on...this are the items that we Order
from Vendors.

Goal: to produce a Purchase Order for a company.

Listbox: sched has a rowsource of qrProduction
OrderNo| RequiredDate| CustomerID | ModelNumber | Desc | OrderQty
011 04/27/07 BBM G200M ANY
3
011 04/27/07 BBM W100M ANY
2
012 04/27/07 WMD G200M ANY
3

Report: PO has a rowsource of qrProduction


i am not very experienced in this, but i gathered some code to have the
multiselect list work and produce that report. i currently have OrderNo, an
item in the listbox, as the only criteria to the report printout. i like to
have the criteria by every line selection, and not an item only.

Problem: i know that code bellow is set to have just OrderNo as the criteria
strWhere, i am having difficulties having the whole line being the criteria.
i want it by selection, not by OrderNo. Can anyone help with this.....?

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
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If
DoCmd.OpenReport "rptProduction", acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip
Resp = MsgBox("Print??????", vbYesNo)
If Resp = vbYes Then
DoCmd.PrintOut
Else
DoCmd.OpenReport "rptProduction", acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip
End If

 

[bigwillno2 via AccessMonster.com]

i do need help setting up the query with the criteria......thanks...for the
help

I am no expert either, but if I were doing it, I would use the Report's Query
and just have as many criteria as I needed. You can type in your criteria or
use combo boxes on your form.

If you need help setting up the query or combo boxes I can help.

You can post any questions here or email me at pwood57 at gmail dot com.

Hunter57
http://churchmanagementsoftware.googlepages.com

Hello, if anyone can help with this will be greatly appreciated.

[quoted text clipped - 67 lines]

OpenArgs:=strDescrip
End If

 

[Guest]

Hi bigwillno2,

Here is a sample query based on the information you provided. You can edit
it, then copy and past it into the SQL view of the query.

Since I do not know what fields are in which tables I included tablenames
with the fields just to be sure. This is written as if you want to enter in
a parameter for everything except the OrderNo. If there is some field you do
not need a parameter for, just do not include it in the WHERE statement.

SELECT mytablename.OrderNo, mytablename.RequiredDate,
mytablename.CustomerID, mytablename.ModelNumber, mytablename.Desc,
mytablename.OrderQty
FROM mytablename, myothertablename (if you have one)
WHERE mytablename.RequiredDate = [Enter ReqiredDate] AND
mytablename.CustomerID=[Enter CustomerID] AND mytablename.ModelNumber=[Enter
ModelNumber] AND mytablename.Desc=[Enter Desc] AND
mytablename.OrderQty=[OrderQty];

When you run your query, for each Parameter a dialog box will pop up telling
you what to enter. Also, dates can be troublesome in Access. Be sure to
enter the date in the proper format. Most likely it will be like 4/26/2007.
If it doesn't work at first, don't be discouraged. There is a way to find
out exactly which Parameter is causing the problem.

To use this with your report, save the query and put the query name as the
RecordSource under the Data tab in your Report's Property Sheet.

Hunter57
Just huntin' for the right data.
http://churchmanagementsoftware.googlepages.com

i do need help setting up the query with the criteria......thanks...for the
help

I am no expert either, but if I were doing it, I would use the Report's Query
and just have as many criteria as I needed. You can type in your criteria or
use combo boxes on your form.

If you need help setting up the query or combo boxes I can help.

You can post any questions here or email me at pwood57 at gmail dot com.

Hunter57
http://churchmanagementsoftware.googlepages.com

Hello, if anyone can help with this will be greatly appreciated.

[quoted text clipped - 67 lines]

OpenArgs:=strDescrip
End If

 

[Guest]

Hi bigwillno2,

Here is a sample query based on the information you provided. You can edit
it, then copy and past it into the query SQL view.

Since I do not know what fields are in which tables I included tablenames
with the fields just to be sure. This is written as if you want to enter in
a parameter for everything except the OrderNo. If there is some field you do
not need a parameter for, just do not include it in the WHERE statement.

SELECT mytablename.OrderNo, mytablename.RequiredDate,
mytablename.CustomerID, mytablename.ModelNumber, mytablename.Desc,
mytablename.OrderQty
FROM mytablename, myothertablename (if you have one)
WHERE mytablename.RequiredDate = [Enter ReqiredDate] AND
mytablename.CustomerID=[Enter CustomerID] AND mytablename.ModelNumber=[Enter
ModelNumber] AND mytablename.Desc=[Enter Desc] AND
mytablename.OrderQty=[OrderQty];

When you run your query, for each Parameter a dialog box will pop up telling
you what to enter. Also, dates can be troublesome in Access. Be sure to
enter the date in the proper format. Most likely it will be like 4/26/2007.
If it doesn't work at first, don't be discouraged. There is a way to find
out exactly which Parameter is causing the problem.

To use this with your report, save the query and put the query name as the
RecordSource under the Data tab in your Report's Property Sheet.

Hunter57
Just huntin' for the right data.
http://churchmanagementsoftware.googlepages.com

i do need help setting up the query with the criteria......thanks...for the
help

I am no expert either, but if I were doing it, I would use the Report's Query
and just have as many criteria as I needed. You can type in your criteria or
use combo boxes on your form.

If you need help setting up the query or combo boxes I can help.

You can post any questions here or email me at pwood57 at gmail dot com.

Hunter57
http://churchmanagementsoftware.googlepages.com

Hello, if anyone can help with this will be greatly appreciated.

[quoted text clipped - 67 lines]

OpenArgs:=strDescrip
End If