help.. dirk goldgar or someone familiar with Dev Ashish search

[misschanda]

I seen Dirk Goldgar, answer a question based on the same program. (
http://www.accessmonster.com/Uwe/Forum.aspx/access/82753/Dev-Ashish-Search)

I got this code from http://www.mvps.org/access/forms/index.html that allows
form search. the form than, has a command that allows for export of the
filter information. my 1st question is.. that when asked to choose location,
i
chose excel 3 for example... the file is not exporting to destination..and (2)
how do you limit the list if tables and queries of mdb that is in the list
box. any help is appreciated

misschanda

Private Sub cmdExport_Click()
On Error GoTo ErrHandler
Dim arrCtl As Control
Dim intUbound As Integer
Dim intLbound As Integer
Dim intCount As Integer
Select Case cmdExport.Tag
Case "Choose"
intCount = -1
For Each arrCtl In Me.Controls
Select Case arrCtl.ControlType
Case acTextBox, acComboBox, acCheckBox, acListBox, acCommandButton
If arrCtl.Name <> "cmdExport" And arrCtl.Name <> "lstResult" Then
intCount = intCount + 1
ReDim Preserve arrCtls(0 To intCount)
With arrCtls(intCount)
.Name = arrCtl.Name
.Enabled = arrCtl.Enabled
End With
arrCtl.Enabled = False
End If
End Select
Next

With lstResult
.ColumnCount = 4
.ColumnWidths = "0,0,0"
.RowSourceType = "Value List"
.RowSource = "-1,-1,-1,Export Type," _
& "0,0,.xls,Excel 3," _
& "0,6,.xls,Excel 4," _
& "0,5,.xls,Excel 5," _
& "0,5,.xls,Excel 7," _
& "0,8,.xls,Excel 97," _
& "0,2,.wk1,Lotus WK1," _
& "0,3,.wk3,Lotus WK3," _
& "0,7,.wk4,Lotus WK4," _
& "0,4,.wj2,Lotus WJ2 (Japanese)," _
& "1,2,.txt,Delimited Text," _
& "1,8,.html,HTML"
'& "1,3,.txt,Fixed Length Text,"
.Selected(1) = True
End With
Label16.Caption = "Select ..."
cmdExport.Tag = "Export"
Case "Export"
If MsgBox("Are you sure you want to export this query", vbYesNo +
vbQuestion) <> vbNo Then
Call ExportRoutine
End If
intLbound = LBound(arrCtls)
intUbound = UBound(arrCtls)
For intCount = intLbound To intUbound
With arrCtls(intCount)
Me(.Name).Enabled = .Enabled
End With
Next
Label16.Caption = "Search Results"
cmdExport.Tag = "Choose"
lstResult.ColumnWidths = ""
If Me.chkAutoBuildSQL = True Then Call sBuildSQL
End Select
ExitHere:
Exit Sub
ErrHandler:
If Err = 2448 Then Resume Next
Resume ExitHere
End Sub

[Dirk Goldgar]

"misschanda" <u36612@uwe> wrote in message news:8337047de6654@uwe...

>I seen Dirk Goldgar, answer a question based on the same program. (
> http://www.accessmonster.com/Uwe/Forum.aspx/access/82753/Dev-Ashish-Search)
>
>
> I got this code from http://www.mvps.org/access/forms/index.html that
> allows
> form search. the form than, has a command that allows for export of the
> filter information. my 1st question is.. that when asked to choose
> location,
> i
> chose excel 3 for example... the file is not exporting to destination..and
> (2)
> how do you limit the list if tables and queries of mdb that is in the list
> box. any help is appreciated

Hmm, I just tested it and the export to Excel 3 format worked for me. So I
can't explain the problem you're having there, at least not yet. As far as
I can tell, the error-handling is set up in such a way that an error in the
export won't be reported by a message of any kind. You could set a
breakpoint in the code of the ExportRoutine function and step through to see
if an error is raised, and find out what it is by using the Immediate window
to interrogate Err.Number and Err.Description.

As for limiting the list of what appears in the tables/queries list box,
that is determined by the list box's RowSource query (on the Data tab of its
property sheet). This is set to the following query, which extracts from
the system table "MSysObjects":

SELECT
[MsysObjects].[Name] AS ObjectName,
IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE
(((Left$([Name],1))<>"~") And ((Left$([Name],4))<>"Msys")
And
(([MsysObjects].[Type])=1 Or
([MsysObjects].[Type])=5 Or
([MsysObjects].[Type])=6)
And
(([MsysObjects].[Flags])=2097152 Or
([MsysObjects].[Flags])=128 Or
([MsysObjects].[Flags])=0 Or
([MsysObjects].[Flags])=16))
ORDER BY [MsysObjects].[Name];

That query extracts non-system, non-hidden, non-temporary tables and
queries. I can't recall offhand exactly what the Flags specifications are,
though I could look it up.

If you want to restrict the listed objects by name, that would be pretty
easy. All you'd have to do is apply additional criteria to the [Name]
field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

[Tom Wickerath]

Hi Dirk,

> That query extracts non-system, non-hidden, non-temporary tables and
> queries. I can't recall offhand exactly what the Flags specifications are,
> though I could look it up.

I was curious on the flags, so I did some experimenting. Here's what I have
found so far:

Flag Description
0 Select query (visible)
8 Select query (hidden)

16 Crosstab query (visible)
24 Crosstab query (hidden)

32 Delete query (visible)
40 Delete query (hidden)

48 Update query (visible)
56 Update query (hidden)

64 Append query (visible)
72 Append query (hidden)

80 Make table query (visible)
88 Make table query (hidden)

96 Data Definition query (visible)
104 Data Definition query (hidden)

112 Pass through query (visible)
120 Pass through query (hidden)

128 Union query (visible)
136 Union query (hidden)

2097152 ?

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

[misschanda via AccessMonster.com]

thanks for your response.
I am still not able to use the export option command on this dev-ashish
search form..

so, how exactly do i get it to show me an error message..

thanks
misschanda

Dirk Goldgar wrote:
>>I seen Dirk Goldgar, answer a question based on the same program. (
>> http://www.accessmonster.com/Uwe/Forum.aspx/access/82753/Dev-Ashish-Search)

>[quoted text clipped - 9 lines]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200805/1