UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Criteria Changes By Itself Adding Rows And Columns, Access 2007    
 
   
KathCobb
post Oct 3 2018, 04:59 PM
Post#1



Posts: 492
Joined: 18-October 06



I'm not sure where I am going wrong that may be causing this. I have a form I created, with four combo boxes that are used to select the criteria for a query. The final two combos are cascading. They include the use of select "all" union queries. I am not sure what information to provide yet, other than the picture of what is happening.

Every time I use the form to make query selections and run the query...the query works no issue. ( I have it open in design view, make selections on form, go to query and hit Run since I am testing it).

After I close the query completely and re-open it, what you see in the picture has happened. The query now has a dozen criteria rows and has added columns for itself. I don't have a clue what is causing this. Can anyone help?

Thank you
Kathy

Attached File(s)
Attached File  ScreenShot.pdf ( 1.81MB )Number of downloads: 10
 

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
Doug Steele
post Oct 3 2018, 05:18 PM
Post#2


UtterAccess VIP
Posts: 21,956
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You say "Every time I use the form to make query selections and run the query". Exactly what are you doing to "make query selection"?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
http://www.accessmvp.com/DJSteele/AccessIndex.html
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
KathCobb
post Oct 3 2018, 05:41 PM
Post#3



Posts: 492
Joined: 18-October 06



I am using the 4 combo boxes on my Select form. The combo boxes are unbound, so is the form.

I'm just making the form, so I am going back and forth between the query and form. I have the Form in design view where I make changes, then switch back to form view. Select an item from each combo box, click the query that is also open in design view and select the "Run" ! that is in design tab of the query. The query then (hopefully) turns into the data sheet view using the four items I selected in the comboboxes.

Depending on the result, I usually switch the query back to design view (where there have not been any changes created) and also the form back to design view and tweak or add whatever is next.

