Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Updating Combobox when Not in List is Active

This is all about the Limit to List Property setting of the Combo Box. When this property value is set to Yes you cannot type a new value into the combo box control. The user is forced to select/type an item from the drop-down list of the combo box.

Typing a new value into the combo box control will trigger an error message and will force you to select an item from the existing list.

Names of Months or Weeks etc. in a combo box are of constant nature and never changes or an addition is not required. But, names of new employees or names of Products, clients and so on need to be added to the combo box list, as and when the need arises.

A new item can be added to the Source Table of the combo box directly through a separate Form.  The user has to open source table editing form and add a new record.  Even if the user is prepared to do that the new item added on the source table will not appear on the combo box list on the form immediately. The user must close and open the form with the combo box to refresh and make the new item appear in the list. It is not a user-friendly approach and lot of time is wasted.

But, we can make it very easy to the user with a VBA Program. The user can type a new value into the combo box itself and with his/her permission we can add the new item into the source table and refresh the combo box instantly.

When a new value is typed into the combo box the Limit to List property setting will run our program from On Not in List Event Procedure.  After getting confirmation from the user we can add the new item into the combo box source table and refresh the combo box.

The following program adds ProductID and Product Name into the Products Table and refreshes the cboProd Combo box to make the new item appear in the list:

Private Sub cboProd_NotInList(NewData As String, Response As Integer)
Dim strProd As String, strName as String
Dim rst As Recordset, db As Database
Dim msg as string

On Error Goto cboProd_NotInList_Err

'continue without displaying error message
Response = acDataErrContinue

strProd = NewData
msg = "Product ID: " & strProd & " Not found in List!" & vbCr & vbCr & "Add it in Source File...?"

If MsgBox(msg, vbDefaultButton2 + vbYesNo + vbQuestion, "cboProd_NotinList()") = vbYes Then
    'Get Product Name from User
    strName=""
    'Repeat the loop if user presses enter key without entering a value
    Do While strName=""
        strName = InputBox("Product Name: ","cboProd_NotinList()","")
    Loop
    'Add ProductID and Name to the source Table
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Products", dbOpenDynaset)
    With rst

      .AddNew
      ![ProductID] = strProd
      ![PName] = strName
      .Update
      .Close
    End With
    
    'make combobox control source empty
    'in preparation to refresh the combobox
    Me!cboProd = Null
    'refresh the combobox
    Me.cboProd.Requery
    'now the combobox list have the new entry
    'place the new code in the combobox control source
    Me![cboProd] = strProd
 
    Set rst = Nothing
    Set db = Nothing
Else
   'if user refuse to add the new entry into source file
   'then display the error message and exit
   Response = acDataErrDisplay
End If

cboProd_NotInList_Exit:
Exit Sub

cboProd_NotInList_Err:
MsgBox Err & " : " & Err.Description,,"cboProd_NotInList()"
Resume cboProd_NotInList_Exit

End Sub

The above code is run from On Not in List Event Procedure of the cboProd Combo box.

Share:

Overlaying Sub-Forms in Real-Time

This is all about loading two or more forms into a single Sub-Form Control, one after the other at will.  Normally we place only one form into a Sub-form control and this will be done during design time.  One Main Form can have one or more sub-forms, normally linked to the main form through Link  Master Fields and Link Child Fields references.

By the way, a sub-form control is a container that holds a form object reference in the Source Object Property.  If you remove the form’s name from this property you can see that an empty control remains on the main form.  You can re-write the Source Object Property value with any form name in real-time to  open that form into the sub-form control.  Now, you got the idea how it works.

Let us look at a quick example by loading three different forms into a single sub-form control, one after the other, replacing the earlier form. Check the video given below:

[embed width="500"; height="360"]http://www.youtube.com/watch?v=71tu5LeEgZ8[/embed]

You can create this Form very easily. See the sample image given below:

  1. You can use a copy of an existing form with enough space below the existing data fields, drag another form from the navigation pane and drop it in the detail section as a sub-form.  Or you may create a blank form in design view, drag another form and drop it on Detail Section leaving enough space above the sub-form to create an Option Group Control. The drag drop action will automatically create a sub-form control and place the form within the control.
  2. Display the Property Sheet (F4) of the sub-form, while the sub-form is in selected state and change the Name Property value to subFrm
  3. Change the Name Property value of the Child Label as HeaderLabel.
  4. Create an Option Group Control above the sub-form with the names of your existing three form names as Labels.
  5. Change the Name Property value of Option Group Control to Frame1.
  6. Change the Default Value property value of Option Group control to 0.
  7. Select the After Update Event property, select [Event Procedure] from the drop-down list and click on the build (. . .) button to open the VBA Editing window.
  8. Copy and paste the following VBA Code into the Module over-writing the Private Sub Frame1_AfterUpdate(). . . End Sub lines:
    Private Sub Frame1_AfterUpdate()
    Dim i, sub_Form As SubForm
    
    Set sub_Form = Me.subfrm
    i = Me![Frame1]
    Select Case i
        Case 1
          sub_Form.SourceObject = "frm_Session"
          Me.headerLabel.Caption = "frm_Session:"
        Case 2
          sub_Form.SourceObject = "frm_Payments"
          Me.headerLabel.Caption = "frm_Payments:"
        Case 3
          sub_Form.SourceObject = "Stationary"
          Me.headerLabel.Caption = "Stationary:"
    End Select
    
    End Sub
  9. Modify the above Code to replace the Form Names in quotes with your own form names.
  10. Press ALT+Q to close the VBA Window.
  11. Change the Child-label Caption, of the Option Group control, to Form Selection.
  12. Save and close the Form.
  13. Open the Form in normal view and try out the Option Group Radio buttons to load your forms into the sub-form control , in any order you like.

Assume that you have a Main-Form (say Students) with two sub-forms (frm_Session & frm_Payments).  The first sub-form is linked to the main form using a common field StudentID, but that is not so important here.

The second sub-form control is linked to the first sub-form control (not linked to main form field value, as we do normally) using the common field SessionID .  It is important to know that to filter data in the second sub-form related to the data in the first sub-form control we must set up Link Master Fields and Link Child Field property values in the second sub-form control.  The Mater Field value should come from the first sub-form control and this is the main challenge we face.

The image of the sample form is given below:

The problem with the Link Master Fields property of a sub-form control is that it should be set with Field/Textbox name(s) at design time (will not accept any expressions or qualified names) and it will always expect these references to come from the Main Form only.  Not from within any other sub-forms on the same main form.  The simplest solution to this issue is to create an Unbound textbox on the main form and write an expression (like =[frm_Session].[Form]![SessionID]) in the textbox to obtain the current record key value from the first sub-form and use the textbox name for the Link Master Fields property value. You can set the Visible property value to No to keep this textbox control hidden.

We established the links between both sub-forms to filter the data in the second sub-form control, corresponding to the SessionID value in the first sub-form.  If you load any form that doesn't have the SessionID field into the second sub-form control Microsoft Access will prompt for the field value set in the Link Child Fields property.

View the following Youtube Video for a preview of the trick:

[embed width="500" height="360"]http://www.youtube.com/watch?v=oem4oFFQCu0[/embed]

