In this video I demonstrate how you can set up a two column report and have the records Weave to and fro across the Report. Open the report in “print preview” then select “page setup”, “columbs”, then select the column layout you prefer.
… …
Show Table Structure to a Forum.The best way is to provide a small sample database. You don’t need to provide your whole database. In fact by including too much information you just confuse the issue and make it difficult for anyone to help you.
Indeed if you just dump your monster dB on the Forum people will take one look and leave. You won’t get the help you expect. If your database is untidy, full of junk code, and generally slung together, why should anyone else try and sort it out for you… All that is required is the Form that has the issue and the complementary table or tables related to the issue on that form, nothing else. If your problem is in a module, then provide a brand new form to show the problem. If you are unable to supply a sample, then try the alternative(s) below which aren’t ideal, but better than nothing.To help someone to answer your Forum question, you’ve been asked to show more information about your “Table” structure. Here are several options you can use to extract and present the information to the forum…
… …
A common requirement in MS Access is to extract Columnar data into horizontal data, for instance to make reports look better, to provide a list of emails suitable to send to with Outlook. In the YouTube videos below I explain how this can be done.
Good database design requires you to store your data Vertically. However it’s difficult to display this Vertical, columnar data in forms and reports. Also, in the case of an email list, you may need it convert it from the “Vertical” to “Horizontal” for use in VBA code to send multiple emails.
In this video I demonstrate how to extract this columnar data and concatenate it into a string suitable for these type of uses…There is enough information in the video for you to be able to reconstruct the demonstration yourself.
If you want to save yourself a bit of time, then by all means you can download the sample database for a small fee..
Public Function fConcatList()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String
Dim strText As String
strSQL = "SELECT tblContact.MailToHere FROM tblContact" 'This is just a query like you would build
'in the query builder, but it is in text format
Set DB = CurrentDb
'Open a Recordset and loop through it to fill the text box txtTest
Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
Do Until RS.EOF
If strText = "" Then 'This If statment prevents a single comma at the begining of the text","
strText = RS!MailToHere
Else
strText = strText & ", " & RS!MailToHere
End If
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Set DB = Nothing
fConcatList = strText
End Function 'fConcatList
… …
In the above example “Display Field Data Horizontally” I demonstrate how to compile a list of email addresses which are displayed in a text box. The results could just as easily be passed to another function designed to email the list of clients stored in a table. It would also be possible to provide a query (as an SQL string) with a where clause, making it possible for you to email a subset of clients based on some sort of criteria.
It is always desirable to have the SQL Statement build itself as it were. In this Video “Complex Data Horizontally” I demonstrate how to build the SQL Statement with a WHERE clause that limits the records extracted using the batch number and serial number of the item. The item is a very sophisticated and expensive device somewhat similar to a mobile phone. You just could not manually create the necessary queries effectively.
In this YouTube I’m going to show you one way to go about this.
Option Compare Database
Option Explicit
Const strSQLWhere As String = "SELECT tblUnitRepair.UnitRepairBatchNo, " & _
"tblUnitRepair.UnitRepairSerialNo, tlkpRepair.RepairDesc " & _
"FROM tlkpRepair INNER JOIN (tblUnitRepair INNER JOIN tblRepairLog " & _
"ON (tblUnitRepair.UnitRepairSerialNo = tblRepairLog.RepLogSerialNo) " & _
"AND (tblUnitRepair.UnitRepairBatchNo = tblRepairLog.RepLogBatchNo)) " & _
"ON tlkpRepair.RepairID = tblRepairLog.RepLogRepair " & _
"WHERE (((tblUnitRepair.UnitRepairBatchNo)="
Const strSQLAnd As String = "AND ((tblUnitRepair.UnitRepairSerialNo)="
Const strSQLOrder As String = "ORDER BY tlkpRepair.RepairDesc"
Const strSQLParenRHS1 As String = ") "
Const strSQLParenRHS2 As String = ")) "
Public Function fConList(intBatchNo As Integer, strSerialNo As String)
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String
Dim strText As String
strSQL = strSQLWhere & intBatchNo & strSQLParenRHS1 & _
strSQLAnd & "'" & strSerialNo & "'" & strSQLParenRHS2 & strSQLOrder
Set DB = CurrentDb
'Open a Recordset and loop through it to fill the text box txtTest with
'the faults Repaired >>>reprorted by the customer<<<<
Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
Do Until RS.EOF
If strText = "" Then 'This If statment prevents a single comma at the begining of the text","
strText = RS!RepairDesc
Else
strText = strText & ", " & RS!RepairDesc
End If
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Set DB = Nothing
Let fConList = strText
End Function
… …
The link(s) below relate to various discussion(s) related to this Blog.
Visit these and you may find someone else has already solved the problem you are having. You could also pose a new question to clarify the process of using this technique for concatenating vertical lists into a horizontal display of information.
… …
This video should be of interest to anyone who wants to easily apply filters to an MS Access form in datasheet view or continuous form view. In this demonstration I show how you can apply filters in a Form presented in Continuous view and also in Datasheet view. When you apply the filters, they are actually added to the forms properties. By opening the Forms property sheet you can view the filter you have just created. The filter appears as a text string added to the filter property of the Form. Once you are aware of this nifty little trick that MS Access automatically does for you, then you can leverage it by copying the text version of the filter and use it in VBA code! It’s not 100-percent straightforward, and I show you one Gotcha and the solution… The solution is to change the double quotes within the filter to single quotes. You can use this filter string created by MS Access in your VBA code. Calling the VBA code which sets the filter from the onClick event of the label above the column. With this information you now have the ability to level up your display to something nice and professional looking. Got to be good for your kudos in your company!
You will find more information related to this video in this thread on Access World Forums (AWF) here:- ApplyFilter to subform
… …
Can’t remember the name of the Form you want? Quickly and easily access the list of forms in MS Access when you are entering code into a code window.
Type in “form”, then hold down the Ctrl key and press space. This should show you a list of Forms. Look for the form you want. Look for the form name, (the form with the name you gave it) but it will be prefixed with “Form_” so if your form is named “frmYourForm” then look for “Form_frmYourForm” … Now delete the prefixing characters Form_ and you will have a reference to your form in your code.
… …
If you’re stuck for real estate, (a crowded jumbled Form) a particularly busy Form then one way of getting a bit more room might be to have a label oriented vertically. An alternative would be to take stuff off your Form and put it on a pop-up Form. You can see my blog here on a Class Module which adds some interesting functionality when you pop open a Form, it’s a very good solution. Another solution is to put a Tab control on your Form so that you have different sets of controls on different Tabs.
Open the property sheet for the Text Box and/or the Label. You can do this, both in a Form and a Report. You can also multi-select Controls as shown in the demo. In the property sheet select the other tab and then click on the “Vertical” listing and this will change it from Yes to No, and vice versa.
If you want something a bit special you could try one of the activeX controls. I’m not sure if this one from Lebans works with the latest versions of MS Access, but you could give it a try. As a general principle, is not considered good practice to use activeX Controls because they can fail when Microsoft updates MS Access…
…
