Replies

Just for “fun”, as experiment, ran the attached procedure in VB Editor, with Normal.dot as the active VBA project:

InsertMacros 20, 1000

This resulted in creation of 20 new code modules, each with 1000 public sub procedures, for a total of 20,000 “macros” available from the Normal.dot template. Was able to save changes (resulting in a slow-loading Normal.dot file almost 6 MB in size). Resulting macros ran successfully from the Word user interface (the macro list took a while to load). So it does appear as noted by Hans, the MSKB article is apparently outdated.

NOTE: This was tested with a COPY of actual Normal template. Do not recommend add 20,000 macros to your actual Normal.dot template. Tested on system with Word 2000.

HTH

I would agree with John Hutchison – take your original estimate and double it. Testing & debugging may well take more time than the original design & development of the application. Joel Spolsky, author of “Joel on Software” and “User Interface Design for Programmers” (both recommended), maintains a web site on software development, Joel on Software. While not specifically related to Access programming, many of the articles should be of interest to anyone involved in the art, science (?), and business of software development, including Access developers. In reference to schedules and time estimates, see this article for some ideas:

Painless Software Schedules

HTH

FYI – Buried deep in the MSDN, amongst all the VB.NET and VB 2005 documentation, you can still find archaic, “legacy” VB 6.0 (aka VB98) documentation. Some links below:

VB 6.0 Code Basics

This article notes, “You can’t follow a line-continuation character with a comment on the same line. There are also some limitations as to where the line-continuation character can be used.” These “limitations” do not appear to articulated, though the MSDN VB 2005 version of this article How to: Break and Combine Statements in Code states: “Some limitations exist as to where the line-continuation character can be used, such as in the middle of an argument name. You can break an argument list with the line-continuation character, but the individual names of the arguments must remain intact.” This applies to “legacy” VB as well.

VB 6.0 Code Limitations

The latter article notes, “A single line of code can consist of up to 1023 bytes. Up to 256 blank spaces can precede the actual text on a single line, and no more than twenty-four line-continuation characters ( _) can be included in a single logical line.” Generally, code limitations in VB6 would also apply to VBA as used by Access & other MS Office apps. NOTE: The “Legacy” MSDN apparently doesn’t work too good if not using IE as browser – the left-hand hierarchy does not display properly. Recommend view VB6 articles in recent version of IE. The “new” MSDN (MSDN2) does display properly in non-IE (Firefox) browser.

HTH

If you really need to select columns in a datasheet form (or subform), you can use the Form SelTop, SelLeft, SelHeight, & SelWidth properties for this purpose. The attached database (A2K format) demonstrates how to use these properties to get or set the selected columns (or rows) in a datasheet form. Open one of the sample forms & use the custom toolbar (or custom right-click popup menu) to get or set selected columns. See code module for details. Note that the SetSelection function automatically includes all rows by using the form’s record count (plus one – apparently the column headers count) for SelHeight property. This can be modified as needed. The code also determines if active form is a form or subform, and uses CurrentView property to ensure form is in datasheet view (or error may occur).

Note: I’m unaware of any way to select non-contiguous columns either “manually” or using VBA. You can select only adjacent columns. Of courses datasheet columns can be rearranged if that is an issue.

Personally I’ve never had a need to do this in code, when exporting a datasheet I usually export the entire recordset that populates the form or subform.

HTH

I installed the IE 7.0 beta a few weeks ago w/no problems or bugs – a big improvement but still think Firefox does the tabs better (they can be re-arranged) & the “Organize Favorites” dialog is still less than useful. One interesting side-effect I’ve noticed is that (on my machine anyway) — Windows Explorer is suddenly far more responsive than before installing the beta. This machine is an installation of Win XP (SP-2) w/appx 3 years of accumulated gunk. Before installing the beta, when opening Windows Explorer (in Explorer view) & navigating to My Documents or clicking on folders like Program Files or the Windows folder, the machine would spin its wheels for what seemed a year & a day before finally expanding the folder tree hierarchy on left, & displaying files on right. Now when open Explorer, files & folders are displayed instantly, without delay, no matter how convoluted the folder/subfolder hierarchy or how many files they contain. The difference is not subtle — it is like nite & day. Installing the IE beta is only configuration change made during this time frame. Wonder if anyone else has observed this phenomenon or am I imagining things.

