Like Filter in Query Based Form

[DJRhino1175]

I have a form I open to close out a Reject Tag Number. I have the underlying query where it asks for the Reject Tag Number to be closed. My question is can this have more than one and if I have it set up for more than one but only need one how would I code that?
Here is what I normally put in the query

Like [Enter Reject Tag Number]

Thanks a million for your guidance

[NeoPa]

Hi DJ.

I'll work with what you've posted, but for a clearer, fuller answer you may need to explain your situation (a lot) more clearly.

The SQL term Like is designed for use with wildcards. For flexibility it can be used to match an individual item but if you are looking at that then = is the more appropriate term to use.

SQL also has an In() operator which takes a list of comma-separated items and matches if any are found.

To determine how to incorporate that into your scenario would require me to have an understanding of it not afforded by your description.

[DJRhino1175]

Neo,
I click on a button that opens a form based on this query. I would like to filter that for based on a number or multiple numbers, it all depends on how many "Tags" I need to close out. Right now when I open it all open "Tags" will be available in the form. I would like to filter it to the "Tag" numbers I need to close out. It could be one "Tag" or Multiple "Tags" Normally I would use "Like [Enter Reject Tag Number]", but this will only give me the one "Tag" to close out. Then would need to hit refresh to do the next one. was hoping to see if there was a way to do this. I tried adding multiple "Like [Enter Reject Tag Number]" in the query under the or section but that did not give me multiple entries just asked for one.

Hope that clears things up more.

[NeoPa]

Hi Rhino.

DJHRhino1175:
Hope that clears things up more.

Not in the least. You've repeated what you've already said without giving any indication of where the data comes from that you would like to filter on; how you are currently, and expect to, invoke the Form to use this; how you expect the user to specify multiple tags for you to work with.

My intention was not to give you chapter & verse as to what you should be doing before posting a question, but to encourage you to consider that for yourself before ever posting the question in the first place. What information will someone need in order to assist you? That's your question. Your responsibility.

I'm happy to help with technical problems that you struggle with. Less happy to do the basic work for you - because you can't/won't.

[DJRhino1175]

The data comes from an earlier stage in the process. I tag is created when a product needs to be reject due to a defect or whatever. The product gets looked and a manager will examine it to see if it needs to be reworked or scrapped out. Once this is done a tag or multiple tags will be turned into me or one other person to close. When I go in to close a tag I click a button to open a form to close out this tag or tags. I would like a parameter box to open up and ask me to enter a tag number. Which I already know how to do and can do very easy using "Like [Enter Reject Tag Number]" in the under lying query. I was hoping for guidance on how to do something similar to this but for multiple tags. Could be any where from 1-10 tags, maybe more. I don't need you to do the work for me, I'm just stumped on ho to get it to ask me for more than one number, but if I only have 1 number I don't want it to throw an error message out.

Here is the underlying SQL from the query

SELECT [Reject Tags].*
FROM [Reject Tags]
WHERE ((([Reject Tags].CANCEL)=False) AND (([Reject Tags].Closed)=False));

As you can see the form when open will give me all tags that are still open and not cancelled. I would like to narrow down to just the tags that I need to close.

Thanks

[zmbd]

@DJRhino1175

guidance on how to do something similar to this but for multiple tags

You're going to have to use a form and a bit of VBA.
At least in Access2003 and above there is the TempVars object https://docs.microsoft.com/en-us/off...ccess.tempvars that I use for batch update/delete operations... if I'm lazy I just use a ForEach() loop... feeling a bit more enthusiastic I build an SQL string and the In-Operator and then execute the SQL.
The one thing I like about the ForEach() loop and the TempVars() is that I can set a progress bar (really simple one) or count-down... sometimes there are dozens of records and it gives a bit of user-feedback so they don't force quit.

Thing is, how do you want to get the tag information from the user?
1) Simple text box that I parse the string - I've moved away from this
2) Simple text box that accepts only one sample-id at a time...
3A) Form where they select the record, the button runs a "lookup" against the TempVar and if the record id isn't there then appends the value...
3B) or appends to the string that's being built for the In-Operator.

In (3B) sometimes I'll use the IN() on a SELECT to show a popup-form with the selected records and they have to click on confirm.

clear as mud?
Sometimes the voices in my head understand me better than I do👾

[DJRhino1175]

zmbd,

I receive a paper sheet from the sort area and this is where I get the Tag/ID Number from.

I think I like the 2) idea, it might be the easiest for when I'm not here for another user to use.

Thanks

[NeoPa]

Hi Rhino.

DJHRhino1175:
I don't need you to do the work for me, ...

Strangely enough, I'm happy enough to get down and do some of the work for you. Not all, as that would be wasting both of our times. What I need you to do, and this is a much better stab at it to be fair, is the work beforehand involving consideration of how to express the question such that I have enough information to help you.

To be fair I actually do understand how difficult this can be for most people. My nudging you is nevertheless doing both of us a service. Knowing how to ask a question intelligently can be enormously helpful for you as an individual.

Now, I said the info was better now, and it is. Let's see what I can offer to help guide you towards a better understanding of how you can go about this.

DJHRhino1175:
I'm just stumped on how to get it to ask me for more than one number, ...

This is the crux of the matter. Usually I'd want to ask you to tell me what approach you're taking, but I can see that this is where you're stuck so let's see what we can offer.

There are a number of possible options. I was hoping to avoid having to spell them all out but we work with all levels here and if that's what you need let's give it a go.

Before I start though, while In(Val1,Val2,..., Valn) works perfectly well for separate values such that all individual items are matched, this does not work for a single string passed from a prompt which just happens to include values with commas. Values passed that way (using the prompt and square brackets[]) do not get parsed by SQL so can only be treated as a simple string value.

To illustrate, the following will not match 1, 3 & 5 separately - but only a string value of "1,3,5" when that string value is entered when prompted :-(

WHERE [MyField] In([Please enter 1,3,5])

This is a nuisance as it means the simplest approach is denied us :-( We will either need to prompt more professionally using a Form, or at least use some VBA to do some of the parsing ourselves to get the filtering you want.

1. InputBox()
   This approach is not recommended as it either allows the operator to f^Hmuck things up for you, or alternatively forces you to do extra validation of the entry.
   
   Anyway, if you prompt the user to enter a list of values separated by commas then you can use that to filter the Form.
   

   strFilter = "Please enter all desired [Reject Tag Number]s " _
             & "separated by commas."
   strFilter = InputBox(strFilter, "RTNs")
   strFilter = Replace("In(%F) And Not ([CANCEL] Or [Closed])" _
                     , "%F", strFilter)
   Call DoCmd.OpenForm(FormName:="YourForm" _
                     , WhereCondition:=strFilter)

2. ListBox
   This involves populating a Multi-Select ListBox on an earlier Form with only those items from the table you want the operator to be able to select from (Not ([CANCEL] Or [Closed])). This still involves parsing the data yourself with VBA.
   

   strFilter = ""
   For Each varItem In Me.YourListBox.SelectedItems
       strFilter = strFilter & "," & varItem
   Next VarItem
   strFilter = Replace("In(%F) And Not ([CANCEL] Or [Closed])" _
                     , "%F", Mid(strFilter, 2))
   Call DoCmd.OpenForm(FormName:="YourForm" _
                     , WhereCondition:=strFilter)

3. Form
   This would involve storing your selections in a table. This could be a new Boolean (Flag) Field within [Reject Tags] or more tidily in a separate table created for this specific purpose. Whichever approach is used you must remember to reset any previous selections before use. I won't tell you how to design such Forms at this stage but we can get into those details later if you decide you need to.
   1. Within [Reject Tags]
      Once your new special Form has been closed and the selection data updated :
      

      strFilter = "[YourNewFlag] And Not ([CANCEL] Or [Closed])"
      Call DoCmd.OpenForm(FormName:="YourForm" _
                        , WhereCondition:=strFilter)

   2. New Table
      Once you new special Form has been closed and the selection data updated :
      

      strFilter = "In(SELECT * FROM [YourNewTable]) And " _
                & "Not ([CANCEL] Or [Closed])"
      Call DoCmd.OpenForm(FormName:="YourForm" _
                        , WhereCondition:=strFilter)

You can probably see now why I wanted a clear understanding of exactly where you were in your thinking before answering, so I didn't need to cover so many possibilities. Much easier for me to produce, and also easier for you to get to grips with. Now it's up to you to consider carefully which of these approaches will work best for you and then we can fill in any further gaps of detail you may need.

[DJRhino1175]

Wow, I see this is going to be way more complicated hen I was thinking. I will give it my best shot to see where it takes me, if I run into anything I can't figure out I'll reply back. I think I'm going to try # 3.

Thanks for the guidance.

[zmbd]

@NeoPaListBox
This involves populating a Multi-Select ListBox on an earlier Form...

silly me... I use this in the student-roster database and in one of my Batch databases at work.
Personally, I find that it works best if the user doesn't have to scroll - I'll set these up along the lines of a cascading combo/drop-box to filter down to under 20 or so items.

For larger lists I'll use a continuous form and the double-click event in the record to store the selected record id to either the string I'll use to build the SQL-IN() for DB.Execute() or to the TempVars() collection for the ForEach(). I like either of these because I can GUI feedback about records effected and/or progress

I've also used a field in the table, default 0, and checkmark control in the form to set the field to -1. Command button to db.execute stored delete-action-query that deletes all records where the field value is -1. It's an OK method; however, I've found sometimes when I have to delete related records (because I don't use cascade-delete) the application will appear to stall... for my experienced staff, they've learned to just walk away for a few minutes when that happens.

[DJRhino1175]

Neopa,

I'm looking at 3)Within [Reject Tags]

I use a password form to open this using a Macro - Will be replacing this soon to use VBA

Or should I get this fixed first before proceeding with your help?

Code: If password = **** Then OpenForm "Close Reject Tag Form"

Form Name: Close Reject Tag Form

Record Source: [Close Reject Tag Form]

sql: SELECT [Reject Tags].*
       FROM [Reject Tags]
       WHERE ((([Reject Tags].CANCEL)=False) AND (([Reject Tags].Closed)=False));

How would I proceed from here? I believe I will create a table and call it

Table Name: tblRTNOSelcet
Field Name: RTNo

Am I on the right track to start with or am I way off?

[NeoPa]

Hi Rhino.

If you're looking at option 3A then you need a new Field ([Selected]?) in your existing table (Reject Tags) but not an extra Table. 3B uses a separate Table instead. Only one or the other would be required. Not both.

Before we proceed I need to understand which you've chosen - 3A or 3B.

Password Forms
Don't do it. Simple; easy to remember; just don't.

Why? Because however well you manage it you will not approach the level of security already supplied & available from the operating system (See Retrieve User ID and base your restrictions (or access) on the values returned from that. With very little extra you can also get an account's Security Group membership list). If you'd like to take this further then please understand it does not come within the scope of this thread. However, you can be sure I'll see it & respond if you post it separately.

Be aware also that storage of passwords, whether encrypted or in plain text, is not just a security hole in your application but, due to the tendency of many users to reuse passwords across different systems, a security hole in every other system they use as well. You do not want to be found to be responsible for a user having their online identity stolen, or any other possible effect of allowing someone to break into another of their accounts. The risks are essentially infinite. As I say - just don't do it.

Your SQL
Please consider losing some of the extraneous and unnecessary garbage in your WHERE clause. In it's cleanest form it should simply read :

WHERE Not [CANCEL] And Not [Closed]

or even :

WHERE Not ([CANCEL] Or [Closed])

Booleans are already Booleans so don't need (and should not have) equations to convert them to Booleans. Access typically inserts this automatically because of the complication of treating values other than Booleans so they behave properly when used in filters. It is wholly unnecessary when dealing with values known to be Boolean (Yes / No or -1 / 0 in their intrinsic form).

[DJRhino1175]

Lets go with 3a I think that will be the easiest to go with. I also updated the sql to match this:

Not ([CANCEL] Or [Closed])

I also added the field [Selected] into my table "Reject Tags" I left it as short test for the moment.

[NeoPa]

Hi DJ.

In this scenario we would want [Selected] to be a Boolean (or Yes/No) Field in the table. Like [CANCEL] & [Closed] presumably are.

First point to remember is that these values are always transient and have no good meaning outside of this specific usage - and that includes the specific time they're set up and used. For this reason we designers often use a belt & braces approach to resetting this data & do it both before and after it's used. We don't want any users seeing some records checked and others not & wondering wtf is going on do we. Personally I prefer the separate table for this reason. No danger of the extra Field getting in the way of other work and/or confusing the natives ;-) But we go with 3A for now anyway.

Before I get into more detail of the new Form I will remind you of the very simple SQL that you'll need in order to use it (repeated from post #8) :

strFilter = "[YourNewFlag] And Not ([CANCEL] Or [Closed])"

Or, if you don't use the basic query elsewhere, then you can change that and not use the WhereCondition parameter at all, as it will always be the same :

SELECT [Reject Tags].*
FROM [Reject Tags]
WHERE [YourNewFlag] And Not ([CANCEL] Or [Closed]);

Also, for clarity, I will use the name [frmSelRTNo] for the Form that you will use (Design now if you haven't already).

It seems I'm called away for now but will post again this evening with how to invoke your new Form. There's plenty of food for thought here anyway so time not wasted ;-)

[DJRhino1175]

NeoPa,

I do not use this query anywhere else, so I'll just modify that. The only thing I don't understand is what do you mean by YourNewFlag? Would the be

[Selected] and not ([Cancel]) or ([Closed])

I created a form called [frmSelRTNo] based on the query [Close Reject Tag Form] with only "Reject Tag Number" and "Selected" fields available.

I'm hoping I didn't jump ahead or go to far. If so this can easily changed.

[NeoPa]

DJRhino1175:
The only thing I don't understand is what do you mean by YourNewFlag?

My apologies. I was called away so didn't spend the usual amount of time checking carefully over what I'd written before I posted it :-( Your amendment is absolutely spot on :-)

Do I know what you have in [Close Reject Tag Form]? Whatever the answer I suspect you're on the right lines. It should look something like :

SELECT   [Reject Tag Number]
       , [Selected]
FROM     [Reject Tags]
WHERE    Not ([CANCEL Or [Closed])
ORDER BY [Reject Tag Number]

Still to post the rest later this evening, though there may not be too much left now.

[NeoPa]

Hi DJ.

The code to open the new Form (frmSelRTNo) would go something like what I show below. I'll leave you to add the Dim lines as they should be straightforward, but notice dbCurDb should be done as DAO.Database.

Set dbCurDb = CurrentDb()
'Before we open the Form we must clear the data.
strSQL = "UPDATE [Reject Tags] Set [Selected]=False"
Call dbCurDb.Execute(strSQL, DbFailOnError)
'Notice we open as Dialog so the following code is triggered
' only after it's closed.
Call DoCmd.OpenForm(FormName:="frmSelRTNo", WindowMode:=acDialog)
...
'Open up your old Form here which is filtered as discussed earlier.
...
'Clear the [Selected] data again.
Call dbCurDb.Execute(strSQL, DbFailOnError)

[DJRhino1175]

Not sure if I missed something. It runs the code just fine. It opens the dialog form. I'm able to select multiple tags by checking the yes/no box, but when I close this form, it opens the correct form but it isn't filtered. I get no error messages. Here is the code as I have it currently:

Private Sub Disposition_Close_Reject_Tag_Click()
 
Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb
Dim strsql As String
 
On Error GoTo Close_Err
 
    'Before we open the Form we must clear the data.
    strsql = "UPDATE [Reject Tags] Set [Selected]=False"
    Call dbsCurrent.Execute(strsql, dbFailOnError)
    'Notice we open as Dialog so the following code is triggered
    ' only after it's closed.
    Call DoCmd.OpenForm(FormName:="frmSelRTNo", WindowMode:=acDialog)
    Call DoCmd.OpenForm(FormName:="Close Reject Tag Form")
 
 
Close_Exit:
    Exit Sub
 
Close_Err:
    MsgBox Error$
    Resume Close_Exit
 
'Clear the [Selected] data again.
Call dbsCurrent.Execute(strsql, dbFailOnError)
 
End Sub

[DJRhino1175]

Neopa,

I was able to get it. I had "frmSelRTNo" set up incorrectly and didn't have [Select] set up right in the query for the close form. Tried it 3 or 4 times and it worked every time. Worked like a charm. Thanks for your assistance.

[NeoPa]

Hi DJ.

Very pleased to hear you managed to get it working, and you're very welcome to the asistance :-)

I had to reset your selection for Best Answer as we very rarely award that to the person asking the question (for what should be obvious reasons) and it also has to be something that someone new coming to the question would find most illuminating or illustrative. I'm sure you were simply trying to express that you were happy with the result - and that we do appreciate.

Having looked at your code I would recommend that you make consistent use of mixed case in the naming of your variables. The usability of strsql & strSQL are some distance apart. I'll leave you with that parting idea.