The VBA Routines that runs on the Command Button Click Event Procedures, of Command Buttons: Stationary and Payments, are given below:

Stationary Command Button Click event procedure:

Private Sub cmdStationary_Click()
Dim frm As SubForm
Set frm = Me![frm_Payments]

With frm
    'load Stationary Form into the control
    .SourceObject = "Stationary"
    .Requery
End With
  'second Sub-Form Child Label Caption Change
  Me.Label7.Caption = "Stationary"
  'Enable Payments Command Button
  Me.cmdPayments.Enabled = True
  'Shift the Focus to cmdPayments command button
  Me.cmdPayments.SetFocus
  'Disable cmdStationary command Button
  Me.cmdStationary.Enabled = False
  Me.Refresh
  
End Sub

Payments Command Button Click event procedure:

Private Sub cmdPayments_Click()
Dim frm As SubForm
Set frm = Me![frm_Payments]
With frm
    .SourceObject = "frm_payments"
    .Requery
End With
'Change Header Label Caption
Me.Label7.Caption = "frm_payments"
'Enable Stationary Command Button
Me.cmdStationary.Enabled = True
'Change focus from cmdPayments
'in preparation to disable the Command Button
Me.cmdStationary.SetFocus
'Disable cmdPayments Command Button
Me.cmdPayments.Enabled = False
Me.Refresh

End Sub

You may download the sample databases for a quick view of this trick.



Download Demo SubFormTrick2003.zip





Download Demo SubFormTrick2007.zip


Share:

Form Footer Section Dropping Down Magic

This method always fascinated me after seeing it in programs. It goes something like this:

When you open a dialog control it is presented with several options to select from. In addition to that the dialog control will have a Command Button with caption, something like Advanced... or More... indicating that more options available to select from, and when you click on the Command Button the bottom portion of the Form extends down showing more options in the newly displayed area of the form.

I have made an attempt to mimic this method on a Form. Screen shot of the Form, before and after expanded modes are given below.

Form in normal view, check the bottom portion of the form.

Look at this Form View; the bottom portion of the form is expanded down to display additional option.

Check the following video to see how it works.

[embed width="500" height="360"]http://www.youtube.com/watch?v=wUlUFT-HCJo[/embed]

Now, you have a general idea what we are trying to do here. We have to design two Forms, create two Macros, some small programs on the Form's VBA module. This Project also demonstrates how to create and use custom properties on the Form (besides the Properties that you see on the Property Sheet) to store values on the Form itself and retrieve them when you need it.

Remember, we always record information on tables and Forms are used to display/edit the data and store them back on the table itself. You never thought of this angle that you can store values on the form itself, but not as much you can store in a table. You can do several tricks with this method. I will give few links of Articles, which I have published earlier, to look at at the end of this page.

  1. Open a new Form in design View.
  2. Right-Click on the Detail Section of the Form and select Form Header/Footer to display the Header and Footer Sections of the Form.
  3. Shrink the Footer of the Form, so that there is no space is showing on the Footer of the Form.
  4. Select the Header of the Form and display the Property Sheet (F4).
  5. Increase the Height Property value to 0.6”.
  6. Click on the Detail Section and adjust the Height of the Detail Section to 2.166”.
  7. Click on the left top corner of the Form (or select Form from the Selection Type control – Access 2007) to select the Form’s Property Sheet and change the Width property value to 4.875”.

    Note:  If your Form size is little bigger or smaller, doesn’t matter.

  8. Create a Header Label with the Caption ‘CONTROL CENTER’ and change the Font-size to 14 or 16 or whatever size you prefer to look like a heading.  I have copied the label and placed over the first one, slightly up and to the right and changed the Font-color of the first label to a lighter color to give some 3D effect to the heading.
  9. Click on Use Control Wizards button on the toolbar to turn it on, if it is not in selected state.
  10. Select the Options Group and draw a control on the left side of the Form, on the Detail Section, big enough to add four lines of options.

    Check the Design View of the Form given below:

  11. Type four Labels one after the other on the control as shown on the design, you can type any labels, this is for design purpose only. 
  12. While the Option Group is in selected state, display the Property Sheet (F4) and change the Name property value to Frame1.
  13. Create another Option Group control to the right side of the earlier one and add four items.  Give the third label caption to the name of a Report in your database.
  14. Change the Name Property Value of the Option Group control to Frame2.
  15. Change the child label captions to Forms and Reports of Frame1 and Frame2 respectively and position them above those controls.
  16. Create four Command Buttons as shown on the design above and change their caption property values as Quit, Open, Print and More…
  17. Click on the Quit Command Button to select it.
  18. Display the property sheet (F4) and change the Name property value to cmdQuit.
  19. select the On Click Event property and select [Event Procedure] from the drop-down list and click on the build button (. . .) to open the VBA Module of the Form.
  20. Copy and Paste the following VBA Code, overwriting the existing lines of Code in the Form Module.
    Option Compare Database
    Option Explicit
    Dim db As Database, doc As Document
    
    Private Sub cmdQuit_Click()
    On Error GoTo cmdQuit_Click_Err:
      
      Me![Frame1] = 1
      Me![Frame2] = 1
    
    DoCmd.Close acForm, Me.Name
    
    cmdQuit_Click_Exit:
    Exit Sub
    
    cmdQuit_Click_Err:
    MsgBox Err & " : " & Err.Description, , "cmdQuit_Click()"
    Resume cmdQuit_Click_Err
    
    End Sub
  21. Select Close and Return to Microsoft Office Access from File Menu of VBA Window (or press ALT+Q) to close the VBA Window.

    For now, we will close the Form with a name (FormA1) and we have to do some work outside this form and then we will come back to the Form.

  22. Before closing the Form, if you need any refinement to the design then do it now. We are not supposed to do any design changes to this Form later except copy pasting few programs on the VBA Module. Save and close the Form with the name FormA1
  23. Right-click on FormA1 and select Copy from the context Menu.
  24. Right-Click on the Navigation pane Header and select Paste from the displayed Menu.
  25. Change the name of the copy to FormB1.

NB:  I tried to do this trick with a single Form and VBA Programs, but it was not at all successful.  If anybody could do this with a single Form I would be very happy to know how you did it?

  1. Open Form FormB1 in design view.
  2. Click on the right-most Command Button (with the Caption: More...) and change the caption to …Less.

    See that the position of the command button is not changed at all. If you are not sure then compare the left and top position values with command button on FormA1, if not same then take the value from FormA1 and change to match. See that you don't make any other changes to the Detail or Header Section designs on FormB1.

  3. Expand the Footer Section down (check the second image from top) to give enough space to create a Command Button.
  4. Create a Command Button, at the right side of footer section, display the property sheet (F4), change the Caption property value to Preview, change the Name property value to cmdPreview.
  5. Select the On Click Event Property and select [Event Procedure] from the drop-down list and click the build button (. . .) to open the VBA Module.
  6. Copy and paste the middle line Code and paste it between Private Sub cmdPreview_Click() and End Sub lines:
    Private Sub cmdPreview_Click()
        DoCmd.OpenReport "myReport", acViewPreview
    End Sub
  7. Change the report name (myReport) with the name of one of your own Report in the database.
  8. Press ALT+Q to close the VBA Editing Window.

    You may change the Footer background color to something different, or leave it as it is. 

