How to turn Code Into Function

[Ken Snell [MVP]]

Ok - I think I am back on board now! < g >

Let's try this:

'Enter this without change:
Dim Q As QueryDef, db As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

Set ctl = Me![ClassOfTradeChosen]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

Set db = CurrentDb()
Set Q = db.QueryDefs("PrintDialogClassOfTradeLBqry")

If Len(Criteria) = 0 Then
Q.SQL = "Select * From [ClassOfTradetbl]"
Else
Q.SQL = "Select * From [ClassOfTradetbl] Where [ClassOfTrade] In (" &
Criteria & ");"
End If

Set Q = Nothing
Criteria = ""
Set ctl = Me![PlantChosen]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

Set Q = db.QueryDefs("PrintDialogPlantChosenLBqry")

If Len(Criteria) = 0 Then
Q.SQL = "Select * From [Planttbl]"
Else
Q.SQL = "Select * From [Planttbl] Where [Plnt] In (" & Criteria & ");"
End If

Set Q = Nothing
db.Close
Set db = Nothing



--

Ken Snell
<MS ACCESS MVP>

Ken, I have two codes running on two command buttons. One updates a query
after the user chooses selections from a list box. The other does the
same
thing, updating its own query. They both work separately and they both
update
their own queries. When I tried to combine them into one code (but still
two
queries to update), I failed.

The two queries and the report all work, no problem. The queries are used
to
filter the report. This all works. All I want to do is make these two
codes
work on one command button instead of two. Once the code works, I will
put the
code on the print preview and print command buttons, adding a line to do
the
macro (as the user actually chooses one of ten or so reports).

Just in case you were getting confused, this is why I was bewildered that
it
was so difficult to get two working codes to work as one. I initially
thought
to make functions of the two codes (thus the subject line we are using).
But
than I thought it would be best to just make one code to do the job of the
two.
No, I do not want one query. I want the code to update the two queries
alreadly working.

I hope this is clear. The two codes that do work are below.

Thanks, and sorry for confusing you. It certainly isn't in my best
interest to
waste anyones time here and I deeply appreciate your perserverence.

<Can you desribe what you want:
(1) one or two queries?
(2) what do you do with the resulting one or two queries?
(3) what is the purpose of the stored queries? are you opening a
report>

1. Two queries.
2. The queries already work in a line of queirs that filter reports.
3. The purpose is to filter reports. I will add a line to open a report
at the
end of the query. A Docmd to do a macro.

Matt


_______________________________________

Code 1 is this (on the click of a command button):

'Enter this without change:
Dim Q As QueryDef, db As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

Set ctl = Me![ClassOfTradeChosen]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

Set db = CurrentDb()
Set Q = db.QueryDefs("PrintDialogClassOfTradeLBqry")

If Len(Criteria) = 0 Then
Q.SQL = "Select * From [ClassOfTradetbl]"
Else
Q.SQL = "Select * From [ClassOfTradetbl] Where [ClassOfTrade] In(" &
Criteria &
");"
End If
______________________________________________________

Code 2 is this (on the click of another command button):

Dim Q As QueryDef, db As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

Set ctl = Me![PlantChosen]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

Set db = CurrentDb()
Set Q = db.QueryDefs("PrintDialogPlantChosenLBqry")

If Len(Criteria) = 0 Then
Q.SQL = "Select * From [Planttbl]"
Else
Q.SQL = "Select * From [Planttbl] Where [Plnt] In(" & Criteria & ");"
End If

____________________________________

 

[DOYLE60]

Thanks for a wonderful xmas present, Ken. Except for some bad wrapping (on one
line), it was very thoughtful.

Seriously, I think that day or two break where I missed one of your responses
threw us both off a bit.

Thanks for persevering. I really appreciate it.

Matt

 

[Ken Snell [MVP]]

You're welcome. Sorry it took so long to get right!