Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Creating Using Form Custom Property

Parameter Controls are provided to the Users for entering data Filter criteria values for preparing MS-Access Reports. A reference to the parameter control fields can be set in the criteria row of the Report Source Query directly to filter the data. A sample image of such a Parameter Control is given below.

The above Report Parameter Control gives flexibility to the User to set a Date Range in the fields provided on the screen before opening one of two Report Options provided. When the User Clicks on the Preview Command Button the Report will be opened with data filtered using the Parameter Control Date Range values set as Criteria on the Source Query.

To record the Date Range values (Date From and Date To) a small Table with two fields is created with a single record and used as Record Source to the above Form. The idea behind the use of a Table is to preserve the last used Report Parameter Values in the Table. Next time when we open the Form we will know for which period we have prepared the Report earlier. The parameter table can also be used in the Query to link with the Data Table or use its value as criteria in the criteria Row of the Report Source Query. The following two Form Property Values must be set as shown below to prevent adding new Records to the Table and not to delete the existing one:

  • Allow Additions = No
  • Allow Deletion = No

This method works fine when the Database is a Single User one.

But, when the database is shared on a Network there is a problem to this method. Different Users may open the same Parameter Screen at the same time to prepare their version of the same Report. They will attempt to change the parameter values at the same time. This action will end up with a record edit lock error or the values set can cross over and the Report printed can go wrong too. Even though the Users can open different instances of the same Form on their machines the Record Source Table is same.

We are focusing on this particular aspect to see how we can safely provide the above Parameter Control to the Users to work safely without clashing each other.

Perhaps, you have the right solution to this problem by now. Yes, do not use the Parameter Table to store the Report criteria values; instead create two Unbound Text Boxes on the Form, as you have rightly guessed. This will ensure that all Users will work independently on their own instances of the Report Parameter Form and no chance of clashing with each other's values.

There is only a minor draw back in this method; you cannot store the last used Report Parameter Value anywhere so that it will be displayed next time when you open the Form.

At least one set of value is required when you open the Form next time. If these controls remain empty and if you run the Preview option without setting the parameter values then the Report will be empty and will end up showing #Error in all controls with expressions created for Summary Values.

I have already published an Article on this topic earlier as how to open the Report without this Error condition when the Report Source Query or Table is empty. Click here to find out.

We can save the values, from the Unbound Text Box controls, on the Parameter Form itself in Custom Properties, which we can create on the Form. Managing data in Custom Properties can be done only through VBA and these Property Names and their Values are not visible on the Property Sheets that we normally work with.

Click here to find out more details on Custom Properties and a method that we have used earlier to open the Form with last edited record as current on the Form.

We have to go through the following procedure to manage the User data on the Form itself without the use of a Table as Record Source:

  1. Create two Custom Properties on the Form with the names DateFrom and DateTo with the Data Type Date/Time and with an initial Value.

    This is required only once. A small VBA Program is required in the Standard Module to create the Custom Properties on the Form. In the Program the Parameter Form Name is required for reference. Not necessary to keep the Form in Design View to create the Custom Properties.

  2. When the Parameter Form is closed after normal use the values set on the Unbound Text Boxes are Saved into the Custom Properties during the Form Close Event.
  3. The saved values are loaded back into the Unbound Text Boxes from the Custom Properties when the Report Parameter Form is open next time.
  1. To try out this method, open a new Form and create two Unbound Text Boxes.
  2. Click on the first Text Box and display its Property Sheet (View - -> Properties).
  3. Change the Name Property Value to fromDate.
  4. Change the Name Property Value of the second Text Box to toDate.
  5. Close and save the Form with the name RptParameter.
  6. Display the VBA Editing Window (Alt+F11), copy and paste the following VBA Code into the Standard Module. If necessary, create a new Module (Insert - -> Module).
    Public Function CreateCustomProperty()
    Dim cdb As Database, doc As Document
    Dim prp As Property
    
    Set cdb = CurrentDb
    Set doc = cdb.Containers("Forms").Documents("RptParameter")
    Set prp = doc.CreateProperty("DateFrom", dbDate, Date)
    doc.Properties.Append prp
    
    Set prp = doc.CreateProperty("DateTo", dbDate, Date)
    doc.Properties.Append prp
    doc.Properties.Refresh
    
    Set prp = Nothing
    Set doc = Nothing
    Set cdb = Nothing
    
    End Function
  7. Click somewhere within the pasted VBA Code and press F5 to Run the Code and create two Custom Properties with the Names DateFrom and DateTo with the Data Type Date/Time and with the initial value of System Date.

    How do you know whether these Properties are created or not? Try running the Program again and this will tell you that these Property names already exist on the Form.

    If you want to Delete these Properties from the Form then Run the following Code:

    Public Function DeleteCustomProperty()
    Dim cdb As Database, doc As Document
    Dim prp As Property
    
    Set cdb = CurrentDb
    Set doc = cdb.Containers("Forms").Documents("RptParameter")
    doc.Properties.Delete "DateFrom"
    doc.Properties.Delete "DateTo"
    doc.Properties.Refresh
    
    Set prp = Nothing
    Set doc = Nothing
    Set cdb = Nothing
    
    End Function
  8. Open the RptParameter Form in Desgin View.
  9. Display the VBA Code Module of the Form (View - -> Code).
  10. Copy and Paste the following two Sub-Routines into the Form Module and save the Form:
    Private Sub Form_Close()
    Dim cdb As Database, doc As Document
    
    Set cdb = CurrentDb
    Set doc = cdb.Containers("Forms").Documents("RptParameter")
    doc.Properties("DateFrom").Value = Me![fromDate]
    doc.Properties("DateTo").Value = Me![toDate]
    
    Set cdb = Nothing
    Set doc = Nothing
    
    End Sub
    
    Private Sub Form_Load()
    Dim cdb As Database, doc As Document
    
    DoCmd.Restore
    
    Set cdb = CurrentDb
    Set doc = cdb.Containers("Forms").Documents("RptParameter")
    
    Me![fromDate] = doc.Properties("DateFrom").Value
    Me![toDate] = doc.Properties("DateTo").Value
    
    Set cdb = Nothing
    Set doc = Nothing
    
    End Sub
  11. Open the RptParameter Form in Normal View and enter some Date Range values into fromDate and toDate Unbound Text Boxes.

    Close the Form and open it again in Normal View. The date values you have entered earlier will appear in both Unbound Text Boxes.

