Printing forms from Microsoft Access
Added on: Wednesday 17th March 2010
I have many clients who occasionally like to print out a form record from their databases. Obviously they have reports for the information they regularly print out but sometimes its useful to take a 'screengrab' of the current record they are viewing.
The problem is that often although the form is displaying one record it will actually print out all the records in the underlying recordsource which can mean the whole table.
I did a quick search on the Internet and was surprised to find that almost all results suggest creating a report and then using the 'where condition' of the OpenReport method so that only the selected record is displayed.
The question is- is it worth creating a report for every form that you might want to print out.
I was surprised that not one solution mentioned actually printing the form itself.
All you need to do is use the PrintOut method and ensure that it only prints one page. The code behind the button is shown below:
DoCmd.PrintOut acSelection, 1, 1
Where acSelection is the print range and the two 1s are the first and last page to print.
There are other tricks you can use too. All objects on the form have a Display When property - by default this is set to Always but if you set it to Screen Only the object won't appear on the printed page. So for example your print button doesn't need to appear on the print.
The header and footer sections of the form also have a Display When property so you can hide these too on the print out.
Finally, you can change the Page Setup for the Form - for example changing the layout to Landscape and reducing the margins - so that the record fits on one page.
There are disadvantages of course - you'll get all the shading or colour of the form - but as a quick and dirty method it works fine and saves building a report.
Microsoft Access Search and Replace
Added on: Tuesday 26th January 2010
Although Microsoft Access has a search and replace feature it only works on data within a form or a table. There is also a Find facility within code modules but what if you are looking for something within a form or query design.
Following the change in the VAT tax rate in the UK on January 1st, I was asked to update an access database to reflect this change.
Unfortunately, this wasn't as easy as I thought it would be because the database had obviously had bits bolted onto it on numerous occasions - possibly by different developers.
So there wasn't a single VAT rate stored in a table somewhere - sometimes the rate was hardcoded into the code behind forms or modules, sometimes in the query design and sometimes in a calculated field on a form.
The code was easy enough to update but the database contained hundreds of forms and even more queries (none of which had a name that gave any indication of what they did) so I was faced with opening each one up and going into design view to check for a hardcoded VAT rate.
At this stage I wondered whether I could make use of the little known SaveAsText feature of Microsoft Access VBA.
SaveAsText does exactly what it says - it writes the query or form design out to a text file. Some of the output is fairly unintelligable but I figured it would be easy enough to spot the VAT rate calculation amongst the text.
After a few minutes here is what I came up with:
Function WriteObjects(sFolder As String)
Dim qdf As QueryDef
Dim ctr As Container
Dim intForms As Integer
Dim db As Database
Set db = CurrentDb()
Set ctr = db.Containers("Forms")
For intForms = 0 To ctr.Documents.Count - 1
SaveAsText acForm, ctr.Documents(intForms).Name, sFolder & ctr.Documents(intForms).Name & ".txt"
Next intForms
For Each qdf In db.QueryDefs
If Left(qdf.Name, 1) <> "~" Then
SaveAsText acQuery, qdf.Name, sFolder & qdf.Name & ".txt"
End If
Next
End Function
This uses the Microsoft DAO library instead of Active X Objects but you could adapt the code to use the latter.
For this to work in a project you would have to first add a Reference to the DAO Object library.
Then just call the function with the folder you want to save the text files in as a parameter. You could call it from the Immediate window or else from a button on a form.
The function then loops through all the forms and queries in the database and outputs each one to a text file with the same name as the object.
Notice the line: If Left(qdf.Name, 1) <> "~" Then in the code. This just makes sure that only saved queries are output - otherwise you'll also get all the queries behind combo and list boxes.
Once I'd got my folder of text files I simply used a text editor (PSPad) to search the entire folder for the references I was looking for.
Having examined the search results I could go back to the database itself and make the relevant changes.
Sorting text fields in numeric order in MySQL
Added on: Tuesday 24th November 2009
Here are several ways of sorting text fields in numerical order in a MySQL query.
Normally you wouldn't do this because you will have defined the field as numeric when setting up the table.
However, if you want to create an application with as much flexibility as possible there will be times when you might need this.
I often use it in our CMS because it allows 'user defined fields' in any record type.
The data is stored in an additional table which references the correct record and field.
As all the data for any user defined field is stored in the same table it has to be stored as text in order to allow the maximum number of 'types' of data.
Any validation is done on the input form so effectively you can have different data types.
So an example might be that a 'Price' field is added to the standard content record for listing products (the full e-commerce module has a separate table for products but a small site wouldn't have this) and this would need to be sorted in ascending or descending order.
If for example you had several entries such as 5.00, 10.99, 1.45 and 6.35 then the SQL statement:
SELECT user_value FROM table ORDER BY user_value
would return 1.45, 10.99, 5.00 and 6.35.
There are several ways you can sort this (excuse the pun!).
SELECT user_value FROM table ORDER BY (user_value+0)
SELECT user_value FROM table ORDER BY CAST(user_value, INT)
and
SELECT user_value FROM table ORDER BY LPAD(user_value,10,'0')
All of the above work and I don't think it really matters which is used. I would guess that the overhead is slightly greater for the last two (although an internal conversion probably has to be done for the first one) but I haven't tested them against each other.
Be careful with the last one which in the example above pads each record with zeros until the result is 10 characters long - if the original value is more than ten characters it will get truncated.
Conditional Formatting in Microsoft Access Reports
Added on: Saturday 29th August 2009
Whilst working on a Microsoft Access project recently, I needed to produce a report with certain fields highlighted in different colours depending on their values. I new I could do this with conditional formatting so I ploughed on and started building the
After I had spent some time on this, I realised that Access only allows three conditions for formatting of cells - presumably because it mirrors field formatting in forms where you have a format for negative, zero or null and positive values.
This was a bit of a blow as I had committed myself to using this method and the report was all but finished.
After the initial panic, I came up with a fairly simple solution that allowed me to use the five different colours that I needed for each field.
All I did was copy the text box control with the three formats and pasted it on top of the original control.
With the new control renamed I then removed the three conditional formats and added the two extra that I needed.
All that remained was to make sure that the new control only displayed if the value in the field matched one of the two new conditions - one line of code in the Detail_Format event.
My conditions were actually based on the value of a hidden field on the report so the code was simply:
Me!field2.Visible = (Me!hiddenValue > 3)
where field2 is the text box containing the formatting and hiddenValue is the hidden control that is the criteria for the formatting.
Both field2 and the original control had the same recordsource so when the hiddenValue was greater than 3, field2 displayed exactly the same data as the original control but because it was on top, it hid the original.
This worked fine and in fact could be extended to use 10, 20 or more conditions just by adding more duplicate controls and using the Format event to control their display.
NOTE: I had thought originally I could have set the Back Style property of the text boxes to Transparent to hide them without using any code at all but because it was the Back Colour property that was being changed in the formatting this didn't work. Changing the Back Colour when the Back Style is Transparent has no effect.
The report itself was quite a challenge in the first place which is why I didn't want to abandon what I had done for another solution. I will publish another article covering the whole report in due course.
Database Conversion
Added on: Friday 13th February 2009
If you find yourself frequently moving data between different types of databases then you can either write a script to do it or you can use one of the many utilities available.
I've been converting a lot of Microsoft Access databases to MySQL recently as customers want their software applications to be available online.
Although I have developed a script that I can use to do this it is a bit rough around the edges and I haven't had time to refine it at all so I usually end up with quite a bit of additional tidying up to do on the data.
So, the other day I thought I'd see what was available on the Internet. In the past I've tried various programs but have always found them lacking in certain areas (or not working at all).
To my surprise though I found a piece of software - Access To MySQL - by a company called Bullzip
Unlike a lot of other utilities it doesn't try to be clever and only does the one type of conversion but that is all I wanted.
I have to say I am really impressed - its very simple, fast and more or less foolproof. As well as all this the software is Freeware so may be used free of charge for non-commercial purposes.
I will do some more testing but it looks like this might become a valuable tool for me.
Andrew Parrott runs 