The attached file (Excel 2000 format spreadsheet) demonstrates one possible method for doing this by “capturing” the data in Access database from Excel using ADO in VBA to open a recordset and copying the records to into Excel. The function uses the Jet 4.0 OLEDB provider, which should be available even if Access not installed (if using Office 2K or later), as Jet is used by other applications. To test, open attached file (you’ll get standard “Macro” warning) & enter full path of Access database in cell A2. Then click button. Column B will be populated with table (and query) names (excluding system tables) then the code will loop thru each table listed and copy the records into a new worksheet. Hyperlinks to each new sheet are inserted, and a record count is added to column C. I tested this with the “Northwind” sample database & worked OK, with one exception: The Excel CopyFromRecordset function apparently doesn’t like binary/OLE datatypes as found in a few Northwind tables to (clutzily) store images, etc. so these tables were not copied properly. If your db doesn’t use this data type should be no problem. Must enter a valid path in A2. If any of the tables have more than 65536 records will encounter “issues” due to Excel row limitations. Caveat: None of my PC’s do NOT have Access installed so cannot state with total certitude this will work on system w/o Access, but AFAIK Access is not required to read data from Jet databases when using methods provided by ADO, ODBC, etc.

Don’t know if you can read Access data from MySQL via ODBC or other methods. I’ve had no problem doing the converse – linking MySQL tables in Access, using the MySQL ODBC provider – but haven’t been doing much MySQL stuff lately so am not sure if possible.

HTH

(Edited by MarkD on 28-Nov-05 22:20. Replaced attachment with revised demo file.)

Note – just because the socalled wizard only allows you to create 20 option buttons, it doesn’t mean you cannot add additional buttons “manually” (drag from Toolbox on top of the option group frame). See att’d demo file for an example. It is similar to the other samples posted, only uses 27 option buttons (alphabet plus an “All” option) to filter list. Arrow keys can be used to navigate within option group. The query that populates listbox uses “Like” criteria as a “filter”, referencing a textbox on form whose value is reset when option group is updated. In actual use you may want to hide the textbox from users – it’s visible here for demo purposes. See demo file to see how it works. BTW, as noted in previous replies, command buttons are also a good (maybe better) way to do this.

PS – Another option is to use Toggle buttons instead of Option buttons. Because toggle buttons have a “depressed” appearance may be best choice? See att’d (revised) demo file, added Option Group of toggle buttons (footer) (“synchronized” with the option buttons (header)).

HTH

In reference to your first question, recommend refer to this MSKB article for Microsoft’s explanation of how library references are resolved in Access:

How Access 2000 resolves Visual Basic for Applications references

Article notes: “If the RefLibPaths registry key does not exist or does not contain a proper reference, Access next uses the SearchPath API to search for the referenced file.” The article then lists the order in which various folders are searched.

HTH

BTW, if you want to “round” a number up or down to nearest multiple of a specified fractional or integer value, you can use this simple function:

Public Function RoundToCustomVal(ByVal dblVal As Double, _
ByVal dblCustomVal As Double) As Double
RoundToCustomVal = Round(dblVal / dblCustomVal, 0) * dblCustomVal
End Function

Example:

? RoundToCustomVal(.17599,.25)
0.25

? RoundToCustomVal(31.2999, 32)
32

Assuming any installed memory is likely to be in multiples of 32 MB of RAM (ie, 64, 128, 256, etc) you can use this function to “round” the fractional MB value derived from hexadecimal byte count to typical installed RAM value:

? RoundToCustomVal(58.5, 32)
64
? RoundToCustomVal(258.0041, 32)
256
? RoundToCustomVal(511.2265625, 32)
512

HTH

To add to HansV’s reply, you also need to divide result by 2^20 (1048576) to convert bytes to megabytes:

