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 btnExportReports_DblClick(Cancel As Integer)

'Select which DDA Account will be exported to the C:\Program
Files\RECON\RNDI directory
Dim qry As String
Dim qryCboDDAAccount As Recordset

qry = "qryCboDDAAccount"
DoCmd.OpenQuery "qryCboDDAAccount"
Select Case qry

Case "BM2"
Call mBM2Export
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedBM2",
"C:\Program Files\RECON\RNDI\BM2.xls", True, ""
Case "BM20"
Call mBM20Export
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedBM20",
"C:\Program Files\RECON\RNDI\BM20.xls", True, ""
Case "CM"
Call mCMExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedCM",
"C:\Program Files\RECON\RNDI\CM.xls", True, ""
Case "CU"
Call mCUExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedCM",
"C:\Program Files\RECON\RNDI\CM.xls", True, ""
Case "DEC"
Call mDECExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedCM",
"C:\Program Files\RECON\RNDI\CM.xls", True, ""
Case "MABS"
Call mMABSExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedMABS",
"C:\Program Files\RECON\RNDI\MABS.xls", True, ""
Case "RBANK"
Call mRBANKExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedRBANK",
"C:\Program Files\RECON\RNDI\RBANK.xls", True, ""
Case "SF"
Call mSFExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedSF",
"C:\Program Files\RECON\RNDI\SF.xls", True, ""
Case "SF2"
Call mSF2Export
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedSF2",
"C:\Program Files\RECON\RNDI\SF2.xls", True, ""

End Select

DoCmd.Close '"qryCboDDAAccount"

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 btnExportReports_DblClick(Cancel As Integer)

'Select which DDA Account will be exported to the C:\Program
Files\RECON\RNDI directory
Dim qry As String
Dim qryCboDDAAccount As Recordset

qry = "qryCboDDAAccount"
DoCmd.OpenQuery "qryCboDDAAccount"
Select Case qry

Case "BM2"
Call mBM2Export
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedBM2",
"C:\Program Files\RECON\RNDI\BM2.xls", True, ""
Case "BM20"
Call mBM20Export
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedBM20",
"C:\Program Files\RECON\RNDI\BM20.xls", True, ""
Case "CM"
Call mCMExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedCM",
"C:\Program Files\RECON\RNDI\CM.xls", True, ""
Case "CU"
Call mCUExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedCM",
"C:\Program Files\RECON\RNDI\CM.xls", True, ""
Case "DEC"
Call mDECExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedCM",
"C:\Program Files\RECON\RNDI\CM.xls", True, ""
Case "MABS"
Call mMABSExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedMABS",
"C:\Program Files\RECON\RNDI\MABS.xls", True, ""
Case "RBANK"
Call mRBANKExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedRBANK",
"C:\Program Files\RECON\RNDI\RBANK.xls", True, ""
Case "SF"
Call mSFExport
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedSF",
"C:\Program Files\RECON\RNDI\SF.xls", True, ""
Case "SF2"
Call mSF2Export
'DoCmd.TransferSpreadsheet acExport, 8, "qryCombinedSF2",
"C:\Program Files\RECON\RNDI\SF2.xls", True, ""

End Select

DoCmd.Close '"qryCboDDAAccount"

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_AfterUpdate()
DoCmd.TransferSpreadsheet acExport, 8, "qryCombined" & cboAcct, _
"C:\Program Files\RECON\RNDI\" & 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_AfterUpdate()
DoCmd.TransferSpreadsheet acExport, 8, "qryCombined" & cboAcct, _
"C:\Program Files\RECON\RNDI\" & 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