Help - Update split form using text box, combo box, and button

Posted on 2007-08-02
Last Modified: 2013-11-28
Note: I'm using Access 2007.

I have a database with one table containing a bunch of data that was moved over from a spreadsheet. I have one form (split form) that shows the data from the table. Currently the record source for the form is simply the table.

When the form is opened I want to display all of the data from the table, however, each person using the form will need to filter the data based on their name and the date. I have added a text box for the date and a combo box for their name.

Form = migrationtracker
Text box = txtDate
Combo box = cbxTech

I have also added a button that I want to execute the filter command.

Button = btnUpdate

So basically what I want to do is update the form record source to be a query that is filtered by the Tech name and the date. I figured it would be something like this:

SELECT MigrationData.ID, MigrationData.UserID, MigrationData.AcctType, MigrationData.AcctNumber, MigrationData.MigrationTech, MigrationData.TargetDate, MigrationData.UserDom, MigrationData.MachineDom, MigrationData.AdminRights, MigrationData.TargetOU, MigrationData.AssetID, MigrationData.UserStatus, MigrationData.ComputerStatus, MigrationData.CompleteDate, MigrationData.Comments, MigrationData.LastName, MigrationData.FirstName, MigrationData.Shift, MigrationData.Telephone, MigrationData.Email, MigrationData.Combined, MigrationData.State, MigrationData.City, MigrationData.Department
FROM MigrationData
WHERE (((MigrationData.MigrationTech)=[Forms]![MigrationTracker]![cbxTech]) AND ((MigrationData.TargetDate)=[Forms]![MigrationTracker]![txtDate]));

It's been a while since I have done something like this and for some reason I'm just not getting it right. Any help would be much appreciated.

Thanks!! :)
Question by:zpetersen
    LVL 3

    Expert Comment

    try using

    me.filter = "XYZ"
    me.filteron = true

    Of course, you should put your criteria in for XYZ...8)
    LVL 58

    Accepted Solution

    You don't need to change the record source. Simply set a filter:

    Private Sub cmdFilter_Click()

        Me.Filter = "MigrationTech = [cbxTech] And TargetDate = [txtDate]"
        Me.FilterOn = True

    End Sub

    Leave the table as record source.


    Author Comment

    Both worked. Thanks to Harfang for the full solution.
    LVL 3

    Expert Comment

    hey... i posted my answer from my car on my iphone.... i think a little understanding is in order for my not copying your full filter...:P


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    760 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

    7 Experts available now in Live!

    Get 1:1 Help Now