How to get a return value of the number of full years elapsed between two dates. Q: William Butler writes: “I have been working on this problem all day, writing an expression in a query to show the difference between today’s date and one that just passed. It seems it
Here’s a more streamlined method for backing up databases without exiting them. In Access Archon #115(WAW 5.16) I described a method (usable in database formats from 2000 up) for making a backup of the current database, without exiting the database. This is very handy when working on a database –
Working with a combo box record selector. Q: Debbie Sabia writes that she has created a form with a record selector combo box. The end user selects an ID from the combo box and correct data is pulled up on that student. Here’s the problem. When you use the record
How to make all table records appear in a query regardless of whether they have any linked Extras records. Q: Liz has a query that returns records from a Registration table and an Extras table. She needs the recordset to include all the records in the Registration table regardless of
Using the Size to Fit Form. Q: Peter Farren writes “I would be grateful if you could clarify for me usage of the acCmdSizeToFitForm constant. For example, the Load Event of frmInvoices in Archon Column 114 consists of: DoCmd.RunCommand acCmdSizeToFitForm I assume that the RunCommand method is applied
How to reference specific fields in an Access query. Q: Lee Aldrich wants to know how to reference specific fields in an Access query/dataset. A: In VBA code, you can set up a DAO recordset to work with Access data, using the following syntax (the example sets two variables
How to change the color of a specific control on a report. Q: Huber Stevan wants to know whether there is a way to change the color of a specific control on a report record by record, depending on whether certain criteria are met. A: For a report, this can
Some readers suggest efficient alternatives to writing large quantities of text to a separate document, which is then imported into Word. Several readers wrote to suggest more efficient alternatives to writing large quantities of text to a separate document, which is then imported into Word. Robin Trew and Andrew Koch
A reader’s having some problems backing up back end databases. Q: Bob Linthicum writes that he is having a problem with backing up back end databases using the code from Access Archon #151. He is getting Error 76, “Path not found”. A: Try my new Extras Plus add-in (Code Sample
Q: Bradley Welland writes to ask if there is any information available on using ActiveX controls in Access. A: This continues to be a sore point, even in Office 2007. ActiveX controls (other than the few native ones) can’t be depended on to work the same in Access as in
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
At the end of Access Archon #159 (in AW 9.06), I noted that in some cases, multi-valued fields can replace complex code for add-to combo boxes. As it turns out, I have to take that statement back – I was relying on a feature that was pulled from Access 2007
How to filter a table-type recordset. Q: Mark Jackson writes: “Does anyone have a very complete example of how to open a table type recordset based on a table in the same database? I can’t seem to find what I am missing in my code. I was able to get
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 make invoice numbers automatically advance one number at a time. Q: Peter Lessey writes: I would like the invoice numbers to advance automatically one number at a time. Also to start from a preset number. Is there a way to do this? A: You can always use an
How to create emails for multiple recipients using data stored in an Access table, and attach general reports. Access Archon #135 Introduction This article combines techniques from some previous Access Archon columns to create emails to multiple recipients using data stored in an Access table, and attaching either a saved
This article looks at using conditional formatting on a form field. Q: Tim Rundle writes: “I have two fields that form part of a database. Both are numeric. In the forms area I obtain the average of the two fields and show the result as an unbound Field in the
How to use the AddItem method to transfer data from two fields into a single column in a listbox. Q: Kris Rutko wants to know how to transfer data from two columns of table information into a single column in a listbox, using the AddItem method. A: Save the value
A tip to keep in mind when copying queries to another database. Queries copied to another (transfer) database may lose their links. Try including the tables in the transfer.
How to shrink up the blank spaces on reports. Q: Randy writes: “I was wondering if there was any way I could shrink up the blank spaces on a report. The CanShrink Property just kinda shifts the blank spaces from the bottom of the detail section to the top in
A reader suggests a quick way to re-attach all procedures. Mark Plumpton, responding to the Orphaned Code tip in WAW 4.22, suggests that a quick way to re-attach all procedures is to open the original form’s code module, select all (Ctrl-A), copy (Ctrl-C) and then open the new form’s module
Some interesting error messages and unhelpful help that readers have come across. Sometimes you get singularly unhelpful help or error messages. Here’s some we and our readers have found recently … William Ferry reports the following weird error message, which occurs whenever he boots his laptop running Win NT Workstation:
How to suppress subtotals on reports. Q: Barbara Gorches writes: I am creating a report that needs to subtotal my detail records on several different levels. Level 1 – project phase totalLevel 2 – project totalLevel 4 – grand total This works fine, but when we have a lot of
How to prevent duplicate data entry in different records. Q: Nigel Heremaia wants to prevent users from entering the same data in the FirstName and LastName fields in different records. He is using this code, but it isn’t working: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim FirstName Dim LastName Dim
