Answer: Excel Export, Query Defs, SQL IN criteria
in [Help]
|
Prev: fDialog - Opens at a User defined Folder
Next: Running different queries contained in a single query object from a form
From: christianlott1 on 30 Jun 2010 13:03 I searched a few days to come up with all these answers, so I'd like to share my knowledge. I wanted to export to excel using multiple criteria based on either region number or county name (text) using a list box to select which. My goal was to not embed any plain text sql. For some reason the query def wouldn't work for me, maybe because I was using - In([qryCriteria]). Instead I set up two single field tables (jtCounty, jtRegion). These are emptied then updated from the list box selections. Two make table queries are then used - one for region, one for county since the fields are different types (number, text). The table (jtCounty or jtRegion) is joined to the main table where I'm getting all my info. Procedure - 1. Select list rowsource to populate lstMain. I used two buttons: cmdByRegion, cmdByCounty. 2. Select items from lstMain. 3. Add to criteria list (lstMainSelected) with the cmdAddToSelected or clear both lists with cmdClearSelected. 4. Finally, use cmdExportExcel to create the spreadsheet. The make table queries create a table called tblExcelExport. I use Ken Getz's module for the Save As dialog box: http://www.mvps.org/access/api/api0001.htm ----------------------------------------------------------------------- Option Compare Database Public strFilterBy As String Private Sub cmdAddToSelected_Click() Dim i As Integer Dim strCriteria As String EmptyTable Dim db As DAO.Database Dim rst As DAO.Recordset Set db = CurrentDb If Me!lstMain.RowSource = "qLkpRegion" Then Set rst = db.OpenRecordset("jtRegion") Else Set rst = db.OpenRecordset("jtCounty") End If Set lst1 = Me!lstMain Set lst2 = Me!lstMainSelected lst2.RowSource = "" With lst1 For i = 0 To .ListCount - 1 If (.Selected(i)) <> 0 Then lst2.AddItem .ItemData(i) rst.AddNew rst!field1 = .ItemData(i) rst.Update End If Next End With End Sub Private Sub cmdClearSelected_Click() Dim varItem As Variant Me!lstMainSelected.RowSource = "" For Each varItem In Me!lstMain.ItemsSelected Me!lstMain.Selected(varItem) = False Next End Sub Private Sub cmdExportExcel_Click() On Error Resume Next Dim db As DAO.Database Dim strFilter As String Dim strInputFileName As String Set db = DBEngine(0)(0) db.Execute "DROP TABLE tblExcelExport;" If Me!lstMain.RowSource = "qLkpRegion" Then DoCmd.OpenQuery "qryExcelExport_Region" Else DoCmd.OpenQuery "qryExcelExport_County" End If strFilter = ahtAddFilterItem(strFilter, "Excel File (*.xls)", "*.xls") strInputFileName = ahtCommonFileOpenSave( _ Filter:=strFilter, _ OpenFile:=False, _ DialogTitle:="Save File As...", _ Flags:=ahtOFN_HIDEREADONLY) If Len(strInputFileName) <> 0 Then DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblExcelExport", strInputFileName, True End If End Sub Private Sub cmdByRegion_Click() Me!lstMain.RowSource = "qLkpRegion" End Sub Private Sub cmdByCounty_Click() Me!lstMain.RowSource = "qLkpCounty" End Sub Private Sub EmptyTable() Dim db As DAO.Database Dim strSql As String Set db = DBEngine(0)(0) If Me!lstMain.RowSource = "qLkpRegion" Then strSql = "DELETE FROM jtRegion;" Else strSql = "DELETE FROM jtCounty;" End If db.Execute strSql, dbFailOnError End Sub
From: Douglas J. Steele on 30 Jun 2010 13:48 Glad you got it working! One enhancement you might consider is to put jtCounty and jtRegion into a temporary database, so that you can avoid the bloating that will occur from repeated deleting and adding the tables. Tony Toews has an example at http://www.granite.ab.ca/access/temptables.htm -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) <christianlott1(a)yahoo.com> wrote in message news:330ff726-e59e-4d26-a5f1-bd6f3b063e0d(a)t10g2000yqg.googlegroups.com... >I searched a few days to come up with all these answers, so I'd like > to share my knowledge. > > I wanted to export to excel using multiple criteria based on either > region number or county name (text) using a list box to select which. > My goal was to not embed any plain text sql. > > For some reason the query def wouldn't work for me, maybe because I > was using - In([qryCriteria]). > > Instead I set up two single field tables (jtCounty, jtRegion). These > are emptied then updated from the list box selections. > > Two make table queries are then used - one for region, one for county > since the fields are different types (number, text). The table > (jtCounty or jtRegion) is joined to the main table where I'm getting > all my info. > > Procedure - > > 1. Select list rowsource to populate lstMain. I used two buttons: > cmdByRegion, cmdByCounty. > > 2. Select items from lstMain. > > 3. Add to criteria list (lstMainSelected) with the cmdAddToSelected or > clear both lists with cmdClearSelected. > > 4. Finally, use cmdExportExcel to create the spreadsheet. > > The make table queries create a table called tblExcelExport. I use Ken > Getz's module for the Save As dialog box: > > http://www.mvps.org/access/api/api0001.htm > > > ----------------------------------------------------------------------- > Option Compare Database > Public strFilterBy As String > > Private Sub cmdAddToSelected_Click() > Dim i As Integer > Dim strCriteria As String > > EmptyTable > > Dim db As DAO.Database > Dim rst As DAO.Recordset > > Set db = CurrentDb > > If Me!lstMain.RowSource = "qLkpRegion" Then > Set rst = db.OpenRecordset("jtRegion") > Else > Set rst = db.OpenRecordset("jtCounty") > End If > > Set lst1 = Me!lstMain > Set lst2 = Me!lstMainSelected > > lst2.RowSource = "" > > With lst1 > For i = 0 To .ListCount - 1 > If (.Selected(i)) <> 0 Then > lst2.AddItem .ItemData(i) > rst.AddNew > rst!field1 = .ItemData(i) > rst.Update > End If > Next > End With > > End Sub > > Private Sub cmdClearSelected_Click() > Dim varItem As Variant > > Me!lstMainSelected.RowSource = "" > > For Each varItem In Me!lstMain.ItemsSelected > Me!lstMain.Selected(varItem) = False > Next > > End Sub > > Private Sub cmdExportExcel_Click() > On Error Resume Next > Dim db As DAO.Database > Dim strFilter As String > Dim strInputFileName As String > > Set db = DBEngine(0)(0) > db.Execute "DROP TABLE tblExcelExport;" > > > If Me!lstMain.RowSource = "qLkpRegion" Then > DoCmd.OpenQuery "qryExcelExport_Region" > Else > DoCmd.OpenQuery "qryExcelExport_County" > End If > > strFilter = ahtAddFilterItem(strFilter, "Excel File (*.xls)", > "*.xls") > > strInputFileName = ahtCommonFileOpenSave( _ > Filter:=strFilter, _ > OpenFile:=False, _ > DialogTitle:="Save File As...", _ > Flags:=ahtOFN_HIDEREADONLY) > > If Len(strInputFileName) <> 0 Then > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, > "tblExcelExport", strInputFileName, True > End If > > End Sub > > Private Sub cmdByRegion_Click() > Me!lstMain.RowSource = "qLkpRegion" > End Sub > > Private Sub cmdByCounty_Click() > Me!lstMain.RowSource = "qLkpCounty" > End Sub > > Private Sub EmptyTable() > > Dim db As DAO.Database > Dim strSql As String > Set db = DBEngine(0)(0) > > If Me!lstMain.RowSource = "qLkpRegion" Then > strSql = "DELETE FROM jtRegion;" > Else > strSql = "DELETE FROM jtCounty;" > End If > > db.Execute strSql, dbFailOnError > > End Sub > >
From: christianlott1 on 30 Jun 2010 14:03 On Jun 30, 12:48 pm, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > Glad you got it working! > > One enhancement you might consider is to put jtCounty and jtRegion into a > temporary database, so that you can avoid the bloating that will occur from > repeated deleting and adding the tables. > > Tony Toews has an example athttp://www.granite.ab.ca/access/temptables.htm Thanks Douglas. The tblExcelExport is the one that gets deleted, the jtRegion/jtCounty get emptied. The tblExcelExport is not linked to any other table. jtRegion/jtCounty are only linked to the main table in the query, not the db design. Will it still bloat? Thanks.
From: Douglas J. Steele on 30 Jun 2010 14:28 Any time you delete rows from tables and repopulate them, you're going to get bloat. That's because Access doesn't actually return the space that was used unless you do a Compact and Repair of the database. How much bloat you'll get depends on how large the tables are and how frequently you perform the deletions and repopulations. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) <christianlott1(a)yahoo.com> wrote in message news:1d2dabb0-26db-4daa-b94d-c3b11887c075(a)c33g2000yqm.googlegroups.com... On Jun 30, 12:48 pm, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > Glad you got it working! > > One enhancement you might consider is to put jtCounty and jtRegion into a > temporary database, so that you can avoid the bloating that will occur > from > repeated deleting and adding the tables. > > Tony Toews has an example athttp://www.granite.ab.ca/access/temptables.htm Thanks Douglas. The tblExcelExport is the one that gets deleted, the jtRegion/jtCounty get emptied. The tblExcelExport is not linked to any other table. jtRegion/jtCounty are only linked to the main table in the query, not the db design. Will it still bloat? Thanks.
From: christianlott1 on 30 Jun 2010 14:54
On Jun 30, 1:28 pm, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > Any time you delete rows from tables and repopulate them, you're going to > get bloat. That's because Access doesn't actually return the space that was > used unless you do a Compact and Repair of the database. > > How much bloat you'll get depends on how large the tables are and how > frequently you perform the deletions and repopulations. Then maybe I should delete and create the tables each time I open the form? They are simple one field tables. Will this bloat as well? Thanks. |