Even after implementing this method I am not fully happy with it. Because, it will preserve only one of the Values, set by different Users working with the Form at the same time.

What I would like to see as a User is that the last value that I set on the Report Parameter Field is appearing on the Form again when I open the Form next time, not the value set by someone else. Is it possible? Yes, it is possible. We will see how to do this next week.

Share:

MS-Access And Data Processing-2

This is the continuation of earlier Article published on this subject last week. Click here to visit that Page.

Last week we have explored the sample data processing methods and tried to approach the same problem in different ways to arrive at the same result. Reports are the main output component that goes to the User with critical information for analysis of business activities and for making serious business decisions. Transforming raw data into meaningful form and providing them on Reports is a real challenge of any Project.

If you attain some working knowledge of different types of Queries available in MS-Access you can do most of these tasks without touching the VBA Code. Depending upon the complexity of processing steps you can use several Queries, create intermediate temporary Tables and use those tables as source for other Queries to overcome issues that may arise as hurdles in the processing steps.

We will look into such an issue here so that you will know what I meant by hurdles in creating the final Report. Such complex data processing steps can be automated by sequencing each step in a Macro and run that Macro from a Command Button Click or from VBA Sub-Routines.

It is absolutely necessary to create and maintain Flow Charts of process that involves several Queries and Tables for Reports. You may create hundreds of Queries in a Database for different Reports. After some time we may forget what we did for a particular Report. If the User points out any flaw in the output then we can easily back track the steps using the Flow Chart and debug the problem.

Last week I have raised a question as how we will show Revenue, Expenses and Profit/Loss month-wise if the sample data are added with Year and Month Fields. The image of the sample Table (Transactions2) Source data is given below:

The image of the Report Output Created and presented to you last week is shown below:

We can transform the sample data given in the first image above into the Report output form in the second image in two steps. The numbers appearing as Suffix to the Column headings represents the Month Value. For example, Revenue1 is January Revenue and Profit/Loss2 is of February.

We can arrive at the above result in two steps and the SQL String of those two Queries are given below:

Query Name: Method2_1

TRANSFORM Sum(Transactions2.Amount) AS SumOfAmount
SELECT Transactions2.Location,
 Transactions2.Year
FROM Transactions2
GROUP BY Transactions2.Location,
 Transactions2.Year
PIVOT IIf([type]="R","Revenue","Expenses") & [Month];
  1. Copy and Paste the above SQL String into the SQL Editing Window of a new Query and save it with the name Method2_1.
  2. Open the Query and view the output as how it is transformed with the Cross-Tab Query.

     

    Query Name: Method2_2

    SELECT Method2_1.Location,
     Method2_1.Year,
     Method2_1.Revenue1,
     Method2_1.Expenses1,
     [Revenue1]-[Expenses1] AS [Profit/Loss1],
     Method2_1.Revenue2,
     Method2_1.Expenses2,
     [Revenue2]-[Expenses2] AS [Profit/Loss2]
    FROM Method2_1;
    
  3. Copy and Paste the above SQL String into the SQL Editing Window of a new Query and save it with the name Method2_2.

    We are using the first Query as input to the second Query for the final Report output.

  4. Open Method2_2 Query and view the output.

