[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1242
  • Last Modified:

Looking for Microsoft Access 2010 Macro Example

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.
0
RayRider
Asked:
RayRider
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
RayRiderAuthor Commented:
@LSMconsulting:

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now