Ive kept doing this for several days now (haven't worked with access in quite awhile and was only ever a beginner). Today when I decide that I had to move on and do something else for awhile, I saved the form, the query and the db and closed it. An hour later, I opened both query and form in design view and the query now looked like the picture! I deleted all the extra rows and columns from the query and put it back to the way I had it and tried a few more times using the form. No changes while I had the query open. Saved and closed everything again and then reopened. All those row and columns were back.

Ive been working on this all week and today is the first time that craziness started happening. I am particularly trying to do several things.....

Form that a user can make selections from the 4 combo boxes to export a query to excel. I have not even attempted an export yet. The query is to display fields from multiple tables (Name, address, city, state, zip, phone, agent, orders, etc). The combo boxes use union queries so that the user can select "all". the third combo boxes selection then narrows down the selections available in the fourth combo box.

My query then has the criteria set to read the selections in the combo boxes or Is Null. It works. It just makes all those crazy changes after I close. Is there some event procedure I might have somewhere that is changing that? I do have an after update procedure that narrows down the choices of the 4th combo box but it doesn't refer to the query? I don't know I am just grasping at straws....


UPDATE--> I just opened the database again and all the criteria lines were added again in the query. I deleted all the rows and columns that did not belong and saved the query. I opened nothing else and did nothing else. I closed and reopened. They were all back. What could possibly be happening?
This post has been edited by KathCobb: Oct 3 2018, 06:01 PM

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
RJD
post Oct 3 2018, 06:44 PM
Post#4


UtterAccess VIP
Posts: 8,941
Joined: 25-October 10
From: Gulf South USA


Hi Kath:

QUOTE
My query then has the criteria set to read the selections in the combo boxes or Is Null.

Yes, this is normal behavior when you use this type of criteria. You are entering two criteria for a single field (using OR). So Access SQL "helps" you by splitting the criteria into two fields and aligning the criteria slots vertically (the OR positions). The more dual criteria you add to the query, the more strange the Design View looks. That's just the annoying way this works.

There are ways to avoid this by writing your criteria differently. Instead of using OR you might use an IIf approach, something like...

IIf(IsNull(Forms!MyForm!MyCombobox),[MyField],Forms!MyForm!MyCombobox)

If used for all criteria, this should stop the Design View revisions and make the Design much easier to decipher. However, the revision you are experiencing should work without issue - even if it is much more difficult to understand and modify.

If you show us the SQL before the revision occurs, someone could probably help you sort this out, if you need that ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
KathCobb
post Oct 3 2018, 07:15 PM
Post#5



Posts: 492
Joined: 18-October 06



Well Thank goodness it’s not broken! I will post what I have for SQL tomorrow and I also had questions about the cascading combo box. Thank you for replying...at least now I can’t sleep tonight! notworthy.gif

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
RJD
post Oct 3 2018, 07:29 PM
Post#6


UtterAccess VIP
Posts: 8,941
Joined: 25-October 10
From: Gulf South USA


Sleep well ... and we will take a look at your SQL and cascading comboboxes and see how we can help tomorrow ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
KathCobb
post Oct 11 2018, 12:03 PM
Post#7



Posts: 492
Joined: 18-October 06




Sorry it took so long, I am just getting back to this. I took your suggestion and changed all my criteria to use an IIF statement. For example:


CODE
IIf(IsNull([Forms]![frmSelectToExport]![cboCounty]),[fkCountyID],[Forms]![frmSelectToExport]![cboCounty])



All of that works and doesn't create any more additional lines of criteria in my query. woohoo.gif

Now here is what I am having trouble figuring out next. My last combo box is a cascading combo box from the one above. I removed its Row Source because it was being populated by an after update event from the one above. But I need this combo box to also have an "all" option if the one above is "all". But even more so, I would like it to keep the "All" option after the results are limited by the above selection.

For example, if I choose a company, the next combo box has four choices I can then select one...but I'd like to also have all four of these choices? Is that possible?

Here is the After Update event associated with the "company" combo box. I did not write this, I had help from several sources because Joins confuse me to no end.

CODE
Private Sub cboSelectCompany_AfterUpdate()


    Dim strSQL As String
    Dim strWhere As String
    strWhere = " WHERE (1=1)"

   If (IsNull(Me.cboSelectCompany) = False) Then strWhere = strWhere & " AND (fkInsuranceCompanyID=" & Me.cboSelectCompany & ")"

    
    strSQL = "SELECT DISTINCT tblInsurancePlanType.pkInsurancePlanTypeID, tblInsurancePlanType.PlanType " & _
             "FROM tblInsurancePlanType LEFT JOIN tblInsuranceCompanyPlanLink ON tblInsurancePlanType.pkInsurancePlanTypeID = tblInsuranceCompanyPlanLink.fkInsurancePlanTypeID " & _
             strWhere & _
             "ORDER BY tblInsurancePlanType.PlanType;"
    Me.cboSelectPlanType.RowSource = strSQL
    Me.cboSelectPlanType.Requery
    Me.cboSelectPlanType = 0
End Sub



I do have a query for "PlanTypeUnionAll" that I had in my row source, but the visible column kept being different when I used that, and I kept changing it not sure what I was missing. The actual plan name is in column one of that query and the pk is column 2. Whenever I kept that row source and used the after update event, it would only show the pkID, no matter how many times I changed the columns widths around. Its just all confusing.

Any help would be greatly appreciated.

Kathy

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
RJD
post Oct 12 2018, 08:35 AM
Post#8


UtterAccess VIP
Posts: 8,941
Joined: 25-October 10
From: Gulf South USA


Hi Kathy: You are welcome. Glad you got the criteria format sorted. You could also use a NZ approach ...

NZ([Forms]![frmSelectToExport]![cboCounty]),[fkCountyID])

... but I suggested the IIF approach first because the logic (IMO) seems more apparent.

However, I am having a difficult time with your other issue. I think we will need to see the db to understand and suggest (at least, I work better that way). Could you post a db (relevant objects, enough non-sensitive data to see the issue)? Also include some instructions that would help us see where we need to be focused.

This is no doubt solve-able if we can dig into the db.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
KathCobb
post Oct 12 2018, 09:54 AM
Post#9



Posts: 492
Joined: 18-October 06