Even though we could arrive at the sample result with the above two Queries we have to modify the second Query every time to create Profit/Loss Column when new data records are added for subsequent months. The P & L Report if created using the second Query then that also has to undergo changes to add Revenue, Expenses and Profit Columns for the new period.

This cannot be a good method when we are expected to automate every process in the Database so that the User can prepare Reports with the click of a Button.

We can automate this data processing task permanently with the following few simple steps:

  1. Create a second Report Table with Revenue and Expenses Fields for all twelve months.
  2. Change the second Query created above (Method2_2) as an append query and add the output data of available months into the Report Table.
  3. Create a SELECT Query using the Report Table as source to calculate Profit/Loss Values for all twelve months only once. This is possible because we have all twelve month's data fields in the Report Table, even if some of them will have only zero values till December.
  4. Design the P&L; Report with all twelve months Revenue, Expenses&Profit/Loss Fields using the Query created in Step-3 as source.

Once you implement this method you don't have to make any changes to the Queries or Report when new data records are added in the Source Table. All you have to do is to automate this process, like deleting the old data (for this action we will need a Delete type Query) from the Report Table and bringing in fresh Report data from source table Transactions2.

So, let us get to work and do it.

  1. Create a Table with the following Field Structure and save it with the name PandLReportTable.

    The Data Fields R1 to R12 and E1 to E12 will hold Revenue and Expenses Values respectively for the period from January to December.

    NB: Don't forget to set the Default Value Property of all Number Fields with 0 values as shown in the Property Sheet below the Field Structure. This will prevent from adding data fields with Null Values when data is not available for those fields. Remember, when you write expressions using Numeric Fields with Null values combined with fields with values; the end result will be Null.

    We have modified the first Query given above for simplifying the data field names.

  2. Copy and paste the following SQL String into a new Query's SQL Editing Window and save it with the name Method3_l.
    TRANSFORM Sum(Transactions2.Amount) AS SumOfAmount
    SELECT Transactions2.Location,
     Transactions2.Year
    FROM Transactions2
    GROUP BY Transactions2.Location,
     Transactions2.Year
    PIVOT [type]&[Month];
    
  3. Copy and paste the SQL string given below into a new Query and save it with the name Method3_2.
    INSERT INTO PandLReportTable
    SELECT Method3_1.*
    FROM Method3_1;
  4. Copy and paste the following SQL String into a new Query and save it with the name PandLReportQ.
    SELECT PandLReportTable.Location,
     PandLReportTable.Year,
     PandLReportTable.R1,
     PandLReportTable.E1,
     [R1]-[E1] AS P1,
     PandLReportTable.R2,
     PandLReportTable.E2,
     [R2]-[E2] AS P2,
     PandLReportTable.R3,
     PandLReportTable.E3,
     [R3]-[E3] AS P3,
     PandLReportTable.R4,
     PandLReportTable.E4,
     [R4]-[E4] AS P4,
     PandLReportTable.R5,
     PandLReportTable.E5,
     [R5]-[E5] AS P5,
     PandLReportTable.R6,
     PandLReportTable.E6,
     [R6]-[E6] AS P6,
     PandLReportTable.R7,
     PandLReportTable.E7,
     [R7]-[E7] AS P7,
     PandLReportTable.R8,
     PandLReportTable.E8,
     [R8]-[E8] AS P8,
     PandLReportTable.R9,
     PandLReportTable.E9,
     [R9]-[E9] AS P9,
     PandLReportTable.R10,
     PandLReportTable.E10,
     [R10]-[E10] AS P10,
     PandLReportTable.R11,
     PandLReportTable.E11,
     [R11]-[E11] AS P11,
     PandLReportTable.R12,
     PandLReportTable.E12,
     [R12]-[E12] AS P12
    FROM PandLReportTable;
    
  5. Design a Report using PandLReportQ as Source File, like the sample design image given below.

    The sample image shows Columns of January and February only. But, you may design the Report for all twelve months in similar way. The Value from Year field is used for creating Headings so that it automatically changes when the Report is printed next year without modification to the Report.

    The above Report in Print Preview is given below.

    We will automate the P&L; Report preparation procedure to get updated data on the Report when new data of Revenue and Expenses are added to the Source Table. As part of the automation procedure we need a Delete Query to remove the earlier data from the PandLReportTable before adding revised data into it.

  6. Create a new Query with the following SQL String and name the Query as PandLReportTable_Init.