Perhaps, you got the idea by now that we need two identical forms (not exactly identical) to pull this trick to the User.  Before proceeding further I will tell you how we are going to show this trick to the User.

Opens FormA1 through a Macro (macFormA1), on a specific location on the Screen.

It is important that we open both Forms on the same location inter changeably to give it a feel of quick transition. Both Forms are of the same size and design, except the second form is having it's footer section extended with a command button.

When the Command Button with the Caption More... is clicked; the second form FormB1 is opened through a second Macro (macFormB1), overlaying the first form on the exact position on the screen and closes the first Form.  But, the user will not see this quick change of form, like different frames in a movie. 

Since, we brought the second form on the same position on the screen it will look like the first form's footer section is dropping down to reveal the Preview Command Button. The Caption More... of the first form Command Button will look like changed to …Less We have written a program to open a Report, from your database, in Print Preview, when the Preview Command Button is clicked.  After that, when the Command Button with the caption …Less is clicked the first Form FormA1 is opened again on the same position of the second form FormB1 and closes FormB1 immediately.  It will look like that the footer section of the form is withdrawn (shrunk).  We are showing only one form to the User at a time. 

I tried to do this with only one Form by expanding/shrinking the Footer Section of the Form with VBA Programs and Macros.  But, could not succeed during the repetition of the same action and the macros refused to run along with other issues.

Now, going back to our current mode of trick, the major issue is with the Option Group controls on both Forms.

For example, the User selects a particular option (say option 3 under Reports to print Preview the Report) on the first Form. Clicks on the More… Command Button to display the Print Preview option.  At this point we have to open the second form, with the Footer Section extended, and we have to ensure that the user’s selection of the option on the first form appears on the second form as well.  The user will not know that the form is changed.  This was the most difficult part in this two form based method.

The big question is where to save the user’s selection of option number(s) (the user may make selections on Option Group-Frame1 and Option Group-Frame2) in order to transfer it to the second form.  We plan to do this by saving these value on form FormA1 itself, in custom made properties, with the names: Frame1 and Frame2.  We can save/retrieve values through VBA and you can do this even when the FormA1 is in closed state. 

We need a one time run of a program to create the custom properties Frame1 and Frame2  on FormA1.  Copy and Paste the following program into a new Standard VBA Module:

Public Function createProperty()
Dim db As Database, doc As Document
Dim prp1 As Property, prp2 As Property

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("FormA1")
Set prp1 = doc.CreateProperty("Frame1", dbInteger, 1)
Set prp2 = doc.CreateProperty("Frame2", dbInteger, 1)
doc.Properties.Append prp1
doc.Properties.Append prp2
doc.Properties.Refresh

Set doc = Nothing
Set prp1 = Nothing
Set prp2 = Nothing
Set db = Nothing
End Function

Click somewhere in the middle of the Code and press F5 to Run the program to create Frame1 and Frame2 custom properties on the form FormA1. The CreateProperty() method accepts three parameters.

  • First parameter “Frame1” is the Name of the custom property.  You can use any text value as name.
  • Second parameter dbInter is a constant indicates that the custom property Frame1 will store Integer Type data.
  • Third parameter is the initial value stored in the custom property Frame1.

Two custom properties are created and appended to the properties collection.

Don't run the program a second time, you will end up with errors.  You cannot view the user-defined properties on the Property Sheet of the Form.

We don’t have to create these custom properties on the other form: FormB1

Note: As you can see in the above program that the collection of Forms are referred to as a Container Object.  Tables (Queries also comes under Tables), Forms, Reports, Scripts (Macros)  are Container Objects.  Each Container Object consists of several Documents.  Each form you create is referred to as a Document under Forms Container.  These are some of the object, there are others too.  You may search for Access Object Model Reference in VBA Help and select Object Model Map from the Table of Contents, to see the hierarchy structure of Access Objects, Properties etc.  You may visit the link: Saving Data on Forms not in Table for a useful method we have published earlier, using custom property on the form.

Now, we have to create two Macros to control the open/close events of FormA1 and FormB1.  With macros we can define where exactly the form should appear on the Screen.

  1. Select Macro from Create Menu to open a new macro in design view.
  2. Add the following Actions and parameters on the macro:
    • Select Echo from the Action column and set No in the Echo On parameter in the Arguments column.
    • Select OnError from the Action column in the next row and set Next in the Go to Argument.
    • Select Close from the Action column in the next row and select Prompt in the Save Argument.
    • Select OpenForm from the Action column in the next row and set FormA1 in the Form Name control, set Form in the View argument and set Normal in the Window Mode argument.
    • Select MoveSize from the Action column in the next row and set the values 1”, 1”, 4.875” in Right, Down, Width arguments respectively.
  3. Save and close the Macro with the name macFormA1.
  4. Make a copy of the macro macFormA1 and name the new macro as macFormB1.
  5. Open macro macFormB1 in design view and make only one change in the OpenForm Action line Form Name argument.
  6. Change the Form Name to FormB1.
  7. Save and close the macro.

Now, only two things left to do on both forms to complete our design tasks.  Two VBA Subroutines (Form_Load() and Form_Unload() Event Procedures) to be copied and pasted into the VBA Modules of both forms.  The macros we have created must be assigned to the On Click Event of the More… and …Less captioned Command Buttons.

  1. Open FormA1 in Design View.
  2. Click on the Command Button, with caption More…, to select it.
  3. Press F4 to display the Property Sheet and select On Click Event Property.
  4. Select macFormB1 from the drop-down list of the property.
  5. Press ALT+F11 to display the Form’s VBA editing window.
  6. Copy and Paste the following Sub-Routines, below the existing Program codes on the module:
    Private Sub Form_Load()
    'Load values from the custom properties of FormA1
    'into Frame1 and Frame2
    On Error GoTo Form_Load_Err
    
      Set db = CurrentDb
      Set doc = db.Containers("Forms").Documents("FormA1")
      
      Me!Frame1 = doc.Properties("Frame1").Value
      Me!Frame2 = doc.Properties("Frame2").Value
      Me.Repaint
    
    Form_Load_Exit:
    Exit Sub
    
    Form_Load_Err:
    MsgBox Err & " : " & Err.Description, , "Form_Load()"
    Resume Form_Load_Exit
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
    'Before closing the Form, save the Option selections
    'of the User into the custom properties
    On Error GoTo Form_Unload_Err
    
    Set db = CurrentDb
    Set doc = db.Containers("Forms").Documents("FormA1")
    
    doc.Properties("Frame1").Value = Me!Frame1
    doc.Properties("Frame2").Value = Me!Frame2
    doc.Properties.Refresh
    
    Form_Unload_Exit:
    Exit Sub
    
    Form_Unload_Err:
    MsgBox Err & " : " & Err.Description, , "Form_Unload()"
    Resume Form_Unload_Exit
    
    End Sub
  7. Save and Close FormA1.
  8. Open FormB1 in Design View.
  9. Click on the Command Button with caption …Less to select it.
  10. Press F4 to display the Property Sheet and select On Click Event Property.
  11. Select macFormA1 from the drop-down list of the property.
  12. Press ALT+F11 to display the Form’s VBA editing window.
  13. Copy and Paste the above Sub-Routines, below the existing Program codes on the module of this Form also.
  14. Save and Close FormB1.

