Adding/Removing and Saving an existing query

[June7]

I got that but what I want to do is allow people to run a query with the requery button and then change the criteria and click a different button that would add the new data matches to the already existing query.

Sounds like what al and I said earlier. User changes criteria. Click button (same button) to requery the data.

[MondayLon676767]

But wouldn't that produce an entirely new set of data in the query and overwrite the existing query?

I am talking about this:

Table= 1,2,3,4,5,6,7,8,9

User searches for: 2,3,4

The Subform query displays: 2,3,4

User add searches for: 8,9

The subform displays: 2,3,4,8,9

User remove searches: 2,8

The subform displays: 3,4,9

Currently on my form it is like this:

User searches for: 2,3,4

The Subform sidplays: 2,3,4

User searches for: 5,6,7

The Subform displays: 5,6,7

I am real sorry if I am misunderstanding to me it sounds like we are talking about different things. Or are you two talking about a button that would always act like the first search I described and another button that would just clear the table and subform for a fresh search?

[June7]

Nothing is 'overwritten'. Just applying a different filter on the form's recordset.

A button could be made available that would clear all the search controls but is not required to requery with different criteria.

Clarify what you mean by Table= 1,2,3,4,5,6,7,8,9

Are these 9 fields or 9 records?

The scenario you want should be possible. The how depends on clarification.

[MondayLon676767]

Table= 1,2,3,4,5,6,7,8,9 are examples of records.

[June7]

So how does user select multiple values as criteria for one field - with a listbox? How are you applying that multiple criteria to filter the records? The only method I know requires VBA code.

If you have a database and working form, want to provide for analysis? Follow instructions at bottom of my post.

[alcapps]

testform.accdb
ok.. here is an example of what I have been telling you...

[alcapps]

here is how to get the list box values into a In clause..

Public Function ListInClause(lst As Control, Optional OneToForceAll As Integer) As String

Dim ListRow As Variant
Dim strList As String
Dim strTemp As String
Dim i As Integer

If IsNumeric(lst.Column(0, 0)) Then
If OneToForceAll = 1 Then
strTemp = "In("
For i = 0 To lst.ListCount - 1
strList = strList & "," & lst.Column(0, i)
Next i
strList = strTemp & Mid(strList, 2) & ")"
ListInClause = strList
Else
strTemp = "In("
For Each ListRow In lst.ItemsSelected
strList = strList & "," & lst.Column(0, ListRow)
Next
strList = strTemp & Mid(strList, 2) & ")"
ListInClause = strList
End If
Else
If OneToForceAll = 1 Then
strTemp = "In("
For i = 0 To lst.ListCount - 1
strList = strList & ",'" & lst.Column(0, i) & "'"
Next i
strList = strTemp & Mid(strList, 2) & ")"
ListInClause = strList
Else
strTemp = "In("
For Each ListRow In lst.ItemsSelected
strList = strList & ",'" & lst.Column(0, ListRow) & "'"
Next
strList = strTemp & Mid(strList, 2) & ")"
ListInClause = strList
End If
End If

End Function
Public Function ListInClauseInt(lst As Control, Optional OneToForceAll As Integer) As String

Dim ListRow As Variant
Dim strList As String
Dim strTemp As String
Dim i As Integer

If OneToForceAll = 1 Then
strTemp = "In("
For i = 0 To lst.ListCount - 1
strList = strList & "," & lst.Column(0, i)
Next i
strList = strTemp & Mid(strList, 2) & ")"
ListInClauseInt = strList
Else
strTemp = "In("
For Each ListRow In lst.ItemsSelected
strList = strList & "," & lst.Column(0, ListRow)
Next
strList = strTemp & Mid(strList, 2) & ")"
ListInClauseInt = strList
End If

[MondayLon676767]

So how does user select multiple values as criteria for one field - with a listbox? How are you applying that multiple criteria to filter the records? The only method I know requires VBA code.

If you have a database and working form, want to provide for analysis? Follow instructions at bottom of my post.

Lets see the user chooses between the following:

4 Text box controls, 4 combo box controls and 6 text box between controls (like between controls as in, people who who have a score of X to X., two text box with a label in between). The user can come up with any combination of the controls to pull a list.

I stayed up pretty late tinkering with it and didn't get far but I realized a pretty big challenge on top of everything else. The query is connected to two tables where one is people with a id and the other is kinda accounts with the ids. So often people have many accounts. I need to be able to remove people if they have one account like the criteria. So it would be like this:

TableIDs
1, John, Brown
2, Mary, Brown
3, John, Doe

TableAcounts
1, blue
1, red
1, yellow
2,red
3, blue
3, red

If the criteria said I need Names from TableIDs with that are all colors, query results:
1, John, Brown
2, Mary, Brown
3, John, Doe

The I could remove Names that have yellow accounts, query results would be:
2, Mary, Brown
3, John, Doe

[MondayLon676767]

testform.accdb
ok.. here is an example of what I have been telling you...

Thanks for the example but that is what I already have but just with a lot more controls. Like I was saying I need to be able to search and then kinda search append, but not append a table but query results kinda.

[alcapps]

Ok you would put in a control option for not equall or equal next to your combo box you would have another combo box with = or !=. When you build your where clause if statement != you output

and not gender='male' instead of -> and gender = 'male'

if it can be done in a query you can build the where clause from filter controls.

You can also make the fields in the output dynamic

[alcapps]

I gave the listinclause function already takes input of list box and gives you in(1,3,4) on the items that are selected to build your where clause.

[MondayLon676767]

Ok you would put in a control option for not equal or equal next to your combo box you would have another combo box with = or !=.

Is there anyway to bypass the need for "equal" or "not equal" combo boxes in front of all my criteria and having a separate button from the search button that would set a "not equals" in front of the last query? I would have to add about about 15 combo boxes if not and I still don't see how that would return the right query. I need to be able to say that in the same field that "blue" is equal but "yellow" is not.

[June7]

If the query criteria is ="blue" then no need for <>"yellow"? Yellow is excluded by ="blue".

If you want everything <>"yellow" there is no need for ="blue".

As you can see, building multi-criteria strings can get complicated. Especially if you want to mix OR and AND operators as well as multiple criteria on one field.

I think we have provided enough references for you to decide on a path. When you have specific issue with code, post new thread.