Query:  Criteria Row to Equal Value in Text Box

Posted on 2006-05-31
Last Modified: 2008-02-01

On my main form, I currently have 3 subforms.

Subform 1:  Active
Subform 2:  DeActive
Subform 3:  Other

Each subform points to the same data source, but the only thing different in each one is in the criteria row, I have either "Active" or "DeActive" or etc...

This way, each subform will only show a records within a specific status.  Well, I'm a bit annoyed having 3 subforms on this form.  I would imagine, I can avoid all this with my idea.

Is there any way, to put some code into the criteria row - that basically points it to a text box on my form that contains the value.

For example...  My plan is to have 1 subform.  Above that, I want a combo box that contains the 3 values (Active being the default value...)

Then when the subform is generated, in the status column it looks for the value of say, "cboStatus" and puts that value into the criteria row.

Does this make sense?
P.S.  I know how to make the subform refresh, I just need it to know where to find the value for the criteria row...

Thank you in advance.
Question by:xp310
    LVL 28

    Accepted Solution


    OM Gang
    LVL 65

    Expert Comment

    am I right, one subform, but filtered different on flag?

    u could try this

    private sub cboStatus_AfterUpdate

        Me.mysubform.Form.Filter = "Status = " & cboStatus
        Me.mysubform.Form.FilterOn = True
    end sub

    Assuming status is numeric
    LVL 65

    Expert Comment

    urm, code supplied is for mainform
    if u want button on subform, u simply do this

    private sub cboStatus_AfterUpdate

        Me.Filter = "Status = " & me.Parent.cboStatus
        Me.FilterOn = True
    end sub

    this also assumes the field in the subform is called Status

    Author Comment

    OM Gang,

    Your solution worked perfectly.  Thank you very much for you time.

    LVL 28

    Expert Comment

    You're welcome.  Thank you.

    OM Gang

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    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…
    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…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    745 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

    19 Experts available now in Live!

    Get 1:1 Help Now