DELETE PandLReportTable.*
FROM PandLReportTable;

Isn't it easy enough to prepare the P&L; Report with the above simple Queries and with a supporting Report Table for any number of Locations that you add to your main Source Table Transactions2. As you can see now you don't need any complicated programs to prepare this Report.

If you look at the Queries we have created we can see that there are only two Action Queries among them (Delete and Append Queries). We can put these two Queries into a Macro to automate the P&L; Report preparation easily. But first, let us examine the logical arrangement of this Report preparation procedure with a Process Flow Chart.

In Step-1 the PandLReportTable_Init Query removes earlier Report Data from the PandLReportTable.

In Step-3 the Append Query (Method3_2) takes the Cross-Tab Query output from Step-2 and adds them to the Report Table PandLReportTable.

We have already written expressions in PandLReportQ SELECT Query to calculate Profit/Loss Values. The Report will automatically get all available data from this Query and other Columns on the Report will remain empty till fresh data Records are added in the Source Table Transactions2.

If we can add both the Action Queries into a Macro (or VBA Subroutine) then the User can click on a Command Button to run it every month to create the Report with added data within seconds.

The sample image of the Macro with the Action Queries in sequence is given below for reference:

If you can further simplify this procedure please share that idea with me too?

Share:

MS-Access and Data Processing

Designing Forms or Reports can be learned quickly by mastering the usage of Design Tools available in MS-Access, keeping aside the programming aspects. But, data processing is something that demands diversity in each Project and cannot be standardized. The data Table design is very important and these must be carefully planned and created for easier retrieval of information as well as to avoid duplication. Proper relationships must be established between Tables to join related information together.

Ignoring the importance of these considerations, designing with casual approach and filling up data in them like you do in Microsoft Excel will land you in trouble when you attempt to prepare reports out of them.

You can see a good example of database design in the C:\Program Files\Microsoft Office11\Samples\Northwind.mdb sample Database.

Open this sample database and select Relationships from Tools Menu to view the structure of various Tables and how they are organized and related one another. Use it as a reference point and guide when you plan for a new Project.

Each Field Name in bold is defined as Primary Key in their respective Table and established One-to-many Relationships to one another. This will ensure the availability of required information when needed from related tables for Reports.

The above lines were only a reminder to your future projects. You can see an example image of a bad Table design below. The Location names and Description values should have been in Tables of their own with appropriate Codes. Two Combo Boxes can be created in the Transactions Table Structure to insert those Codes into the fields to avoid duplication of information as shown below.

But here, we are going to concentrate on learning the data processing steps using the above Table.

The second data field Type contains transaction type Codes. R stands for Revenue and E for Expenses. These category Codes are introduced in the Table keeping in mind that we must be able to Group the transactions on Category and Tabulate the Revenue and Expenses Values separately. The Description field shows the actual Account Heads under which each transaction is recorded.

We have been asked to prepare a Location-wise Profit/Loss Statement. Subtracting the Total of all Expenses from the Total of all Revenue figures will give us the required result. How many Queries or steps you require to solve this problem, any idea? We require only the final Profit/Loss value with the Location Name on the Report, like the image below:

The first thought in your mind, I presume may be, how you can subtract the value of one Row from the other. Then you are thinking on the right direction.

If you say in four steps I will not accept that as a good approach but if you can solve the problem and come out with the result then that is OK with me. After all, the correct end-result is all that matters as far as the User is concerned.

If you say in three steps I will be happy to see you how you do it. If you say in two steps then I know you have some grip on things around here. If you say in one step then I know you are somebody with MS-Access.

If you are really interested in taking up this simple challenge then stop reading further down from here and start trying out this in a database of your own. Come back with your own solution and compare it on the examples given here. If you do it differently, but arrived at the same result then share that idea with me too.

Create the Transactions Table with the Structure and sample data given above.

One Step solution

  1. Copy the following SQL String into the SQL Editing Window of a new Query and save it with a name you prefer.
    SELECT Transactions.Location,
     Sum(IIf([type]="E",-[Amount],[Amount])) AS Profit
    FROM Transactions
    GROUP BY Transactions.Location;
    
  2. Open the Query in Normal View and you will see the result of the Query as shown in the second Image given above.

