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

[ruthcali]

i am using Access 97 in a Novell environment.<br><br>i have a table with records (and a primary key called ID).&nbsp;&nbsp;some of those records will require the users to create a work ticket.&nbsp;&nbsp;there are 3 types of ticket the user can create: power, iron, fiber.<br><br>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.&nbsp;&nbsp;(i have another table called tblTicketType that associates 1 with Power, 2 with Iron and 3 with Fiber.)<br><br>so on a form, i have 3 command buttons, cmdPower, cmdIron and cmdFiber.&nbsp;&nbsp;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.&nbsp;&nbsp;but if there is no existing Power ticket created, i need a blank form displayed so the user can create a new ticket.&nbsp;&nbsp;then i need to somehow make sure the value of Type is 1.<br><br>i don't want to create 3 separate queries. i was hoping to do it all with one. <br><br>any ideas?<br><br>

 

[elizabeth]

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

 

[ruthcali]

that sounds good but how can i allow new records to be added?&nbsp;&nbsp;i can open the frmTickets with the two filters:<br><br>stLinkCriteria = &quot;[id]=&quot; & &quot;'&quot; & [Forms]![frmEngineeringRequest]![id] & &quot;'&quot;<br><br>stLinkCriteria1 = &quot;[var]=&quot; & &quot;'&quot; & [Forms]![frmDay1]![var] & &quot;'&quot;<br><br>DoCmd.OpenForm stDocName, , , stLinkCriteria & &quot; & &quot; & stlinkcriteria1<br><br>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.&nbsp;&nbsp;but, what if i want to create a new Power ticket for an ID?&nbsp;&nbsp;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.&nbsp;&nbsp;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.&nbsp;&nbsp;<br><br>help!

 

[cmmrfrds]

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

 

[elizabeth]

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