Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

i need 3 filters but i don't want to create 3 separate queries

i need 3 filters but i don't want to create 3 separate queries

i need 3 filters but i don't want to create 3 separate queries

i am using Access 97 in a Novell environment.

i have a table with records (and a primary key called ID).  some of those records will require the users to create a work ticket.  there are 3 types of ticket the user can create: power, iron, fiber.

then i have a table called tblTickets. It also contains the ID field, as well as a field called Type and that contains either a 1, 2 or 3.  (i have another table called tblTicketType that associates 1 with Power, 2 with Iron and 3 with Fiber.)

so on a form, i have 3 command buttons, cmdPower, cmdIron and cmdFiber.  what i want is for my form frmTickets (based on the table tblTickets) to open and display only the Power records when the cmdPower button is clicked, only the Iron tickets when the cmdIron is pressed and so on.  but if there is no existing Power ticket created, i need a blank form displayed so the user can create a new ticket.  then i need to somehow make sure the value of Type is 1.

i don't want to create 3 separate queries. i was hoping to do it all with one.

any ideas?

RE: i need 3 filters but i don't want to create 3 separate queries

How about this: use an unbound combo instead of a button, with allowable values of 1/Power, 2/Iron, 3/Fiber. Use the value (Forms!FormName!ComboName) as the criteria in your query. Use the OnChange event to launch the query.

If you do want to stick with buttons, you could create an OnClick event that sets the value of an invisible text control on your form to the appropriate value, then launch the query.

RE: i need 3 filters but i don't want to create 3 separate queries

that sounds good but how can i allow new records to be added?  i can open the frmTickets with the two filters:

stLinkCriteria = "[id]=" & "'" & [Forms]![frmEngineeringRequest]![id] & "'"

stLinkCriteria1 = "[var]=" & "'" & [Forms]![frmDay1]![var] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria & " & " & stlinkcriteria1

so that opens the frmTickets and show all records where the ID is the same as on the main form and my variable Var changes depending on which command button was pressed.  but, what if i want to create a new Power ticket for an ID?  i click on the cmdPower button, and the frmTicket is filtered to show only the record with a matching ID that is also Type=1.  but, if there aren't any records, i just get a blank screen with nothing on the form. i don't have the opportunity to enter any data at all since all my text boxes are invisible.  


RE: i need 3 filters but i don't want to create 3 separate queries

A form where the query returns no records will be blank under certain conditions. Go to the forms properties and look under the data tab. Is AllowAdditions set to yes, if not set it to yes. The query type should be a dynaset not a snapshot.

RE: i need 3 filters but i don't want to create 3 separate queries

Hmmm, I was thinking that you had a form based on the parent table, witha subform based on the child (Ticket) table, or to be more specific, a query based on the Ticket table using the specified selection criteria. If you set it up this way and used the Button Wizard, it will insert the code you should be using, just take a look at it when the wizard completes. As cmmrfrds notes, your form may have properties set incorrectly that you will need to reset to the deafult value (AllowAdditions = Yes).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close