I wouldn't know how to take the db apart. There are 12 tables involved in the query and I wouldn't know how to delete 5,000 records? I have attached a screenshot of the form with an explanation. Maybe that will help. I just need to figure out how to include choices for "all" in a cascading combo box that is populated by VBA from the after update event of the one above instead of a union query in the record source as the others are. Adding "all" has been a very frustrating experience for me...there is so much information out there on the internet and everyone does it differently. Anyway, It's just that last box that needs an all choice and I can't get it to work.

Thank you for trying to help, I appreciate any and all guidance.

Kathy





Attached File(s)
Attached File  Screen_Shot_2018_10_12_at_10.36.53_AM.png ( 193.48K )Number of downloads: 8
 

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
KathCobb
post Oct 12 2018, 02:12 PM
Post#10



Posts: 492
Joined: 18-October 06



Maybe I should use a multi-select list box?

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
RJD
post Oct 12 2018, 02:45 PM
Post#11


UtterAccess VIP
Posts: 8,941
Joined: 25-October 10
From: Gulf South USA


Kathy: If the default is Null, then that can be used as the "All" option, just as it is in the previous examples. Or you can use a UNION query for the row source and provide an <All> option in the extra query part, and use that in the criteria logic.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
KathCobb
post Oct 12 2018, 03:51 PM
Post#12



Posts: 492
Joined: 18-October 06



I switched it to list box with multi value select. Still No joy. Can I not use the an IIF statements as criteria in my query with multiple selections? Everything else works on this silly form/query except this final criteria. If I select one thing, it works. More than one, it just returns all the values (which by the way as a combo box, it never did that). Again it is possible that the problem is in the after update event on the box above that determines what items will populate the list below. I have nothing in the row source of the actual list box. I am stumped.

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
Doug Steele
post Oct 12 2018, 08:00 PM
Post#13


UtterAccess VIP
Posts: 21,956
Joined: 8-January 07
From: St. Catharines, ON (Canada)


How are you trying to refer to the multiselect list box? You cannot simply include it in the SQL: a multiselect list box always returns Null, regardless of how many entries are selected. You have to loop through its ItemsSelected collection and build the string of selected values in VBA.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
http://www.accessmvp.com/DJSteele/AccessIndex.html
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
RJD
post Oct 12 2018, 11:05 PM
Post#14


UtterAccess VIP
Posts: 8,941
Joined: 25-October 10
From: Gulf South USA


Kathy: Up to this point I had assumed you were talking about selecting either one or all of the items on the list (combobox or listbox). But it looks like you also want to be able to select some as well. If you want to use a multi-select list box approach, as Doug says, you can loop through the ItemsSelected and create a criteria with VBA, while building the query or filter. There are other methods: I have used an approach that gathers each selection into a hidden textbox (or deleted if clicked again), then used the contents of the textbox with a Like or In operator, and I have used an approach that sends each selection to a utility table visible in a subform as it is selected, which is then linked into a query with an INNER JOIN. And no doubt there are other techniques as well.

While all techniques are pretty straightforward, they do require design and VBA skills to construct.

Please confirm for us whether the "some" option is a requirement for the final conbobox/listbox rather that just "one" or "all".

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
KathCobb
post Oct 14 2018, 09:42 AM
Post#15



Posts: 492
Joined: 18-October 06



Yes, I would also like some options to be able to be selected. Such as two out of four. I’m really confused on the joins, and have no idea how to make this an option.

Thank you for replying.

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
RJD
post Oct 14 2018, 01:26 PM
Post#16


UtterAccess VIP
Posts: 8,941
Joined: 25-October 10
From: Gulf South USA


Hi Kathy: Well, it looks like you have some work cut out for yourself. While well known, and pretty straight-forward, the techniques to select multiple, but not all items on a list to set as criteria for a query, are not trivial or directly built into Access queries.

Doug mentioned using the ItemsSelected approach. HERE is an explanation that describes the code necessary to capture the items for further use in your query. And there are other write-ups and examples here at UA as well.

In addition, I mentioned other methods. Attached is a demo of one such approach that may help you along (it uses your plan type list). As you select an item from the combobox list (not listbox) the item is added to a textbox on the form. If you select it again, the item is removed from the textbox. Then that textbox list is used in a query to limit the results of the query.

See if either of these approaches helps you solve your issue.