Now, it is time to test our form footer drop-down trick.  But, before we open the forms we must change some Global settings of Microsoft Access.

  1. Click on Office Button and select Access Options.
  2. Select Current Database,  select Overlapping Windows Radio Button under Document Window Options.

    A message is displayed to close Microsoft Access and open it again to take effect of the new settings.

  3. Restart Microsoft Access and open your database.

If we open FormA1 directly from the navigation panel the form may not open at the exact location (i.e. 1” from top and 1” from left side of the window) we have specified in the macros (macFormA1 and macFormB1).  So, we must see that we run the first macro macFormA1 either from a command button click Event Procedure from another form or running the macro directly from the navigation panel.

To try the trick we will run the macro macFormA1 directly from the navigation panel.

  1. Display the macros list in the navigation panel.
  2. Double-click on the macro macFormA1 to run it and open FormA1 in normal view, on the exact location we specified in the macro.
  3. Select any one of the option from the Reports Option Group. 

    Remember, we have added one of your Report on the Preview Command Button click event procedure just to see that the Report runs from there.  We are not running any validation check on the Reports Option Group to see which option the User selected to run the report.  But, I asked you to select one of the option from there just to see how the selected option value is transferred to the second form FormB1You may select any value from both Option Groups (Frame1 and Frame2) to watch the change on the second Form.

  4. Click the More... Command Button to extend the form down to show the Preview Command Button.
  5. Check whether the selected report option appears on FormB1 in the Reports Option Group also. The command button with More... caption now changed to …Less.
  6. Click on the Preview command button to open your report in print preview.
  7. Click on the …Less command button to hide the footer section of the form. This will change the command button caption to More... again.
  8. Click on the Quit Command Button to close the Form and close the Report also.

Well, how was that for a trick?  After all it is all about playing tricks in front of the user’s unsuspecting eyes. As far as the user is concerned he/she doesn’t have a chance to catch the action in slow motion and to know how it works.

I know it took lot of explaining in a very lengthy post, but it is all about two forms, with almost same design, showing one after the other on the same location.  You learned how to store program parameter values on the form itself.  This is a very powerful feature that you can put to use in programs to play many tricks with it.  Take a look at another trick we have published earlier: Create your own color palette.

I don’t know about later versions of Access (I am still using Access 2007) have anything that could help the trick to achieve the same effect with some lighter methods.

You may download the sample database from the Links given below.



Download Demo FormTrick2003.zip





Download Demo FormTrick2007.zip


Share:

PIE Chart and live data on Form

Students' Exam Scores of five different Subjects are recorded in a Table: tbl_Students. A sample Table image is given below:

The name of the student, from the current record on the Form, is used as criteria in a Query, to filter the exam records of that student, as source data for the PIE Chart on the same Form. Sample Form image with the PIE Chart is given below:

When any one of the exam record, out of five subjects of a  particular student become current on the Form the PIE Chart should display the marks of all five subjects and percentage on Maximum Total Marks, on the same Form.  This is the tricky part; we cannot design a Graph Chart on the Form, while the Form is in normal view to filter the data in the query as source for the same Chart. 

We need three simple queries to filter and prepare the data for the Chart.  Besides that we need a temporary table to store the Total of all Obtainable Maximum  Marks (to calculate the percentage of total Obtained marks) on each subject for the chart (100 x 5 = 500). The image of the temporary table is given below:

We will divide the task in three parts, so that the procedure is easy to understand:

  1. Part-A: Create tbl_Students and tmp_MaxMarks.
  2. Part-B: Design Form frm_Students for tbl_Students and create three simple Queries to prepare data for the Chart.
  3. Part-C: Create a PIE Chart on frm_Students Form.

Part-A:

  1. Create a Table with the Structure, as shown in the first image on the top and name the table as tbl_Students.  You can ignore the two empty fields on the Table.  As you can see the table contains three students’ exam scores of five subjects, out of hundred each.
  2. Key-in those sample data into tbl_Students Table.
  3. Create a small table with two fields, with the structure shown on the above image and save it with the name tbl_MaxMarks.
  4. Create a single record with the sample data, shown on the image above.

Part-B:

  1. Design a Form in Column format, using tbl_Students, leaving enough space at the right side to create the PIE Chart. You can use the Form Wizard to create the Form quickly and save the Form with the name frm_Students.

    We will create three Queries, before we proceed with some more work on the Form.

  2. Create the following Queries by copying, pasting the SQL String into new Query’s SQL Editing Window and saving it with the name as indicated:

    Query-1: StudentsQ

    SELECT [Student] & " " & [Subject] AS [Desc], tbl_Students.Marks
    FROM tbl_Students
    WHERE (((tbl_Students.Student)=[forms]![frm_Students]![student]));
    

    Query-2: MaxMarksQ

    SELECT tmp_MaxMarks.Desc, [MaxMarks]-DSum("Marks","StudentsQ") AS Max_Marks
    FROM tmp_MaxMarks;

    Query-3: UnionPieChart

    SELECT StudentsQ.* FROM StudentsQ
    UNION ALL SELECT MaxMarksQ.* FROM MaxMarksQ;
  3. Open frm_Students in Design View.
  4. Create two Text Boxes below the existing fields.
  5. Click on the first Text box and press F4 to display the Property Sheet.
  6. Change the Name Property value to Obtained.
  7. Type the following expression into the Control Source Property:
    =DSum("Marks","StudentsQ")
  8. Change the Caption Property Value, of the Child Label of the Textbox, to Total Obtained:.
  9. Click on the second Textbox to select it.
  10. Write the following expression into the control Source Property:
    =[Obtained]/DLookUp("MaxMarks","tmp_MaxMarks")
  11. Change the Format Property Value to Percentage.
  12. Change Caption Property value of the Child Label of the Textbox to % on Total:.
  13. Save the Form and open it in Normal View.

Let us run a review of what we did so far.

We have created the main table tbl_Students and designed the Form frm_Students, using tbl_Students.

While the frm_Students is active, the StudentsQ filters the data using the current student's name (say John) on the Form as criteria.

Second Query (MaxMarksQ) Sum ups the total obtained marks from StudentsQ. Subtracts it from Maximum Marks of all five subjects (500) from tmp_MaxMarks. The difference of these two values,if any, is used for calculating the percentage of marks the student lost. In other words if this figure on the PIE chart shows as 10% then the student could only obtain 90% aggregate marks out of 500.

Third Query: UnionPieChart combines the Data of StudentsQ and MaxMarksQ queries and used as Data Source for the PIE Chart.

Part-C:

Our PIE Chart should be created on the frm_Students, but the Form should be kept open in normal view to filter the data in StudentsQ for the chart.  We can create the Chart only when the form is in design view. I hope you can see now why we need a little trick to pull this off.