Two Step solution

  1. Create a Query with the following SQL String and name the Query as Query_Step1.
    SELECT Transactions.*,
     IIf([Type]="E",-[Amount],[Amount]) AS Amt
    FROM Transactions;
    

    The Query output will look like the image given below:

    Tip: The Query Amt Column is formatted to display Negative Values in Color and in brackets. Open the Query in Design View. Highlight the Column and click on the Properties Toolbar Button or select Properties from View Menu to display the Property Sheet of the Column. Type 0.00;[Red](0.00);0.00;0.00 into the Format Property and save the Query. If you open the Query now the output will appear in color.

    The Format Property Values are expressed in four segments separated with semi-colons. First segment dictates how to display positive values, second segment stands for Negative values, third segment says what to display when the field value is Zero and fourth segment displays zero when the Field/Column contain Null. Third and fourth segments can be set with a literal string like 0.00;[Red](0.00);"Zero";"Null" to display these values rather than 0.00. You can set the Field Format Property values on the Table Structure, on Forms or on Reports. It is not necessary that you should use all the four segments of the Format Property Values all the time.

  2. Create another Query, with the following SQL String, using Query_Step1 as Source Data and save the Query with the name PL_Report:
    SELECT Query_Step1.Location,
     Sum(Query_Step1.Amt) AS Amount
    FROM Query_Step1
    GROUP BY Query_Step1.Location;
  3. Open the PL_Report Query in normal view and the result will be same as the second image given above.

Three Step Solution

If you need more clarity in how the results are being formed for the final report then try this method.

  1. You can use the first Query under the two steps solution as the first step here.
  2. Use the following SQL String, that uses the first step Query's output as source data, and create the second step Query with the name Query_Step2:
    SELECT Query_Step1.Location,
     Query_Step1.Type,
     Sum(Query_Step1.Amt) AS Amt
    FROM Query_Step1
    GROUP BY Query_Step1.Location, Query_Step1.Type
    ORDER BY Query_Step1.Location, Sum(Query_Step1.Amt) DESC;
    

    The output of the second Query is given below.

  3. Create a third Query for the final result, with the SQL String given below, using the second step Query (Query_Step2) as Input:
SELECT Query_Step2.Location,
 "Profit/Loss" AS Description,
 Sum(Query_Step2.Amt) AS Amt
FROM Query_Step2
GROUP BY Query_Step2.Location;

The output of the above Query is given below with a three Column result replacing Type Column with Description.

How about doing it differently and arrive at the following Result with Queries in two Steps?

  1. Create the first Query Method2_1 with the following SQL String:
    TRANSFORM Sum(Transactions.Amount) AS SumOfAmount
    SELECT Transactions.Location
    FROM Transactions
    GROUP BY Transactions.Location
    PIVOT IIf([Type]="R","Revenue","Expenses");
    
  2. Create the Report Query Method2_2 with the following SQL String that uses Method2_1 as Source:
    SELECT Method2_1.Location,
     Method2_1.Revenue,
     Method2_1.Expenses,
     [Revenue]-[Expenses] AS [Profit/Loss]
    FROM Method2_1;
    
  3. Open Method2_2 Query in Normal View and check the output.
    As you have seen in the above examples you can approach a problem in MS-Access differently and arrive at the same result. If you have to create several steps to get the final Report output, then it is a good idea to create a Flow Chart of the Process Steps. Later on if you find something is not right with the Report you can always follow this path and back-track to find the Error.

A sample Flow Chart of the Three Step Solution is given below:

If the Transactions Table has Year and Month Fields too and both locations have January and February 2009 data in them then how you will create the Report Month-wise?

Try it out on your own and check it out with my examples next week. Sample image of the output is given below for reference.

Share:

Form Bookmarks And Data Editing-3

This is the continuation of our discussion on the usage of Form Bookmarks to revisit the records which we have already visited earlier. The links to the earlier Articles are given below:

  1. Form Bookmarks And Data Editing
  2. Form Bookmarks And Data Editing-2

The discussion on Bookmarks is not complete without touching the subject of Searching and Finding records and showing it as Current Record on Form using RecordsetClone.

In our earlier examples we have used the Find Control (Ctrl+F or Edit - ->Find. . .) to search for a record and when found save its Bookmark in an Array Variable in Memory for later use.

This time we will use a different method to find the record and bring it on the Form with the use of Bookmark of the RecordsetClone of the Form.

For this method we will use an Unbound Text Box to enter the search key value and a Command Button to click and find the record. To keep the VBA Code simple this time we will use the Customers Table rather than Order Details table, because Order Details Table have several records with the same OrderIDs.

If we use Order Details table then we have to combine the ProductID with OrderID to form a unique value to retrieve a specific record among several records with the same OrderIDs. This method we have already used in the earlier example to select and display all the records we have retrieved and edited.