HTH
Joe
Attached File(s)
Attached File  MultipleSelections.zip ( 48.05K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
KathCobb
post Oct 15 2018, 11:31 AM
Post#17



Posts: 492
Joined: 18-October 06



Joe,

I have your demo open and I can follow it, along with the article link you gave me. My question is, in your sample plan type box, you have a RowSource. My plan type box is populated from the combo box above's After Update event limiting it to only plan types by the company selected. Will this cause an issue if I use your example?

Thank you for helping me with all this, I have been reading up, trying to figure it all out, but it really is a lot to understand.

Kathy

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
RJD
post Oct 15 2018, 11:47 AM
Post#18


UtterAccess VIP
Posts: 8,941
Joined: 25-October 10
From: Gulf South USA


Kathy:

QUOTE
My question is, in your sample plan type box, you have a RowSource. My plan type box is populated from the combo box above's After Update event limiting it to only plan types by the company selected. Will this cause an issue if I use your example?

You still have a RowSource, no matter how it is created. As long as the list is there and visible in the combobox the code should grab the value and work as designed. This assumes you are using the type name and not a type code.

QUOTE
...it really is a lot to understand.

Yes, once you stepped up to multiple (but not all) selections from a list (combobox or listbox) you added a new level of difficulty. And yes, it would be nice if the Access development team would add a built-in way to set query criteria for multiple selections in a listbox (such as In(MyListbox.ItemsSelected)). Until then, you have some other ways to address the issue.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
KathCobb
post Oct 15 2018, 02:35 PM
Post#19



Posts: 492
Joined: 18-October 06



Joe (and all who contributed),

Your info gave me enough to start work and do a little bit more googling and I have what I need. I wanted to keep my list box, so the selections would be visible and I could hide the other box named "Selected" on my form. Here is the code I ended up finding and converting:

CODE
Private Sub cboSelectPlanType_AfterUpdate()

Dim lst As Access.ListBox
Dim varItem As Variant
Dim strBuild As String

Set lst = Forms![frmSelectToExport]![cboSelectPlanType]

If lst.ItemsSelected.Count > 0 Then
  For Each varItem In lst.ItemsSelected
    strBuild = strBuild & lst.ItemData(varItem) & ","
  Next varItem
    Me![txtSelected] = Left$(strBuild, Len(strBuild) - 1)
End If
End Sub


I didn't change the name of cboSelectPlanType to reference its a list box because it appears to many places and I didn't want to mess anything else up. My text box "Selected" is hidden because its based on two columns--one being the ID and the other the name. I don't care if the text box only populates with the ID, it still works and can be hidden. If it would be easy to do, I would like to know, how to get the name to show? Anyway, I also kept your button and have it set to clear (=null) all boxes so the user can start over. It all appears to be working well.


I don't know if this is a new topic, but one final thing I'd like to know how to do, if possible, is I have an address type as one of my query fields. Is there a query criteria that says: If address ID is 1 do not include address ID 2. If address ID 1 is blank (no dress entered for ID 1) then include address ID 2? Then I will be done with this project! Thanks to all here who helped smile.gif

--------------------
I'm just a beginner...take pity on me.
Go to the top of the page
 
RJD
post Oct 15 2018, 05:49 PM
Post#20


UtterAccess VIP
Posts: 8,941
Joined: 25-October 10
From: Gulf South USA


QUOTE
I don't know if this is a new topic, but one final thing I'd like to know how to do, if possible, is I have an address type as one of my query fields. Is there a query criteria that says: If address ID is 1 do not include address ID 2. If address ID 1 is blank (no dress entered for ID 1) then include address ID 2?

Well, I don't think we know what your tables and query look like, so it will be hard (impossible?) to give you advice on how to get what you want. Are both addresses in the same record as the other fields from the table? Are the addresses in another table, linked to the main table by ...? Perhaps you could show us the table(s) and query, with some data (in a cutdown db, compacted, zipped and attached here). You refer to both Address type and Address ID. Are there the same or if different, how?

And if you write out the IIf logic, with field names, that should help us as well - or perhaps even give you the solution.

With the db we can go from there.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th October 2018 - 09:18 PM