Does not work for 1-9 1

[melaniews]

I copied the following code from another post (Bob Scriver created the code). It is working (well sort of working) in a list box with multiselect set to simple. The problem is that it only works for values that are 10 and above. When I select any of the values 1 through 9 I get nothing in the report. Or if I select 3 and 12 I only get 12.

Any help is appreciated.


Public Function ViewCriteriaResults()
Dim ctlList As Control, varItem As Variant, strCollect As String

'Return control object variable pointing to the list box.
Set ctlList = Forms![criteria]![lstCriteria]
strCollect = Empty

'enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
'Create a list of the values of the bound columns of the listbox selection.
If strCollect = Empty Then
strCollect = " or " & CStr(ctlList.ItemData(varItem))
Else: strCollect = strCollect & " or " & CStr(ctlList.ItemData(varItem))
End If

'Debug.Print strCollect
Next varItem

ViewCriteriaResults = strCollect & " or "
'debug.print ViewCriteriaResults


End Function


TIA,
Melanie

 

[dhookom]

Before we go any further:
1) do you intend to edit these records or just view them?
2) is your application divided into a front-end and back-end

Duane
MS Access MVP

 

[melaniews]

1) view only, export to text files
2) I'm using linked tables to tables in SQL Server. When I linked to them, they all came up with names like dbo_NAMEOFTABLE



Melanie

 

[dhookom]

Create a query named "qselInterests" with a sql of:
SELECT DISTINCT INDIVIDUAL_ID from dbo_INTEREST;

Create a query with dbo_INDIVIDUALS joined to qselInterests using the INDIVIDUAL_ID fields. Save this query as "qselInterestedIndividuals" and use it as the record source for your form.

Then change your code to:

Private Sub cmdEmailOnly_Click()

Dim strSQL As String
Dim strOldSQL As String
Dim strWhere As String
strSQL = "SELECT DISTINCT INDIVIDUAL_ID " & _
"FROM dbo_INTEREST WHERE 1 = 1 "
strSQL = strSQL & BuildIn(Me.lboTInterest_ID)
strOldSQL = ChangeSQL("qselInterests", strSQL)
MsgBox strSQL 'take a look

DoCmd.OpenForm "frmEmailResults", acFormDS
DoCmd.Maximize

End Sub

Duane
MS Access MVP

 

[melaniews]

Thanks Duane.

Looks like the VB code is working to a point. The function is giving me an error. Compile error: user-defined type not defined.

Function ChangeSQL(pstrQuery As String, pstrSQL As String) As String
Dim db As DAO.Database <=== ERROR HERE
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQuery)
ChangeSQL = qd.SQL
qd.SQL = pstrSQL
Set qd = Nothing
Set db = Nothing
End Function

TIA,
Melanie

 

[dhookom]

While in a module select Tools|References and then find and check a reference to &quot;Microsoft DAO ...&quot;


Duane
MS Access MVP

 

[melaniews]

Wow it works. Thank you so much. I've been working on this for days and have tried too many different approaches to mention.

Now to modify it for my other queries. More fun... I'll have to learn a little bit more about what it is doing. But for now I'm happy to have this part of it working.

Thanks again.

Melanie