As I have pointed out in the earlier Articles, when we open a Form with a Table, Query or SQL Statement each record on the Form is marked by MS-Access with a unique identification tag known as Bookmark. We can create a copy of this Recordset into memory (RecordsetClone) and work with it independently. Using the RecordsetClone, with the Form Bookmark attached to each record, we can find the required record with VBA Code using the search Key Value. Once we find the target record in memory we can read that record's Bookmark and insert it into the Form's Bookmark Property to make that record current on the Form.

But remember, you cannot read the Form's Bookmark Property Value and insert it into the RecordsetClone to find the same record in memory.

On the User's point of View all she has to do is to enter the Search Key Value (CustomerID) into the Unbound Text Box and click the Command Button next to it to find that record and bring it up on the Form.

Look at the sample VBA Code given below that runs on the Command Button Click (with the Name cmdFind) after setting the Search Key Value (CustomerID) in an Unbound Text Box with the name xFind.

Private Sub cmdFind_Click() 
Dim m_Find, rst As Recordset
m_Find = Me![xFind]
Set rst = Me.RecordsetClone
rst.FindFirst "CustomerID = '" & [m_Find] & "'"
If Not rst.NoMatch Then 
     Me.Bookmark = rst.Bookmark 
End If

End Sub

The line that reads Set rst = Me.RecordsetClone copies the Form's Recordset into the Recordset Object Variable rst in Memory and the next line runs the FindFirst method of the Recordset Object to search and find the record with the given CustomerID Value.

In the next three lines we are testing whether the rst.FindFirst method was successful in finding the record or not. If found then the Bookmark of the current record in the Recordset is transferred to the Bookmark Property of the Form to make that record Current on the Form.

I have already published an Article on this method few months back with the Title: Animating Label on Search Success. You may visit that Page to copy the complete VBA Code of the Sub-Routine given above and try them out.

You must import the Customers Table and Customers Form from C:\Program Files\Microsoft Office\Office11\Northwind.mdb sample Database and modify the Form to add a Text Box and a Command Button at the Footer of the Form.

When the rst.FindFirst method finds the record and makes it current; a Label at the bottom of the Unbound Text Box will flash few times with a message indicating that the search operation was successful and that record is made Current on the Form. If the search operation failed then the Label will flash few times with the message: Sorry, not found.

This method added to the above program gives the User a quick indication whether the search was successful or not. To go to the Page to try out the Program Click here.

Share:

Form Bookmarks And Data Editing-2

In the first part of this Article, we were using the saved Bookmarks to revisit the earlier visited records one by one to take a second look, if it became necessary, to ascertain the accuracy of edited information.

The Function myBookMarks() that we have created for this purpose can be added with one more Option, (along with 1=Save Bookmark, 2=retrieve Bookmarks, 3=initialize Bookmark List) to display all the edited records together in Datasheet View.

But, this method has some side effects and must be aware of it to implement some work around methods in such situations. Here, we will try that with the Order Details Table.

In the last example we were using the Bookmark Index Number and OrderID number values as a guide to cross check with the retrieved record.

Several Products can be ordered under the same Purchase Order and all Products under the same Order will bear the same OrderIDs too. If OrderIDs are alone used in a Query Criteria to retrieve the records then all records with the same Order IDs will be displayed, irrespective of which record among them we have visited earlier.

There were no such issues when we were using Bookmarks of each record to find them again and OrderIDs were used only as a guide to cross check the retrieved record's identity.

But here, we are trying to use the OrderId Values saved in the Combo Box List as Criteria in a Query to retrieve all the edited records at one go.

This problem we can overcome if some other unique value, if available, is used in the Combo Box list. Or use one or more field values combined to form a unique value for each record and save it in the Combo Box List along with the Bookmark Index Number. This is what we are going to do now with the 4th Option of myBookMarks() Function.

We will use OrderID with ProductID combined Values and save them in the Combo Box List. The same Product Code will not appear twice under the same Purchase Order. This will ensure that the values saved in the Combo Box are unique.

The idea behind this new method is to create a Dynamic Query using the Values saved in the Combo Box list and open the Query with all the edited records from the Order Detail Table with one click.

In the fourth Option of the Function myBookMarks() we will build an SQL String using the Values saved in the Combo box as Criteria and modify the SQL string of a SELECT Query to retrieve the records. We have to create another Command Button near the << Reset Button to run this Option, so that the User can click on it to retrieve all the edited records and display them in Datasheet View at his will.

But, first let us write the Code Segment that implements this particular Option. We need few Objects and Variable declarations at the declaration section of the Function.

Dim db as Database, QryDef as Querydef
Dim strSql as String, strSqltmp as String, strCriteria as String
.
.
.
Select Case ActionCode
.
.
.
Case 1
.
Case 2
.
Case 3
.
Case 4