? CLng(Replace(“0x1ff3a000″,”0x”,”&H”))
536059904
? 536059904/2^20
511.2265625

Note the result does not round to 512 exactly. The exact number of bytes that equate to 512MB can be calculated in Dec or Hex as follows:

? 512*2^20
536870912

? Hex(512*2^20)
20000000

So the rounding factor would have to be taken into account when displaying this information in megabytes.

HTH

If you use textbox for Yes/No field, and don’t specify format, a -1 (True) or 0 (False) will be displayed. Recommend specify Format property for textbox in report Design View, the three options available are: True/False; Yes/No; On/Off. Then when report is viewed or printed, one of the specified formatting text strings will be displayed instead of -1 or 0.

HTH

On a recent project where I imported toolbars from another project, and wound up with a bunch of unwanted custom popup menus, I also found this to be a major annoyance. For some inexplicable reason, MS does not make the “Shortcut Menus” toolbar accessible from VBA the same way other commandbar objects (menus & toolbars) are. As noted in MSKB 208444 – ACC2000: Can’t Access ShortCut Menus CommandBar from Code:

“You can’t use Visual Basic for Applications to reference the Shortcut Menus toolbar. It is the only toolbar that is not available by using Visual Basic for Applications.”

(No explanation provided.) Therefore you can’t simply loop thru the custom popup menus programatically and delete all or some of them in one fell swoop. But noted in article you can reference custom shortcut menus individually by name. Thus to delete an unwanted custom popup, you can run code like this from the Immediate window or within a procedure:

Application.CommandBars("MyCustomPopup").Delete

where “MyCustomPopup” is the name of your custom shortcut menu (assumes you used a unique name for the popup). This is what I wound up doing to get rid of the unwanted popup menus (there weren’t that many). Of course the technique Hans described also works, using the UI.

HTH

FYI, I tried to unzip file using CuteZIP, which can unzip .RAR files, but get error msg (see att’d pic) whenever try to extract.

Recommend revise form Load event as follows:

Private Sub Form_Load()
Dim n As Long
' Dim test As TextBox 'it's a label not a textbox....

For n = 123 To 130
' Need NZ because if Available is F, DLookup returns Null
' Cannot assign Null to label caption
Me.Controls("txt" & n).Caption = Nz(DLookup("RoomName", _
"tblRooms", "[RoomNumber]=" & n & _
" AND [Available]=" & True), "")
Next n

End Sub

Note error will occur if do not account for Null values with DLookup (which returns a Variant, label caption can only accept string value).

HTH

Amanda,

Problem probably due to using same DSN for each import. Recommend create new DSN for each VFP database you want to import tables from. Easiest way to do this is to “manually” import/link table – select “ODBC Databases()” from list in import dialog, then create new Machine Data Source, specify “Visual FoxPro database (.DBC)” option and path to the .DBC file. Once this is done, to avoid having to hard-code paths and table names, recommend create two new tables: tbl_DSN, to store DSN name & database path; and tbl_Import, to store table names (assuming you are importing same tables from different source databases). tbl_DSN has 3 fields, DSN_ID (Long) – an index to sort on, DSN_Name (Text – 30) – name used when DSN created, DBC_Path (Text – 255) – full path to .DBC file. tbl_Import has 2 fields, tbl_ID (Long) – index, tbl_Name (Text – 50) – name of table to import. (You don’t have to use these names, just an example – the sample code uses these names.) The attached text file is exported code module. TestImportFoxPro sub opens two recordsets based on the two tables & loops thru each, importing each table listed in tbl_Import from each data source specified in tbl_DSN, calling ImportOrLinkTableFoxPro function (same code as previously posted) for each iteration of inner loop. I tested this by creating two new DSN’s (named “VFP1″ and VFP2” to keep things simple), updating tbl_DSN with paths, etc, and adding 3 table names to tbl_Import. The test sub successfully imported total of six tables (the second set of tables automatically had a “1” appended to table name). The Debug.Print statement (inner “Next” loop) is used to help demonstrate how the loop sequence works.

You may be able to adapt this technique for running multiple FoxPro imports automatically.

HTH