How to write long strings to an InputBox. Q: Peter Matthews writes that help for the InputBox says it will input the user’s string, and the definition of a (variable length) string says it can be up to 2 billion characters. So how come my VBA called out InputBox won’t
How to list detail items on a report in the order they were entered. Q: Scott Campos writes: “I want the invoice report to print in the same order as is entered on the order form. This makes it much easier to manually double check an order to make sure
Here’s a review of the Access Workbench, a tool that helps manage Access databases. Access Archon #149 By guest author Danny J. Lesandrini Database Management Made Simple Product reviews can get pretty boring, especially when you’re the one writing them, but I’ve found a way to make this one more
How to rename files in a folder. Q: Richard Bryant writes: I have written the following code to change the name of files imported as .tif files from the county deed books for use by my Access program. Private Sub Command2_Click() Dim Path1 As String Dim Path2 As String Dim
How to retrieve some commonly used folder paths using contemporary syntax. Access Archon #148 Introduction Back in 1996 or 1997 I wrote a set of articles for Office Underground Office Newsletter (that was the one printed on bright yellow paper) on retrieving various paths, such as the Windows and Office
A reader offers some further information about the CreateMonthList function. José Dumoulin wrote in response to the CreateMonthList function in the sample database for Access Archon #146: For us, Europeans, it is necessary, in this line, to replace commas by semicolons: strMonthList = strMonthMinus3 & ” ” & strYearMinus3
How to ensure leading zeroes are not discarded. Q: Albin Moroz wrote me to say that he is having problems keeping the leading zeroes in U.S. Social Security Numbers (SSNs). He was using the expression Format(Nz(patientRS(patient.SSN), “”), “###-##-####”) which discards leading zeroes. A: Instead of the # placeholder, which discards
How to display linked records before deleting them. Access Archon #147 — The VBA code for the command buttons is listed below: VBA Code frmOrderCleanup Private Sub cmdDeleteOrder_Click() On Error GoTo ErrorHandler Dim strPrompt As String Dim strTitle As String Dim intReturn As Integer
Here’s a quick solution to get databases linked again if the Linked Table Manager is missing from the Database Utilities menu. Q: Jeanne M. writes: “I sent new back-end and front-end databases to a client, and told him to re-link the front-end to the back-end, but he said there was
How to display linked records before deleting them. Access Archon #147 Introduction If you have set up relationships between tables with Cascade Delete Related Records unchecked (a good idea, to prevent inadvertently deleting records you might want to save), then you might want a way to check which records are
How to create a date range filter. Q: Fredm writes: “I have a table that has two different date fields but I have linked them to one date table. I want to filter the results of the date to show records from now to 30 days before but find that
How to format text in a memo field using an Access add-in. Q: Michael Masson writes: I am re-working an existing SQL 7 database to MS Access 2003. The data translation etc is OK if a bit hairy at times. I am using this database as a model for a
How to synchronize Access and Outlook contact data. Access Archon #145 Introduction If you store contact data both in an Access table and an Outlook Contacts folder, it would be handy to be able to quickly switch from the Access contact record to the Outlook contact item, or vice versa.
How to change the sorting order of a report. Q: Steve Rudder writes: “I have an Access report that the user would like to be able to change the sorting order. For example, some of the columns they’d like to sort on in the report are Start Date, End Date
Q: Martin Wichmand writes: I’m trying to make some conditional formatting with VBA, using the following line of code to pop up a message box if the date in the control is today’s date or later: If IsDate(Me![cboOpfoelgning].Value) >= Dato() Then But I just get an error. A: The
Access Archon #144 The following sample VBS program will show how to extract these values: Option Explicit Function FindZip (Str) Dim re, Matches Dim Zips(2) Set re = New RegExp re.Pattern = “((d{5})(-(d{4}))?)” Set Matches = re.Execute (Str) If Matches.Count =
How to back up database objects before making changes to them. Q: Jean H. writes: “I am constantly making changes to forms, reports and other database objects, and sometimes I find that a change doesn’t work, and I need to return to an earlier version. I do back up my
Access Archon #144 Introduction I was recently given an assignment. The client needed to extract information from several log files and put them into a format that would expedite ad-hoc analysis. These log files were in several different folders, and were generated at several times during the day. They wanted
Looks at a method that’ll often fix a problem that’s resistant to all other repair methods. Q: Harold Lewton writes: ” I have 1 database file that works perfectly in Access 2000 and partially in Access 2003. In Access 2003 it works fine while in the Access program itself until
How to export data that’s to be archived to an Excel worksheet, and then remove the exported records from the Access tables. Access Archon #143 The procedure that exports the data to be archived to an Excel worksheet, and then removes the exported records from the Access tables, is listed
The correct code to run when you want to add a record to a table with a value from a form. Q: Sarah Balthazor writes that she is getting an error message when trying to run the following code, intended to add a record to a table with a value
How to display an ID field in a closed combo box but hide it in a drop-down list. Q: Rich writes that he needs to display an ID field in a closed combo box, but doesn’t want it to appear in the drop-down list. The field is the first one
How to recover from an AutoNumber disaster. Access Archon #165 Introduction A standard AutoNumber type field (with its NewValue property set to Increment) should start at 1 (though it is possible to start with a higher number), and each new record should be assigned the next higher number, so
The phantom breakpoint: what causes it and how to solve the problem. Q: Don R. writes: When I click a command button to print a Word letter (this button runs VBA code to create the letter from a Word template), the code stops on a certain line, which is highlighted