strSqltmp = "SELECT [Order Details].* "
strSqltmp = strSqltmp & "FROM [Order Details] "
strSqltmp = strSqltmp & "WHERE ((([OrderID]" & "&" & Chr$(34)
strSqltmp = strSqltmp & "-" & Chr$(34) & "&" & "[ProductID]) In ('"

strCriteria = ""
For j = 0 To ArrayIndex -1
   If Len(strCriteria) = 0 Then
   strCriteria = ctrlCombo.Column(1, j)
Else
   strCriteria = strCriteria & "','" & ctrlCombo.Column(1, j)
End If

Next

strCriteria = strCriteria & "')));"

Set db = CurrentDb
Set Qrydef = db.QueryDefs("OrderDetails_Bookmarks")
strSql = strSqltmp & strCriteria
Qrydef.SQL = strSql
db.QueryDefs.Refresh
DoCmd.OpenQuery "OrderDetails_Bookmarks", acViewNormal
End Select 

We are creating part of the SQL string that remains constant in the strSqltmp. Extracting the Combo Box 2nd Column Values (combined values of OrderID and ProductID separated with a hyphen character) and building the Criteria part of the Query in the String Variable strCriteria within the For. . .Next Loop. Finally we are redefining the SQL of the OrderDetails_BookMarks Query before opening it with the extracted Records.

The Combo Box Columns have Zero based Index Numbers and the second Column Index number is 1. So the statement strCriteria = strCriteria & "-,'" & ctrlCombo.Column(1, j) takes the second column value OrderID and PrductID combined String value for criteria.

