Export Lookup Values and not their IDs to Excel

[RECrerar]

Hi,

My database contains a lot of information on projects that users may want to compare. To predict every comparism that the users might want and design reports for all of these is impractical and so what I am trying to do is create a series of simple reports that work for certain data type comparisms and then have an option that if the comparism they require is too complex for the database, to export the information to Excel and let them play around with it there.

For the export I have a multi-select listbox containing all possible fields. The user selects as may as they are interested in and then clicks a button creates a query of these values and then exports them to Excel.

the problem is, that the majority of the fields in my query contian ID numbers rather than actual words that makes sense to an observer.

What is the best way to get Access to export the values in the IDs?

All the values corresponding to the IDs are held in one table, tblCodeValues.

The code I am using at the moment is as follows:

Private Sub Command2_Click()
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strFullSQL As String
Dim varSelected As Variant

strSelect = "SELECT "
' Select the desired fields
For Each varSelected In Me.lsSubCriteria.ItemsSelected
    strSelect = strSelect & "[" & Me.lsSubCriteria.Column(1, varSelected) & "], "
Next varSelected
If strSelect = "SELECT " Then
    MsgBox "Please Select at least 1 item"
    Exit Sub
Else
    strSelect = Left(strSelect, Len(strSelect) - 2)
End If
 
strFrom = " FROM qryFiltered "
 
'Apply the filter from the previous form
Dim recordset As DAO.recordset
Set recordset = CurrentDb.OpenRecordset("tblTempFlexibleFilter", dbOpenDynaset)
strWhere = "WHERE "
On Error GoTo ErrRecordSet
recordset.MoveFirst
Do Until recordset.EOF
    strWhere = strWhere & recordset("FilterWhereClause").Value & " AND "
    recordset.MoveNext
Loop
recordset.Close
Set recordset = Nothing
 
ErrRecordSet:
If Err.Number <> 0 Then
    strWhere = "WHERE TRUE"
    Err.Clear
Else
    If Mid$(strWhere, Len(strWhere) - 4, Len(strWhere)) = " AND " Then
        strWhere = Left$(strWhere, Len(strWhere) - 5)
    End If
End If
 
strFullSQL = strSelect & strFrom & strWhere
Dim qdfNew As DAO.QueryDef
 
'  now create a reusable stored query def
On Error Resume Next
                                    '  is run for the first time
With CurrentDb              '  it would be better to check to see if the
                                    '  querydef exists and then delete it
    .QueryDefs.Delete ("TempFilterQuery")
                                    '  createquerydef command line follows
    Set qdfNew = .CreateQueryDef("TempFilterQuery", strFullSQL)
    .Close
End With

On Error GoTo 0
DoCmd.TransferSpreadsheet acExport, , "TempFilterQuery", "c:/Test.xls"

End Sub

I found this thread that suggests using a maketable query containing the values rather than the IDs, but I'm not sure how to do this either
http://www.access-programmers.co.uk/forums/showthread.php?t=74782

PS. I know the error trapping is rather poor right now, that will be sorted out in time

Any help would really be appreciated.

Thanks, Robyn