Now, the frm_Students Form is in open state and the exam data of John (the first student on the Form) is filtered and available in StudentsQ. We should go through the following steps to create the PIE Chart on the frm_Students.

  1. While keeping the frm_Students in Form View, open a new Form in Design View.
  2. Enable Use Control Wizard Button on the Toolbar above and select Insert Chart Tool from the Controls group.
  3. Draw a Chart in rectangular shape, large enough to show the formatting (like Value Labels, Title etc.) we are going to do, like the sample image given above.
  4. Select Queries from the View group from the Wizard control and select UnionPieChart from the displayed query list.
  5. Click Next to proceed to the next screen and click on >> button to select all the displayed fields for the chart.
  6. Click on the PIE Chart Icon to select it and click Next button twice then type the following as title on the Title control:

    Max. Marks Each=100, 500 Total

  7. Select No, don't display a Legend Radio-button then Click Finish.
  8. Double-Click on the PIE Chart to change it into Edit Mode.
  9. Right-Click on an empty area of the Chart to display the context menu.
  10. Select Chart Options from the displayed Menu.
  11. Select the Data labels Tab and put Check Marks in Category Name, Values and Percentage.
  12. Click OK to close the control.

    We will reduce the size of the PIE slice labels to a smaller font size.

  13. Click on one of the PIE slice Labels. this action will select all the Labels together.
  14. Change the Font size to something like: 12 points, using the Font/Size Tool above. 

    Completed design of PIE Chart is given below.

  15. Click outside the Chart Object on the Form, to exit from Chart-Edit Mode, but the chart will be still in selected state.
  16. Right-Click on the chart, to display the shortcut menu, and click Copy to copy the Chart-Object on the Clip-Board.
  17. You may save the Form with a name and close it, if you would like to keep it safe. But, we don’t need it any more.
  18. Now, Change the frm_Students into Design View.
  19. Click somewhere on the Detail Section of the Form to make Detail Section current.
  20. Right-click on the Detail Section and select Paste from the displayed menu.
  21. Drag the PIE Chart and place it to the right side of data fields.
  22. Click on left top corner of the Form and display the Property Sheet (F4) of the Form.
  23. Select the Event Tab of the Property Sheet and click on the On Current property.
  24. Select Event Procedure from the drop-down list and click on the build (. . .) button to open the VBA editing window.
  25. Write the following statement in the middle of the Form_Current() Event Procedure:
    Me.Refresh

    When completed the procedure will look like the following:

    Private Sub Form_Current()
         Me.Refresh
    End Sub

    When you move the records on the Form, from one to the other,the Form_Current() event procedure will update the recordset with the change on the StudentsQ as well.

  26. Close the VBA editing window.
  27. Save the frm_Students Form and open it in Normal View.

The first five records on the Form belongs to the student John. If you move one record at a time you will not find any difference on the Chart up to the fifth record, because values of all the five records are shown on the PIE Chart. The sixth to tenth records belongs to the second student and 11th record onwards belongs to the third student. You may type 6 or 11 on the Record Navigation Control directly to quickly display other students marks on PIE Chart.

  1. MS-Access and Graph Charts
  2. MS-Access and Graph Charts-2
  3. Working With Chart Object in VBA
  4. Column Chart and VBA
  5. Pie Chart Object and VBA
  6. Missing Lines in Line-Chart
  7. Pie Chart and Live Data on Form
  8. Scaling Chart Object
  9. Cross-Tab Union Queries for Chart
Share:

External References in Conditional Formatting Expression

Normally, we use a Form/Report Textbox value in Conditional Formatting expressions, to set the Font or Background color of a control.

For example:

FIELD VALUE IS BETWEEN 1 and 10  - whatever formatting you define will be displayed on the selected field, provided the field value is between 1 and 10.

OR

FIELD HAS FOCUS – the conditional formatting become visible when the field become active.

OR

EXPRESSION IS [ID]=5 OR [LastName] = "Nancy" – formatting is applied to a field, depending on the current values of two other fields used in the expression.

The key values in capital letters are selected from a dropdown control on the Conditional Formatting Dialog Control. We will take the last example for our new method of reference and let us see how it can be used differently. 

In the above expression, both [ID] and [LastName] controls are on the same Form/Report and refers to the current record values on the Form/Report.  This is the normal procedure.

But, both values referenced in the expression can come entirely from other objects like: from a control of another open Form or from a Field of another Table.  As far as the table is concerned it is not necessary to keep the table open like the other Form, because we are going to use the Dlookup() Function in the expression to pick the value from the table.

When the above example is re-written to reference external field values it will look like the expression given below.

EXPRESSION IS Forms![Orders]![OrderID] = Dlookup("Order_ID","tbl_OrderParam")

Assume that the above Conditional Formatting expression is written on a Field (say the Amount field) of frm_myForm.

If the current record OrderID value of Orders Form (a second form in open mode) and the Order_ID value of tbl_OrderParam Table (the expression assumes that the Parameter Table has only one record in it) matches then apply the defined conditional format on the active Form's (frm_myForm) Amount Field.

Try it out yourself and find out how it works?

Share:

Storing Bitmap Images in OLEObject Attachments Fields

MS-Access Tables have OLEObject/Attachment Field (Access2000/Access2007) to store and display images on Forms/Reports.

Example: Storing of employee’s photo or Product’s image on data table.

Depending on the Staff size of your Company or number of product items in stock the number of images stored directly into their respective table can increase the database size considerably.  In Access 2007 the Bitmap Images (.BMP image files) stored in Attachment field are automatically compressed and reduced to the size of a .JPG image.  In either case the database size will increase, on addition of each image in table.  If you have hundreds or thousands of images to store this way then you can imagine what will happen.  The maximum size of a database can be only 2 Giga Bytes.

But, if you can foresee this issue of your Project and plan to store the required images in a dedicated folder on the disk (Server or Local depending on the User requirements) then you don’t have to bother about the database size at all.  But, you should be able to identify and pick the correct image, related to a particular record from disk easily, and display it on Form or Report, whenever the need arises.

It is important that we organize the images on disk in such a way that we can easily identify which image belongs to which record.  Once this is property planned and executed we can pick the correct image and display it on Form/Report, in an Image Control very easily.

The easiest approach is to give names to images with some unique field value of the related table, like Employee ID of Employee record.  For example, an employee’s photo name can be the Employee’s ID value, like 1.bmp, 2.bmp etc.  Each record on Products Table will have Product Codes as unique values and images can be named with product code of each item.

When a particular employee’s record is Current on the Form we can read the employee code from the form and add the image extension (like me![ID] & “.jpg”) to create the image name and load it from disk into an image control on Form. 

If you prefer to create image type smaller in file size, to save disk space, then you may choose any one of this types: .png, .jpg or .gif. GIF images are lesser in size but image quality may not be good.  Select only one of this image type and save all the images in that type, say .jpg, then the program will be a simple routine, to load the image into an image control on the form/report. 

We need only a small Sub-Routine that runs on the Form_Current() Event Procedure to load the picture into the Image control.

