Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Updating Combobox when Not in List is Active

Introduction.

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, other than what you already have, into the combo box control. The user is forced to select/type an existing item from the drop-down list of the combo box.

Typing a new value in 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 Combobox, are of constant nature, and never change or 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 only be added to the Source Table directly through a separate Form.  The user has to open the source table editing form and add a new record.  Even if the user is prepared to do that the new item added to 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 on the list. It is not a user-friendly approach and a lot of time is wasted.

An Easy Solution.

But, we can make it very easy for 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 in 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 the On Not in List Event Procedure.  After getting confirmation from the user we can add the new item to the combo box source table and refresh the combo box.

The VBA Program.

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 immediately:

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 the On Not in List Event Procedure of the cboProd Combo box.

Share:

Overlaying Sub-Forms in Real-Time

Introduction.

This is all about loading two or more Forms into a single Sub-Form Control, interchangably.  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 have got the idea as to 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:

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


Sample Form Design.

  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 the 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 Container control and places the sub-form within this control.

  2. Display the Property Sheet (F4) of the sub-form, while the sub-form container is in the selected state, and change the Name Property value to subFrm

  3. Change the Name Property Value of the Child Label to 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 Options Group Control to Frame1.

  6. Change the Default Value property value of Options 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:

    Frame1_AfterUpdate() Event Procedure.

    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 Options 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.

When Two Sub-Forms Linked Together.

Assume that you have the 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 the main form of the 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:

View the Demo Video of two sub-forms in action.

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.

The VBA Code.

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

Download the Demo Database.

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


Download Demo SubFormTrick2007.zip

Download Demo SubFormTrick2003.zip

Share:

Form Footer Section Dropping Down Magic

Introduction

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 a caption, something like Advanced, or More indicating that more options are 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 made an attempt to mimic this trick on a Form. The Screenshot of the Form, before and after it is in expanded modes, is 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 options.

Check the following video to see how it works.

Now, you know what we are trying to do here. We will design two Forms, create two Macros, and a few 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 them.

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 as you can store in a table. You can do several tricks with this method.  The links to the Articles, published on this topic earlier, are given at the end of this page for reference.

The Design Task.

  1. Open a new Form in Design View.

  2. Right-Click on the Form's Detail Section 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 showing on the Footer section 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 a 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 it 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 the 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 purposes only. 

  12. While the Option Group is in the 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 Options 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 the Caption Property values to Quit, Open, Print, and More… respectively.

  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 Code, overwriting the existing lines in the VBA Module.

    The Form Module VBA Code.

    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. If you think that you need some changes, to the design of the Form, then you may do it and save and close the Form with the name FormA1.

    Make a Copy of FormA1.

  22. Right-click on FormA1 and select Copy from the Context Menu.

  23. Right-Click on the Navigation pane Header and select Paste from the displayed menu.

  24. Change the name of the copy to FormB1.

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

  1. Open Form FormB1 in the 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 the top position values, with the command button on FormA1, if it is not the same then take the value from FormA1 and change it to match. See that you don't make any other changes to the Detail or Header Section designs on FormB1. The Form should be an exact clone of FormA1 when we incorporate some extra features into this Form.

  3. Expand the Footer Section down (check the second image from the top) to give enough space to create a Command Button.

  4. Create a Command Button, at the right side of the footer section, display the property sheet (F4), change the Caption property value to Preview, and 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:

    Private Sub cmdPreview_Click()
        DoCmd.OpenReport "myReport", acViewPreview
    End Sub
  7. Change the report name (myReport) with the name of one of your own Reports.

  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. 

How it Works.

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 will present 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 having its 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 show the second form on the same position on the screen it will look like the first form's footer section dropped down to reveal the Preview Command Button there. The command button's caption text More... changes to Less. 

The Preview command button click opens a report.  Next click the Less command button opens the first Form FormA1, and closes FormB1.  Both form's will open on the same coordinates on the Database window interchangably.   

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.

The Option-Group Issues.

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:

The Custom Property Creation.

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 the Frame1 and Frame2 Custom properties on form FormA1. The CreateProperty() method accepts three parameters.

  • The first parameter "Frame1" is the Name of the custom property.  You can use any text value as a name.

  • The dbInteger second parameter is a constant that indicates the custom property Frame1 will store Integer Type data.

  • The 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 above 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

Container and Document Objects.

Note: As you can see in the above program that the collection of Forms is referred to as a Container Object.  Tables (Queries also come under Tables), Forms, Reports, and 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 objects, 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 published earlier, using the custom property on the form.

The Macros.

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 the 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 the Right, Down, and Width arguments respectively.

  3. Save and close the Macro with the name macFormA1.

  4. Make a copy of the macro macFormA1 and rename the new macro 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.

VBA Code on Both Form Modules.

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) are 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 the More… captionto 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 the 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.

Test Running the Project.

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, and 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 the 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 run 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 options from the Reports Option Group. 

    Remember, we have added one of your Reports on the Preview Command Button click the 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 options 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.

This is all about two simple Forms Magic.

I know it took a lot of explaining in a very lengthy post, but it is all about two forms, with the almost same design, showing one after the other in 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 published earlier: Create your own color palette.

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

Download Demo Database

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


Download Demo FormTrick2007.zip
Download Demo FormTrick2003.zip
Share:

PIE Chart and live data on Form

Introduction.

Students' Exam Scores in five different Subjects are recorded in 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. The Sample Form- image with the PIE Chart is given below:

When any one of the exam records, out of five subjects of a  particular student becomes current on the Form the PIE Chart should display the marks of all five subjects and the percentage of 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 the source for the same Chart. 

Data Source Queries.

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:


The Three-Part Approach.

We will divide the task into 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 creates three simple Queries to prepare the data for Charts.

  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 for 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 in the above image, and save it with the name tbl_MaxMarks.

  4. Create a single record with the sample data, shown in the image above.

Part-B:

  1. Design a Form in Column format, using tbl_Students, leaving enough space on 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 the 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 TextBoxes 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 Text Box to % of 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 up the total obtained marks from StudentsQ. Subtracts it from the Maximum Marks of all five subjects (500) from tmp_MaxMarks. The difference between 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 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 is 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 a 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 the open state and the exam result 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 a 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 the Next button twice then type the following as a heading 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 smaller font sizes.

  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. 

    The completed design of the PIE Chart is given below.

  15. Click outside the Chart Object in the Form, to exit from Chart-Edit Mode, but the chart will be still in the selected state.

  16. Right-Click on the chart, to display the shortcut menu, and click Copy to copy the Chart-Object on the Clipboard.

  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 anymore.

  18. Now, Change the frm_Students into Design View.

  19. Click somewhere on the Detail Section of the Form to make the 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 on the right side of the data fields.

  22. Click on the 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 the 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 record set 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 belong to 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 the values of all the five records are shown on the PIE Chart. The sixth to tenth records belongs to the second student and the 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

Introduction

Normally, we use a Form/Report Text box 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 becomes visible when the field becomes 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 drop-down control on the Conditional Formatting Dialog Control. We will take the last example of our new method of reference and let us see how it can be used differently. 

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

A Different Approach

But, both values referenced in the expression can come entirely from other objects like: from the 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 with external field reference 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 (the 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.

Earlier Post Link References:

Share:

Storing Bitmap Images in OLEObject Attachments Fields

Introduction.

MS-Access Tables have the OLE Object/Attachment Field to store and display images on Forms/Reports.

Example: Storing the employee photo or Product image on the data table.

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

Identifying the Image with its Related Record.

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 the disk easily, and display it on a Form or Report, whenever the need arises.

Organizing the Images.

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 properly planned and executed we can pick the correct image and display it on Form/Report, in the Image Control very easily.

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

When a particular employee’s record is currently 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 the disk into an image control on the Form. 

If you prefer to create an image type smaller in file size, to save disk space, then you may choose any one of these types: png, jpg, or gif. GIF images are lesser in size, but image quality may not be good.  Select only one of these image types 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.

Prepare for a Trial Run.

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 a 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 the employee’s photo bitmap images in the OLE Object field.  You can save these images on the 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 caption, and select Open from the Bitmap Image Object option.  The image will open in the Paint program.

    • Before saving the image to 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 a 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.

    The Trial Run.

  11. Open the Form in Normal View.

    If everything went on well then you will see the first employee photo in the image control.  The Sample Screenshot of  the Employees Form with the photo is given below:

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

Image Display on Report.

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

  1. Design a Report using a 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 the Image Control on the Report, as shown in the above Image.

  3. Display its Property Sheet and change the Name Property value to ImgCtl.

  4. Click somewhere on the empty area of the 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 and replace 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.

    The Trial Run of the Report.

  8. Open the Report in Print Preview.

    The 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

Introduction.

Last week we 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.

Simple Preparations.

The procedure goes something like the following:

First, let us define some names of databases, reports, and Forms 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).

    The Reference Library Usage.

  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 checkmarks, on top of the list.  You must browse and find DatabaseB.mdb from its 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 its 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 (which 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 display them in DatabaseA Window.

    How It Works.

    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 its ability to search for objects within the library file first, if references of objects appear 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 them 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 the 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.

    Opening Form/Report of DatabaseB from DatabaseA

  14. Open a form and create two Command Buttons on it.

  15. Select the first command button and display its 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 for the middle line.

    Private Sub cmdReport_Click()
       myReportOpen
    End Sub
  19. Copy the centerline 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 the active database.

Earlier Post Link References:

Share:

Printing Ms-Access Report from Excel

Introduction.

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 them with the 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 the 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 your machine (T:\) changes (to K:\ or Z:\ etc.) at a later stage you don't need to do anything to refresh the linked Tables.

Updating a Table not Linked with the Current Database.

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

The Databases Workspace.

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 the Table or Query record set. This approach is better 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, after loading the database into memory, which is not linked to the Front-end. Besides that, we can create Tables or Queries in them too.

Creating Queries on a non-linked External Table.

What we will do if we want to create a Query, with external Table data, 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 its 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.

The 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.

In Microsoft Excel.

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 the References option from the Tools Menu.

  2. Find Microsoft Access 12.0 Object Library (or whatever version is available on your machine) and put the check mark to select it.

  3. Click the 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.

  1. Roundup Function of Excel in MS-Access
  2. Proper Function of Excel in Microsoft Access
  3. Appending Data from Excel to Access
  4. Writing Excel Data Directly into Access
  5. Printing MS-Access Report from Excel
  6. Copy Paste Data From Excel to Access2007
  7. Microsoft Excel Power in MS-Access
  8. Rounding Function MROUND of Excel
  9. MS-Access Live Data in Excel
  10. Access Live Data in Excel 2
  11. Opening Excel Database Directly
  12. Create Excel Word File from Access
Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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