Looking for Microsoft Access 2010 Macro Example

Posted on 2012-09-16
Last Modified: 2012-09-25
I am familiar with Access 2010 except have no experience with Macros. If I could get a sample explanation for what I wish to do, I believe I could manage getting it done. Here is what I wish to do:

I have a table in a database that is quite large, 600K+ records. I wish to create a "query form" that will allow me to enter the search key, for example, the "last name". Then populate the form with the query results of selected fields from this rather large table, such as last name, first name, address, state, zip, etc. Once the data is examined on the form, I wish to have a "button" with a macro attached to it that would allow only these fields on the query form to be inserted into a separate table.

The overall objective is to take paper sheets with names that I wish to find in the database table, if found, I wish to write out these fields to another table. This is a huge table with about a hundred fields, all I want are the fields I mentioned, and once found, create an entry of this limited data to another table.

This should be easy. I have never done this in access.
Question by:RayRider
    LVL 84

    Accepted Solution

    While I'm sure you could do this with macros,  you'd be much better off with VBA.

    Searching on a form can get to be very complex, but if all you're looking to do is filter on a few fields, you can do this:

    1) Build a query that returns ONLY those fields you mention in " selected fields from this rather large table". Name it qrySearch.

    2) Build a form based on qrySearch. Set the form to show in the Datasheet view.

    3) Now build another form, and embed the form from step 2 in it (you'll create a "subform").

    4) On the main form, add a combo or textbox for each criteria you want to search. For example, if you want to search on FirstName, then add a textbox for that, and name it something like "txFirstName"

    5) Add a button named cmSearch to the form. In the Click event of that button, do this:

    Sub cmSearch_Click()
      Dim sWhere As String
      If Nz(Me.txFirstName,"") <> "" Then
        sWhere = " FirstName='" & Me.txFirstName & "'"
      End If

      Me.NameOfYourSubformCONTROL.Form.Recordsource = "SELECT * FROM qrySearch WHERE" & sWhere
    End Sub

    Note the "NameOfYourSubformCONTROL". This is the CONTROL on the main form that hosts your Subform, and may or may not be named the same as the form you're using for a Subform. Be careful with this; it's tricky, and many novice developers have a difficult time with it.

    Once you've got that working, you can add other fields to the Search function. For example, if you want to add the ability to search by a field named OrderDate, you'd add a Textbox (name it txOrderDate), and then modify the Search button code:

    Sub cmSearch_Click()
      Dim sWhere As String
      If Nz(Me.txFirstName,"") <> "" Then
        sWhere = " FirstName='" & Me.txFirstName & "'"
      End If

      If Nz(Me.txOrderDate, "") <> "" Then
        If Len(sWhere) > 0 Then sWhere = sWhere & " AND "
        sWhere = sWhere & " OrderDate=#" & Me.txOrderDate & "#"
      End If

      Me.NameOfYourSubformCONTROL.Form.Recordsource = "SELECT * FROM qrySearch WHERE" & sWhere
    End Sub

    And so on, to build up your WHERE clause.

    Finally, to export to a table, you can use straight SQL, like this:

    Currentdb.Execute "INSERT INTO YourNewTable(Col1, Col2, Col3, Col4) (SELECT Col1, Col2, Col3, Col4 FROM qrySearch " & sWhere & ")"

    This will use the same Where clause you used to fill that subform to insert records into your new table.

    Obviously you'll have to change Table and Field names to match your own.

    Author Closing Comment


    I really appreciate the time you spent on this question. I will use your help a bit later. At the moment, I realized how important it was to know VBA. I am in progress of taking a course on VBA. When finished, I will use your advice and the knowledge from the course to complete this task at hand.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    MS Access Search and Replace Using VBA 6 29
    VBA exception error 5 23
    Locks on MS Access DB not being released 9 25
    Open Args 16 24
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now