The modified Code of the myBookMarks() Function with the above Option is given below.

  1. You may Copy the Code and Paste it in the Standard Module replacing the earlier Code or rename the earlier Function and save this Code separately.
    Public Const ArrayRange As Integer = 25
    Dim bookmarklist(1 To ArrayRange) As String, ArrayIndex As Integer
    
    Public Function myBookMarks(ByVal ActionCode As Integer, ByVal cboBoxName As String, Optional ByVal RecordKeyValue) As String
    '-----------------------------------------------------------------
    'Author : a.p.r. pillai
    'Date   : October-2009
    'URL    : www.msaccesstips.com
    'Remarks: All Rights Reserved by www.msaccesstips.com
    '-----------------------------------------------------------------
    'Action Code : 1 - Save Bookmark in Memory
    '            : 2 - Retrieve Bookmark and make the record current
    '            : 3 - Initialize Bookmark List and ComboBox contents
    '            : 4 - Filter Records and display in Datasheet View
    '-----------------------------------------------------------------
    Dim ctrlCombo As ComboBox, actvForm As Form, bkmk As String
    Dim j As Integer, msg As String, bkmkchk As Variant
    Dim strRowSource As String, strRStmp As String, matchflag As Integer
    Dim msgButton As Integer
    
    Dim db As Database, Qrydef As QueryDef
    Dim strSql As String, strSqltmp As String, strCriteria As String
    
    'On Error GoTo myBookMarks_Err
    
    If ActionCode < 1 Or ActionCode > 4 Then
       msg = "Invalid Action Code : " & ActionCode & vbCr & vbCr
       msg = msg & "Valid Values : 1 to 4"
       MsgBox msg, , "myBookMarks"
       Exit Function
    End If
    
    Set actvForm = Screen.ActiveForm
    Set ctrlCombo = actvForm.Controls(cboBoxName)
    Select Case ActionCode
        Case 1
            bkmk = actvForm.Bookmark
            'check for existence of same bookmark in Array
            matchflag = -1
            For j = 1 To ArrayIndex
               matchflag = StrComp(bkmk, bookmarklist(j), vbBinaryCompare)
               If matchflag = 0 Then
                   Exit For
               End If
            Next
            If matchflag = 0 Then
               msg = "Bookmark of " & RecordKeyValue & vbCr & vbCr
               msg = msg  & quot;Already Exists."
               MsgBox msg, , "myBookMarks()"
               Exit Function
            End If
            'Save Bookmark in Array
            ArrayIndex = ArrayIndex + 1
            If ArrayIndex > ArrayRange Then
              ArrayIndex = ArrayRange
              MsgBox "Boookmark List Full.", , "myBookMarks()"
              Exit Function
            End If
            bookmarklist(ArrayIndex) = bkmk
    
            GoSub FormatCombo
    
            ctrlCombo.RowSource = strRowSource
            ctrlCombo.Requery
        Case 2
            'Retrieve saved Bookmark and make the record current
            j = ctrlCombo.Value
            actvForm.Bookmark = bookmarklist(j)
        Case 3
            'Erase all Bookmarks from Array and
            'Delete the Combobox contents
            msg = "Erase Current Bookmark List...?"
            msgButton = vbYesNo + vbDefaultButton2 + vbQuestion
            If MsgBox(msg, msgButton, "myBookMarks()") = vbNo Then
                Exit Function
            End If
            For j = 1 To ArrayRange
               bookmarklist(j) = ""
            Next
            ctrlCombo.Value = Null
            ctrlCombo.RowSource = ""
            ArrayIndex = 0
        Case 4
            strSqltmp = "SELECT [Order Details].* "
            strSqltmp = strSqltmp & "FROM [Order Details] "
            strSqltmp = strSqltmp & "WHERE ((([OrderID]" & "&" & Chr$(34)
            strSqltmp = strSqltmp & "-" & Chr$(34) & "&" & "[ProductID]) In ('"
            strCriteria = ""
            For j = 0 To ArrayIndex - 1
                If Len(strCriteria) = 0 Then
                    strCriteria = ctrlCombo.Column(1, j)
                Else
                    strCriteria = strCriteria & "','" & ctrlCombo.Column(1, j)
                End If
            Next
            strCriteria = strCriteria & "')));"
     
           Set db = CurrentDb
            Set Qrydef = db.QueryDefs("OrderDetails_Bookmarks")
            strSql = strSqltmp & strCriteria
            Qrydef.SQL = strSql
            db.QueryDefs.Refresh
            DoCmd.OpenQuery "OrderDetails_Bookmarks", acViewNormal
    End Select
    
    myBookMarks_Exit:
    Exit Function
    
    FormatCombo:
    'format current Bookmark serial number
    'and OrderID to display in Combo Box
    strRStmp = Chr$(34) & Format(ArrayIndex, "00") & Chr$(34) & ";"
    strRStmp = strRStmp & Chr$(34) & RecordKeyValue & Chr$(34)
    
    'get current combobox contents
    strRowSource = ctrlCombo.RowSource
    
    'Add the current Bookmark serial number
    'and OrderID to the List in Combo Box
    If Len(strRowSource) = 0 Then
         strRowSource = strRStmp
    Else
         strRowSource = strRowSource & ";" & strRStmp
    End If
    Return
    
    myBookMarks_Err:
    MsgBox Err.Description, , "myBookMarks()"
    Resume myBookMarks_Exit
    End Function

    You can try out this Option with few changes to the Form that we have created earlier (the Form in design view is given below) by creating another Command Button and with a simple SELECT Query.

  2. First, Create a SELECT Query with the following SQL String and save it with the name OrderDetails_Bookmarks:
    SELECT [Order Details].* FROM [Order Details];
    
  3. Open the Form Order Details and create a Command Button next to the < Command Button as shown on the Form Design image given below:
  4. Click on the Command Button to select it and display the Property Sheet (View - - > Properties)
  5. Change the Name Property Value to cmdShow and the Caption Property Value to View Records.
  6. Select the On Click Property, select EventProcedure from the drop down list and click on the Build (. . .) Button to open the Form's Code Module with the following empty skeleton of Sub-Routine:
    Private Sub cmdShow_Click()
    
             End Sub
  7. Write the following line in the middle of the Sub-Routine as shown below:
    Private Sub cmdShow_Click()
        myBookMarks 4, "cboBMList"
    End Sub
    
  8. Save and Close the Order Details Form and open it in Normal View.
  9. Double-Click on the Record Selector of few records on the Form to add the Bookmark List in the Combo Box.
  10. Click on the drop down control of the Combo Box to ensure that the selected Item Codes are added into the Combo Box List.
  11. Click on the View Records Command Button to open the Query OrderDetails_Bookmarks in Datasheet View with the records that matches with the Combo Box Values.

Check the sample image of Query result overlapping the Form, displaying all the records that belong to the Combo Box List Values.

The Product Field displays Product Description rather than the Product Code that is appearing in the Bookmark Combo Box on the Main Form. The Display Width of the Combo Box in the Product Field is set to 0" to hide the Product Code in the Data View. But when you select an item from this Combo Box the Product Code is stored in the Order Details Table, because that is the Bound Column to the Table. When you double-click on the Record Selector the stored value of ProductID is taken rather than the Product Description, to combine with OrderID Value and updates the Combo Box List.

Want to find out how to open a Form with the last record that you were working on in the earlier session?, Click here?

Want to find out how to use Combo Boxes and List Boxes in different ways? Visit the following Links:

  1. Selected ListBox Items and Dynamic Query
  2. Create List from another ListBox
  3. ListBox and Date : Part-1
  4. ListBox and Date : Part-2
  5. ComboBox Column Values
  6. External Files List in Hyperlinks
  7. Refresh Dependant ComboBox Contents
Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts