No my intention is to open the form and filter by itemIDkey based on the results of a query not change the record source. The query performs a wild card search of the notes for each item. each item may have several "notes" records in a "notes" table linked by a key. Here is the original SQL statement from the query builder.
Code:
SELECT Itemstbl.ItemIDKey
FROM ItemsNotestbl RIGHT JOIN Itemstbl ON ItemsNotestbl.key = Itemstbl.key
WHERE (((ItemsNotestbl.txt) Like "*" & [Forms]![CrossReferencefrm]![ItemNotesWildcardtxt] & "*"));
First, the items in red are neither required nor desired. It is like the difference between how a number is stored and how a number is displayed. The vbCrLf makes that string usable for display formatting (i.e. showing the actual SQL string) but interferes with the query parser (an internal program user) for the string for query purposes.
Second, the items in green lead to questions. Do you actually have a field name TXT in ItemsNotestbl? Do you actually have a field named KEY in Itemstbl and ItemsNotestbl? And I presume you were looking at the value of a control for that reference to .Value?
KEY is listed as an Access reserved word so should not be used as a field name. You can use TXT (but not TEXT) as a field name, I suppose. There CAN be value in brevity. Just be careful to not use TEXT in that place. In the place where you used .VALUE it is legal but not needed because for any control that actually HAS a value, .VALUE is the default property if you don't specify a property.
As to the quoting, you can simplify the asterisk inclusion as shown in blue.
I'm not entirely sure about the locations of the parentheses, though it is obvious that the Access query builder was involved. Whenever you see too many parentheses, the query builder was there.
Thanks I will give everyones suggestions a try. I know the field names are an issue but I am dealing with a access97 database that was nothing but tables to begin with. I will change them but I have so much code tied up in these old carry overs I am hesitant to do anything. I should have changed everything at the beginning like I wanted to. I was busy importing excel files as well that used the same names and still have many to go.
if there are multiple ItemIDKeys that were return, you need to loop through the recordset:
Code:
Dim strIDs As String
Dim rs As DAO.Recordset
Dim Descriptionqryidkey As String
Descriptionqryidkey = "SELECT Itemstbl.ItemIDKey " & _
"FROM ItemsNotestbl RIGHT JOIN Itemstbl ON ItemsNotestbl.key = Itemstbl.key " & _
"WHERE (((ItemsNotestbl.txt) Like ""*"" & me.ItemNotesWildcardtxt.value & ""*""));"
Debug.Print Descriptionqryidkey
set rs = Currentdb.Openrecordset(Descriptionqryidkey, dbOpenSnapshot)
with rs
If Not (.BOF And .EOF) Then .MoveFirst
While Not .EOF
strIDs = strIDs & ![ItemIDKey] & ","
.MoveNext
Wend
.Close
End With
Set rs=Nothing
If strIDs <> "" Then
strIDs = Left(strIDs, Len(strIDs)-1)
DoCmd.OpenForm "itemdatafrm", , , "[ItemIDKey] In (" & strIDs & ")"
Else
'no filter
DoCmd.OpenForm "itemdatafrm"
End If
What no one has noticed is that you are trying to use a select query in your OPEN form command. That is not possible.
You can pass in a query string using the OpenArgs argument but you would need code in the form or report to place that SQL string into the form/report's RecordSource.
The where argument of the openForm method is only the WHERE clause without the WHERE.
If I understand what you are trying to do, and I may not, you will need to use a DLookup() to run a query to get the string you want and then just use the string returned by the dLookup() in the WHERE argument
However, semantics might confuse. DLookup would reference a query object (and I guess that can be considered as 'running' it). So the SELECT SQL would have to be a saved query object (unfiltered) that DLookup could pull from (filter in the DLookup). Otherwise, the SELECT would have to be used to open a filtered recordset object and reference field of recordset to get the ItemIDKey,
I never do quotes this way since I can never get the number of " for each condition correct. I always create a constant and use that. So if you get an error, fix the number of ".
see post #7.
you are only returning 1 record when using dlookup.
there is a possibility that it might return more records since the criteria is not an exact match.
Except that in comments or people's names, the string can contain an apostrophe and that will make the query fail. Try it. Add a name like O'Brien to a query that encloses the search string in apostrophes.
Good point arnel but thirtycal would have had the same problem with his original version had it worked. It is a logic problem that needs to be solved regardless before we can proceed to a real solution.
Well, I am glad to see it is not just me. I really do try hard to accomplish my goals before reaching out. So yes I could have multiple results and usually do. So lets say that I have a bearing and its manufacturer part number is 12345. Well this was never stored in the part description since those were viewed on sales orders. That was always stored in the notes of the item. The notes also contain information as to what other bearings were used as a substitute. Being able to search these notes allows me to see all items that used that bearing when attempting to find substitutes. The main form for each item has a subform for all of these notes. This search is kind of a reverse master child link.
So here is an idea. Instead of trying get the main form to open first based on the query. I could open a hidden temp form (just a copy of the subform) that holds the data with the original query as the record source. Then open the main form based on the itemIDKey from the temp form. Not pretty but would get it done.
In general, you need to follow this rule: Access won't tell you anything you didn't tell it first. If you want to search for something and KNOW you will need to search for it, don't make your life more difficult by hiding that something in an obscure place. If that part number is something you will need to find, put it by itself in a place where a simple search can find it. This is a prime example of the "pay me now or pay me later" concept. Pay a little price up-front, reap a HUGE benefit down the road.
Doc Man I agree ideally all of these notes would be in a notes field for the item record. Easy to search and is tied directly to the item. Unfortunately the data was dumped from symix syteline in 2007. This is how the data was stored there and referenced. The bad part was the data was just dumped and into several different access databases with no information as to how it all linked. The company was bought out and is now using SAP. Syteline is still used but only for historical data and no changes or new queries are allowed.
I would concatenate all the records into one field for the item record but I kind of like that I can keep people from deleting the old notes by accident but allow them to add new if needed. I suppose that I could use two fields old notes and new notes and achieve the same thing.
I am also not convinced that notes do not coincide with a jobs table I have. Both the Items and Jobs table have a Key field and a Key Sequence field. So this Key field has multiple records that are duplicates with the sequence field being different. In theory a job could have one note based off of key sequence combination while the item shows all the notes. Half the battle is figuring out the data structure.
If your initial search will produce multiple records then you cannot use dLookup() or anything similar. Finding the record will be a two step process. Create a form with search fields in the header. Create a DS view or continuous subform to show the results. In the dbl-click event on one of the fields in the subform, use the OpenForm method to open a form with a single specific ID that comes from the record you are sitting on.
If you want to get slick, run a dcount() on the selection criteria and if it returns just a single record, use OpenForm directly. Otherwise, just populate the subform and let the user choose one from the list of records.
Thanks Pat. That is what I will have to do. I tried what I mentioned previously and it only pulls the first record off the list of queried results. Oh well this should have the query taking less time than if I had to populate all of the information in the other form.