Let us prepare for a trial run of a small Program.

  1. Import the Employees Table from Microsoft Access Northwind.mdb sample database.
  2. Use the Form Wizard to create a Quick Form in Column Format.
  3. Move or resize the Fields (or remove some unwanted fields) so that we can add an Image Control on the Form, big enough to display sample images, which you are going to create now.

    A sample Employees Form Design is given below, with the Image Control on it:

  4. Select the Image Control from the Toolbox and draw an Image Control on the Form, as shown above.
  5. Display the Image Control’s Property Sheet (F4) and change the Name Property value to ImgCtl.
  6. Save the Form with the name Employees.

    Note: As I have mentioned earlier, the Employee ID field values are 1,2,3 etc. We will create few images with the names: 1.jpg, 2.jpg, 3.jpg etc.

    If you were able to Import the Employees Table from Northwind.mdb then you are lucky, you have employee’s photo bitmap images in the OLEObject field.  You can save these images on disk.  I am using Access2007 and I will explain the procedure of Access2007 to save the image from Employees Table to disk.  If you are using any other Access Version the menu options may be different.

    • Open Employees Table in Datasheet View.
    • Find the Photo field of the first record, the Employee ID of this record is 1.
    • Right-Click on the Photo field with the Bitmap Image text, select Open from the Bipmap Image Object option.  The image will open in Paint program.
    • Before saving the image on disk create a new folder C:\images.
    • Use Save As… option to save the image in folder C:\images with the name 1.jpg
    • Save few more employee photos in this way.

    If you could not save images from Employees Table then open any image from your disk in Paint Program and save them as 1.jpg, 2.jpg, 3.jpg etc. in C:\images Folder.

  7. Open the Employees Form (you have saved in Step-6) in Design View.
  8. Press ALT+F11 to display the VBA Window.
  9. Copy and paste the following Code into the VBA Window of the Form, below the Module Global Option: Option Compare Database.
    Private Sub Form_Current()
    Dim strImagePath As String, pic As String
    On Error GoTo Form_Current_Err
    'image folder
    strImagePath = "c:\images\"
    'create image name
    pic = Me![ID] & ".jpg"
    strImagePath = strImagePath & pic
    'validate image name and location
    If Len(Dir(strImagePath)) > 0 Then
    'image found, load it into the image control
      Me!ImgCtl.Picture = strImagePath
    Else
    'image not found, make image control empty.
      Me!ImgCtl.Picture = ""
    End If
    
    Form_Current_Exit:
    Exit Sub
    
    Form_Current_Err:
    'Not necessary to display a message here
    Resume Form_Current_Exit
    End Sub
  10. Save and close the Form.
  11. Open the Form in Normal View.

    If everything went on well then you will see the first employees photo in the image control.  Sample Screen-shot of Employee Form with the photo is given below:

  12. Use the record navigation control to move to next record and display other images also.

Image Display on Report.

A sample Employees' Report Design, with the Image Control is given below.

  1. Design a Report using few fields from the Employees Table, as shown above, and see that you are using the Employee [ID] Field on the Report.  This is important because we need this number to create the image name corresponding to the record in print.
  2. Insert an Image Control on the Report, as shown in the above Image.
  3. Display it’s Property Sheet and change the Name Property value to ImgCtl.
  4. Click somewhere on the empty area of Detail Section of the Report.  If you have closed the Property Sheet then press F4 to display it.
  5. Select the On Format Event Property and click on the build ( . . . ) button to open the VBA Window of the Report.
  6. Copy and Paste the following Code replacing the Detail_Format() Event procedure opening and closing statements:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim strImagePath As String, pic As String
    On Error GoTo Detail_Format_Err
    'image folder
    strImagePath = "c:\images\"
    'create image name
    pic = Me![ID] & ".jpg"
    strImagePath = strImagePath & pic
    'validate image name and location
    If Len(Dir(strImagePath)) > 0 Then
    'if found load the image into the image control
      Me!ImgCtl.Picture = strImagePath
    Else
    'make image control empty.
      Me!ImgCtl.Picture = ""
    End If
    
    Detail_Format_Exit:
    Exit Sub
    
    Detail_Format_Err:
    'Not necessary to display a message here
    Resume Detail_Format_Exit
    
    End Sub
  7. Save the Report and close it.
  8. Open the Report in Print Preview.

    Sample Report Preview is given below:

If you want to give more flexibility to your Project, with all the four image types (.bmp, .png, .jpg and .gif) then you may use the following modified Code:

Private Sub Form_Current()
Dim strImagePath As String, pic As String
Dim strImagePathName As String, strI(0 To 3) As String
Dim j As Integer, strType As String

On Error GoTo Form_Current_Err

strI(0) = ".bmp"
strI(1) = ".png"
strI(2) = ".jpg"
strI(3) = ".gif"

strImagePath = "c:\images\"
pic = Me![ID]

strType = ""
For j = 0 To UBound(strI)
  strImagePathName = strImagePath & pic & strI(j)
  If Len(Dir(strImagePathName)) > 0 Then
     strType = strI(j)
     Exit For
  End If
Next
  
strImagePathName = strImagePath & pic & strType

If Len(strType) > 0 Then
  Me!ImgCtl.Picture = strImagePathName
Else
  Me!ImgCtl.Picture = ""
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
'Not necessary to display a message here
Resume Form_Current_Exit

End Sub

Note: Report must be in Print-Preview mode to view the images on Report. The Images may not appear in Layout View.

Share:

Opening External Access Report inside Active Database

Last week we have learned how to print a Report from another Ms-Access Database. With the help of a VBA Program we have created a separate MS-Access Application Window, opened a database within that Application Window and printed a Report from it. We have used the Application.Docmd.OpenReport command to open the Report in Print Preview. The same code we have used in an Excel Application (in a Macro enabled Workbook) to Print Preview the same MS-Access Report.

The procedure was somewhat complicated and difficult to understand, if you are not that familiar with VBA. If you need to print a Report or open a Form from an external MS-Access database, inside the current database then we can do it in a much simpler way.

Once we go through the following simple procedure the external database's Report can be opened within the current database window in Print Preview or Print mode, depending on how you set up the procedure. With the same ease of operation we can open Forms as well.

The procedure goes something like the following:

First let us define some names of databases, Report and Form involved in this procedure, for references.

  • Current Database Name: DatabaseA.mdb
  • Second Database Name: DatabaseB.mdb
  • Report to Print Preview from DatabaseB.mdb: myReport.
  • Form to open from DatabaseB.mdb: myForm.
  1. Open DatabaseB.mdb (you may select any database you have, with at least one Report and One Form).
  2. Open the VBA Editing Window (ALT+F11).
  3. Insert a new Standard Module (Insert - - > Module).
  4. Copy and paste the following VBA Code into the Module:
     
    Public Function myReportOpen()
    'Replace myReport with your own Report name in quotes.
       DoCmd.OpenReport "myReport", acViewPreview
    End Function

    Calling the above VBA Function from DatabaseA.mdb will open myReport from DatabaseB.mdb and will appear in DatabaseA Application Window, in Print Preview.

  5. Copy and Paste the following VBA Code below the earlier Function myReportOpen():
     
    Public Function myFormOpen()
     'Replace myForm with your own Form Name
       DoCmd.OpenForm "myForm", acViewNormal
    End Function

    We have created two simple functions in DatabaseB and we have to ensure that DatabaseA is not having any function with the same name.

  6. Save the VBA Module with the functions and close DatabaseB.mdb.
  7. Open DatabaseA (any database you would like to see the Report/Form open from DatabaseB.mdb).
  8. Open VBA Editing Window (ALT+F11).
  9. Select Tools - - > References.

    You will see the VBA References Dialog Box as shown below, with a lengthy list of available Library Files and the files already selected for the current project, with check marks, on top of the list.  You must browse and find DatabaseB.mdb from it’s parent folder, select it and click Open to attach it to the current database as a Library File.

  10. Use Browse... button on the References Dialog Box, find DatabaseB.mdb from it's folder and open it. See that you have selected 'Microsoft Access Databases' in the Files of Type control, otherwise Database file names will not appear.

    The selected Database's Project Name (can be different from your database name) will appear at the bottom of the Library Files list.

  11. Click OK to close the Dialog Box.

    Now, the functions myReportOpen() and myFormOpen() of DatabaseB are visible in DatabaseA. It means that you can call those functions from DatabaseA.mdb to open myReport or myForm from DatabaseB.mdb and to display them in DatabaseA Window.

    Note: When Function myReportOpen() is called from DatabaseA.mdb, first the function will look for myReport in DatabaseB (the function's parent database), if found it opens from there and displays in the current database, otherwise it will search for the Report, with the same name, in DatabaseA.mdb and opens from there. 

    Keep this particular aspect in mind about library functions and it's ability of searching for objects within the library file first, if reference of objects appears in the library functions, when you design Custom Wizards or some common Form useful in several Databases.

    At this point you can test the programs by running it from the Debug Window.

  12. Press CTRL+G to display the Debug window (Immediate Window), if it is not already visible.
  13. Type the following Command in the Debug Window and press Enter Key:

    myReportOpen

    The Report is open in Print Preview mode behind the VBA Window, minimize the VBA Window to see the Report. You may run myFormOpen() function also to see that it works too. You may create two Command Buttons on a Form so that you can run the programs from the Command Button Click Event Procedure.

  14. Open a Form and create two Command Buttons on it.
  15. Select the first command button and display it's Property Sheet (F4).
  16. Change the Name Property value to cmdReport.
  17. Find the On Click Event property and select [Event Procedure] from the drop-down list.
  18. Click on the build button (. . .) at the right end of the property to open the VBA Window.

    You will find the opening and closing lines of a Sub-routine, similar to the program lines given below, except the middle line.

    Private Sub cmdReport_Click()
       myReportOpen
    End Sub
  19. Copy the center line of the above procedure and paste it into the middle of the VBA subroutine.
  20. Similarly, name the second Command Button as cmdForm and follow the same steps (16 to 19) to create the following Sub-Routine in the VBA Window.
    Private Sub cmdForm_Click()
       myFormOpen
    End Sub
  21. Save the Form.
  22. Open the Form in normal view, click on the Command Button(s) to open myReport/myForm from DatabaseB.mdb.  The user of your database will not know whether the Report is opening from the library database or from active database.
Share:

Printing Ms-Access Report from Excel

Printing Ms-Access Report from Excel or printing Report of another Database from Active Database.

We often talk about the Back-End, Front-End design of Microsoft Access Applications. All the Tables are maintained in the back-end database and kept linked to the front-End. We keep them linked because we always retrieve information from them or update with latest data.

When the external database tables are linked to the front-end they are as good as the native tables of the front-end and you will not find any difference in working with them.

NB:  When you are linking Tables from a Network location see that you provide full network address of the database  \\ServerName\FolderName\Subfoldername\DatabaseName.mdb format, rather than server mapped address T:\FolderName\Subfoldername\DatabaseName.mdb. If the Network Location mapping to you machine (T:\) changes (to K:\ or Z:\ etc.) at a later stage you don't need to do anything to refresh the linked Tables.

When you need to retrieve (or add/update) information in a table that is not linked to the front-end database we need a VBA program to do that.

A sample procedure is shown below:

Public Function CategoryTable()
Dim wsp As Workspace, db As Database
Dim rst As Recordset

'Set a reference to the active Workspace
Set wsp = DBEngine.Workspaces(0)

'DBEngine.Workspaces(0).Databases(0) is CurrentDB 
'Open a second database in Workspace(0).Databases(1) position 
Set db = wsp.OpenDatabase("C:\mdbs\NWSample.mdb")
 
'Open Categories recordset from Database(1) 
Set rst = db.OpenRecordset("Categories")
 
'Display the CategoryName field value 
MsgBox rst![CategoryName]
 
rst.Close 
db.Close 

'remove objects and release memory
Set rst = Nothing 
Set doc = Nothing 
Set db = Nothing 
Set wsp = Nothing 
End Function

When an Access Database is open in the Application Window, it is open within a Workspace in the Workspaces Collection, under Application.DBEngine Object. The default Workspace is Workspace(0) and the first open database is addressable at Workspaces(0).Databases(0). We can open more than one Database within the same Workspace and work with it's Table or Query recordsets. This approach is good if we are not working with those tables/queries on a day-to-day basis, rather than keeping them linked permanently to the front-end database.

But, we cannot open Forms or Report with this approach.

Application.DBEngine.Workspaces(0).Databases(0) Object is CurrentDB. Several databases can be opened in Workspaces(0) but the Current Database only will be visible in the Application Window and other databases, if open, will remain in memory till we close them. Now you know that we can work with tables, of databases loaded into memory, which are not linked to the Front-end, whenever we want to retrieve or update information in them.  Besides that we can create Tables or Queries in them too.

What we will do if we want to create a Query on an external Table that is not linked to the Font-end database? Believe it or not you can create Queries in Microsoft Access without linking external Tables to the current database. Want to find out how? You can learn the trick from here.

So far our discussion was on external Tables/Queries. But, the procedures explained above will not help to open a Form or Report from another database. This can be done only within a separate Access Application Window.   This statement is not 100% true, we will see why it is not next week.

Before going with the Excel based procedure let us see how it is done from the Active Database.

The simple procedure steps are given below:

  1. Create a separate Access Application Object.
  2. Set it’s visible property value to Yes, so that we can see the Application Window.
  3. Open the required Access Database within that Application Window.
  4. Open the required Report in Print-mode (acViewNormal) to send the Report directly to the default printer, or in Preview-mode (acViewPreview) to view the Report before sending it to the printer manually.
  5. Close the Database first and Quit the Application.

Sample VBA Code is given below:

' Include the following in Declarations section of module.
Dim appAccess As Access.Application

Public Function PrintReport()
'---------------------------------------------------------
'Original Code Source: Microsoft Access Help Document
'---------------------------------------------------------

    Dim strDB As String

' Initialize string to database path.
    Const strConPathToSamples = "C:\Program Files\Microsoft Office\Office11\Samples\"
    strDB = strConPathToSamples & "Northwind.mdb"

' Create new instance of Microsoft Access Application.
    Set appAccess = CreateObject("Access.Application")
' Make Application Window Visible
    appAccess.Visible = True

' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB

' Open Catalog Report in Print Preview
    appAccess.DoCmd.OpenReport "Catalog", acViewPreview
    
' Enable next line of code to Print the Report
    'appAccess.DoCmd.OpenReport "Catalog", acNormal

    'appAccess.DoCmd.Close acReport, "Catalog", acSaveNo
    'appAccess.CloseCurrentDatabase
    'appAccess.Quit
    
End Function
  1. Copy and Paste the above Code into a new Standard Module of your Database.
  2. Make changes to the Path of the Database and Report name, if needed.
  3. Click somewhere in the middle of the Code and press F5 to run the Program.

If you were able to run the code successfully and Print/Preview your Report in a separate Access Application Window, then you may proceed to do the same thing from Microsoft Excel.

  1. Open Microsoft Excel.
  2. Display VBA Window (Developer - - >Visual Basic).
  3. Insert a Standard Module (Insert - - > Module) in the VBA Window.
  4. Copy and Paste the above Code into the Module and Save it.

Before running the Code you must add the Microsoft Access 12.0 Object Library to the Excel Project.

  1. Select References.. from Tools Menu.
  2. Find Microsoft Access 12.0 Object Library (or whatever version available in your machine) and put a check mark to select it.
  3. Click OK Command Button to close the Control.
  4. Click in the middle of the Code and press F5 to run.

You will see the same result you saw when you run the Code in Microsoft Access.

Share:

Search for Record Macro Action Access2007

Searching for a record on the Form is normally done with the help of Find (Ctrl+F) method of Microsoft Access. For the search operations on the Last Name field of employees record, on the Employees Form, it is absolutely necessary that the Last Name data field is present on the Form. 

This is where the SearchForRecord Macro Action mainly makes the difference, besides other flexible features.  You can search for the Last Name of an employee even when this field is not present on the Form.  But the data field must be available on the Record Source (Table/Query) of the Form.  The SearchForRecord Macro Action can accept logical comparisons like <, >, AND, OR and BETWEEN to search and find the required record.  But, the Find method accepts only one of the three options, viz. Whole Field, Any Part of Field & Start of Field to search for a record on any of the available field on the Form.

Let us try out SearchForRecord Macro Action with the help of data from Employees Table of Northwind.accdb sample database.

  1. Import the Employees Table from Northwind.accdb sample database.
  2. Design the Form frmEmployees in Columnar Format (see the sample image given below). You can do this quickly with the help of Form Wizard option from the Forms Group under Create Menu.
  3. Select the Last Name Textbox and Delete it.

    We will try to search and find records using this field, without placing the field on the Form. We will also try the search method with the First Name field on the Form combined with Last Name field (not on the form) and learn the usage of AND, OR Logical Operations in the search criterion in the Macro we are going to create.

  4. Create two Text Boxes and a Command Button at the Footer of the Form, as shown in the image above.
  5. Change the Child Label Caption value of the first Text Box to Last Name and the Textbox Name Property Value to lstName.
  6. Similarly, change the second Child Label Caption of the second Text Box to First Name and the Textbox Name Property Value to fstName.

    Before making changes to the Command Button Properties we must create a Macro with the SearchForRecord Action.

  7. Save the Form with the name frmEmployees and close it.
  8. Select Macro from Create Menu to open a new Macro design window.

    Sample Macro image is given below:

  9. Select SearchForRecord from the drop-down list in Action Column of the Macro.
  10. Set Form in the Object Type control, in the property sheet below.
  11. Select frmEmployees from the drop-down list of Object Name.
  12. Select First in the Record control.
  13. Type [Last Name] = "Kotasa" in the Where Condition control. 

    NB: You may open the Employees Table to view and select any record value from the Last Name field, preferably after few records from the beginning of the records. Note down the first name of the employee also so that we can cross check the correctness of the record found by the search operation, when the record changes on the form.  Remember we have deleted the Last Name Field  from the frmEmployees Form.

    Note: Initially, we will try this method with a simple constant criteria (easier to understand it’s usage) in the Where Condition control and search for last name of an employee Kotasa in the Last Name field not on the Form.  After that we will modify the macro to use the values typed on the Text Boxes, which we have created on the Footer of the frmEmployees, as search criteria.  This way it will give us much needed flexibility on search operations on the Form, by simply changing the search values on the text boxes.

  14. Save the Macro with the name macSearch and close it.
  15. Open frmEmployees in Design View.
  16. Click on the Command Button at the Footer of the Form to select it.
  17. Display the Property Sheet (F4), if it is not visible.
  18. Change the Name Property value to cmdRun and change the Caption value to Search For Record.
  19. Select On Click Event on the Event tab of the Property Sheet and type the macro name macSearch, or select it from the drop-down list.
  20. Save the Form and open it in Normal View.  You will see the first record on the form is active now.
  21. Click on the Command Button to search the Last Name Kotasa (or whatever last name you have inserted in the criteria) on the Form.

    You will see the record changes on the Form. Check and confirm that the First Name on the form matches with the name you have noted down earlier.  We will modify the Macro to make it more flexible.

  22. Close the frmEmployees for now.

Now, we will modify the Where Condition control settings on the Macro to take the values we type on the Text Boxes (with the names: lstName and fstName) on the frmEmployees Form, rather than using constant values in the search criteria, as we did in the earlier example. We must create an expression to take the lstName and fstName Text Box values joined with the AND Logical operator to conduct search operation on Last Name and First Name fields on the Form.  For this reason we need to take some extra effort to build the expression correctly so that it works every time.  We must join the Data Field names (Last Name, First Name) and Text Box (lstName, fstName) contents combined with the Logical Operator AND  in the expression.

  1. Open the macSearch Macro in Design View.
  2. Copy and paste the following expression into the Where Condition control, replacing existing one.

    ="[Last Name] = '" & [lstName] & "' AND [First Name] = '" & [fstName] & "'"

    The expression starts with an = sign, the field name Last Name have a space in the middle and is placed in square brackets followed by an equal sign for an exact match of value and the whole segment of the expression is placed in double quotes.  Before closing the second double-quote a open single quote is placed because we have joined the text data from the lstName text box on the form.

    The next segment of the expression is opening with a double-quote, and a closing single quote for the first text data, followed by a space and the AND logical operator followed by the First Name field name in square brackets followed by a space, = sign, opening single quote for the fstName text value followed by the closing double-quote of the third segment of the expression.  The fstName text box reference from the form is joined with an ampersand followed by an ampersand to join the closing single quote within double quotes.

  3. Save and close the macro.

    Since, we have used the AND Logical operator both Last Name (doesn’t exists on the and Form) and First Name field values should match to find a record on the Form.

  4. Open the Employees Table and note down last name and first name of few records on paper and close the Table.
  5. Open the Form frmEmployees.
  6. Type last name and first name from the first record, you have noted down earlier, into their respective text boxes on the footer of the Form.
  7. Click the Command Button to run the macSearch to find the record on the form that matches both last name and first name.  Remember, the last name field is not there on the form.  You may repeat this method with the other record values you have noted down earlier, if any.
  8. You may modify the macro to change the AND Logical Operator to OR.  You may try the macro after entering search value in any one of the text boxes (lstName or fstName) or values in both text boxes.  If any one of the two or both values matches with the record then it will be returned.

The modified expression is given below for reference:

="[Last Name] = '" & [lstName] & "' OR [First Name] = '" & [fstName] & "'"

Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captur...

Labels

Blog Archive

Recent Posts