In situations where a Parent record has a limited number of children (0 to 10 works well) and you would like to list those children next to the parent somewhat like you would with a Pivoted table. Pivoting the table would however result in a massive table which is extremely wide. Pivoting and concatenating the fields can keep the resulting list within a manageable width. Take for example the following table.
So we have table with four header records and the children all relate to those parents. For each parent obtain a list of children
Firstly place the following function in a module
Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null
SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value
' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & ", " & rs!Fld
' remove leading comma and space.
vFld = Mid(vFld, 3)
Set cnn = Nothing
Set rs = Nothing
' return concatenated string.
Conc = vFld
Now set up a Query and call the function in an expression
This results in the appropriate list
If users are editing or creating a record and there is an option on the form to print out the form if they press it they will expect the information that they have just created to appear on the print preview. Normally MS Access does not save the information to the database until the record is exited or specifically instructed. This can lead to user confusion when they enter a record hit print preview expecting to see the record and see a blank preview.
Placing the following code before calling the print preview will ensure that the record is saved prior to the print preview being triggered resulting correct information being displayed in the print easy. An easy fix – you should always do the easy wins.!
Note order is important, put this before the event you are wishing to trigger (quite ubiquitous in my code)
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Its the constant small touches that make great applications
I have three guitars 10 windsurfers and 4 bikes (5 if you count a static exercise bike) I have three regularly used computers (tablet , desktop and laptop) – four if you count my phone. My bikes are not duplicates one is for racing, one is for training, one is for commuting and the final one for mountain biking. I often use my commuting bike instead of my car. I try my hand at DIY and have got to the point of building a cupboard containing drawers only for tools I have so many tools I cannot list them all from memory.
I get the impression that companies are pushing for all applications to be pushed to the web. Fine but if you have invested in an amazing building with an amazing Local Area Network why push absolutely everything to the web? Milk your assets often the longer you leave it the more stable the web environment will be – it still continues to experience massive change. Web has its advantages but flexible cheap very rich User Interface design doesn’t seem to be it. The benefit of database driven applications today is that the backend is totally independent of the User Interface. Developing an internal local area based UI can be totally independent of building a web based user interface.
Carefully consider and don’t be afraid to use the full range of tools available to you get the backend right and then work on a very very usable front end. Users want quick usable design that fit their needs they really don’t care about the technology.
MS Access has a version of SQL that can be edited to enforce specific columns being shown in queries.
Within the query editor navigate to the SQL View
Normally when a cross-tab query is selected MS Access will generate columns for every value that exists. This is often very useful but if there are no values there may be gaps in the scale. Additionally you may wish to limit the number of columns that are displayed in the query or force limited numbers to tally with a report. Some reports will crash if certain columns are not found.
Generally the last line of SQL code within a query will follow a similar syntax to the following;
Where YEAR is an actual field name that could alter in your circumstances
Altering this to the following will for instance generate years 2000 to 2010 with no gaps even if there are no values in certain columns.
PIVOT [QueryName].YEAR in ("2000","2001","2002","2003","2004","2005","2006","2007","2008","2009","2010");