A great overview and comparison of Access, SQL Server, Visual Studio and the .NET Framework. From time to time I get a message from a client asking whether they should upgrade from Access to SQL Server, usually after seeing some presentation or ad for SQL Server. Sometimes this is appropriate
How to have a form open to the last record. Q: Manjit Lota writes to ask how he can have a form open to the last record. A: There are several ways you can do this. If you want to use the form for data entry, change the form’s DataEntry
Making records appear in the order they were entered. Q: Frank Payne writes: “I would like the data in a particular table to appear in exactly the order in which I enter the records. I do not want it sorted in either Ascending or Descending order. I have deleted the
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 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
