By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

access 97: select case & combo box problem

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

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
Nov 12 '05 #2

P: n/a
> 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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.