access 97: select case & combo box problem

[JMCN]

hello
i have your basic select case question. i created a combo box and save
it as a query. so whenever the user selects the value and clicks the
export button, the select case should then export the data. however,
it does not export the it. so then i converted the export macro to VB
and used "call" to call function but it did not work. what am i doing
wrong or more precisely what am i leaving out?

thanks in advance,
jung

here is the code:

Private Sub btnExportReport s_DblClick(Canc el As Integer)

'Select which DDA Account will be exported to the C:\Program
Files\RECON\RND I directory
Dim qry As String
Dim qryCboDDAAccoun t As Recordset

qry = "qryCboDDAAccou nt"
DoCmd.OpenQuery "qryCboDDAAccou nt"
Select Case qry

Case "BM2"
Call mBM2Export
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedBM2 ",
"C:\Program Files\RECON\RND I\BM2.xls", True, ""
Case "BM20"
Call mBM20Export
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedBM2 0",
"C:\Program Files\RECON\RND I\BM20.xls", True, ""
Case "CM"
Call mCMExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedCM" ,
"C:\Program Files\RECON\RND I\CM.xls", True, ""
Case "CU"
Call mCUExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedCM" ,
"C:\Program Files\RECON\RND I\CM.xls", True, ""
Case "DEC"
Call mDECExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedCM" ,
"C:\Program Files\RECON\RND I\CM.xls", True, ""
Case "MABS"
Call mMABSExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedMAB S",
"C:\Program Files\RECON\RND I\MABS.xls", True, ""
Case "RBANK"
Call mRBANKExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedRBA NK",
"C:\Program Files\RECON\RND I\RBANK.xls", True, ""
Case "SF"
Call mSFExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedSF" ,
"C:\Program Files\RECON\RND I\SF.xls", True, ""
Case "SF2"
Call mSF2Export
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedSF2 ",
"C:\Program Files\RECON\RND I\SF2.xls", True, ""

End Select

DoCmd.Close '"qryCboDDAAcco unt"

End Sub

[Peter Miller]

On 19 Nov 2003 13:41:52 -0800, pi******@yahoo. fr (JMCN) wrote in
comp.databases. ms-access:

hello
i have your basic select case question. i created a combo box and save
it as a query. so whenever the user selects the value and clicks the
export button, the select case should then export the data. however,
it does not export the it. so then i converted the export macro to VB
and used "call" to call function but it did not work. what am i doing
wrong or more precisely what am i leaving out?

thanks in advance,
jung

here is the code:

Private Sub btnExportReport s_DblClick(Canc el As Integer)

'Select which DDA Account will be exported to the C:\Program
Files\RECON\RN DI directory
Dim qry As String
Dim qryCboDDAAccoun t As Recordset

qry = "qryCboDDAAccou nt"
DoCmd.OpenQuer y "qryCboDDAAccou nt"
Select Case qry

Case "BM2"
Call mBM2Export
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedBM2 ",
"C:\Program Files\RECON\RND I\BM2.xls", True, ""
Case "BM20"
Call mBM20Export
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedBM2 0",
"C:\Program Files\RECON\RND I\BM20.xls", True, ""
Case "CM"
Call mCMExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedCM" ,
"C:\Program Files\RECON\RND I\CM.xls", True, ""
Case "CU"
Call mCUExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedCM" ,
"C:\Program Files\RECON\RND I\CM.xls", True, ""
Case "DEC"
Call mDECExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedCM" ,
"C:\Program Files\RECON\RND I\CM.xls", True, ""
Case "MABS"
Call mMABSExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedMAB S",
"C:\Program Files\RECON\RND I\MABS.xls", True, ""
Case "RBANK"
Call mRBANKExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedRBA NK",
"C:\Program Files\RECON\RND I\RBANK.xls", True, ""
Case "SF"
Call mSFExport
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedSF" ,
"C:\Program Files\RECON\RND I\SF.xls", True, ""
Case "SF2"
Call mSF2Export
'DoCmd.Transfer Spreadsheet acExport, 8, "qryCombinedSF2 ",
"C:\Program Files\RECON\RND I\SF2.xls", True, ""

End Select

DoCmd.Close '"qryCboDDAAcco unt"

End Sub

Huh?

Why not just use a form with a combobox that has the query as its
recordsource. Then, in the afterupdate even procedure of the combobox
(let's pretend its called 'cboAcct'), just put:

Private Sub cboAcct_AfterUp date()
DoCmd.TransferS preadsheet acExport, 8, "qryCombine d" & cboAcct, _
"C:\Program Files\RECON\RND I\" & cboAcct & ".xls", True, ""
end sub

Wouldn't that be a hell of a lot easier?

Peter Miller
_______________ _______________ _______________ _______________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[JMCN]

> Huh?

Why not just use a form with a combobox that has the query as its
recordsource. Then, in the afterupdate even procedure of the combobox
(let's pretend its called 'cboAcct'), just put:

Private Sub cboAcct_AfterUp date()
DoCmd.TransferS preadsheet acExport, 8, "qryCombine d" & cboAcct, _
"C:\Program Files\RECON\RND I\" & cboAcct & ".xls", True, ""
end sub

Wouldn't that be a hell of a lot easier?

Peter Miller
_______________ _______________ _______________ _______________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051 Wouldn't that be a hell of a lot easier?

good point peter. thanks for the suggestion. i just figured that the
select case would have been an answer since the users would select it
by the combo box but i guess not. thanks again.
jung