How to hide rows in a continuous form that have already been 'selected'?

[Adam Tippelt]

Situation:
I've got a main form, ScriptCreator, with two separate continuous sub-forms, ScriptCreatorSub and ScriptCreatorCurrentScript.

The idea is that the ScriptCreatorSub form displays all the options you can pick from, and each row has a 'Select' button next to it. When you click the Select button, the row of data is copied to a new table, called TestsForScripts, via an append query. This table is linked to the second continuous subform, ScriptCreatorCurrentScript. This second subform queries the table and pulls through any items already 'selected' concerning the current script.

So in short, you've got a form of options, and a form showing what you've already picked.

(Before anyone asks, I know that data duplication goes against database structure standards, but the use of the database and the manipulation of data after 'selection' means that data duplication is required, as opposed to just referencing the original table.)

All of this works fine, however what I'm now trying to implement a way of hiding/filtering out the items from the original list that have already been selected.

So for example say I've got Items A through to Z in ScriptCreatorSub. If I pick Item M, Item M appears in ScriptCreatorCurrentScript, and ScriptCreatorSub now only offers a list of Items A-L, N-Z.

I already have a system in place to filter the list on ScriptCreatorSub, which is based around the NeoPa's code example on this forum. I've tried adding in extra coding in the RunFilter process, but it just returns an error of:

Run-time error '2427':

You entered an expression that has no value.


This is the code I'm using for the filters, as I would have thought this is where I can apply new code to achieve what I'm after:

Private Sub RunFilter()
Dim strFilter As String, strOldFilter As String
 
'CheckFilter produces the new Filter depending on the values currently in
'the various filter boxes.
 
    strOldFilter = Me.Filter
    'FilterRef_ID - Numeric
    If Me!FilterRef_ID > "" Then _
        strFilter = strFilter & _
                    " AND ([Ref_ID]=" & _
                    Me!FilterRef_ID & ")"
    'FilterPageTitle - Text
    If Me!FilterPageTitle > "" Then _
        strFilter = strFilter & _
                    " AND ([PageTitle] Like '*" & _
                    Me!FilterPageTitle & "*')"
    'FilterPageAddress - Text
    If Me!FilterPageAddress > "" Then _
        strFilter = strFilter & _
                    " AND ([PageAddress] Like '*" & _
                    Me!FilterPageAddress & "*')"
    'FilterFieldElement - Text
    If Me!FilterFieldElement > "" Then _
        strFilter = strFilter & _
                    " AND ([Field/Element] Like '*" & _
                    Me!FilterFieldElement & "*')"
    'FilterTestFor - Text
    If Me!FilterTestFor > "" Then _
        strFilter = strFilter & _
                    " AND ([TestFor] Like '*" & _
                    Me!FilterTestFor & "*')"
    'FilterType - Text
    If Me!FilterType > "" Then _
        strFilter = strFilter & _
                    " AND ([TypeOfTest] Like '" & _
                    Me!FilterType & "*')"
    'FilterPriority - Text
    If Me!FilterPriority > "" Then _
        strFilter = strFilter & _
                    " AND ([Priority] Like '" & _
                    Me!FilterPriority & "*')"
        strFilter = strFilter & _
                    " AND ([Ref_ID]<>" & _
                    Forms![ScriptCreator]![ScriptCreatorCurrentScript]![Ref_ID]
    'Debug.Print ".Filter = '" & strOldFilter & "' - ";
    'Debug.Print "strFilter = '" & strFilter & " '"
    'Tidy up results and apply IF NECESSARY
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.Filter = strFilter
    End If
        Me.FilterOn = (strFilter > "")
End Sub
 

It's this part that I've added in the above code (near the bottom) which gives the error:

        strFilter = strFilter & _
                    " AND ([Ref_ID]<>" & _
                    Forms![ScriptCreator]![ScriptCreatorCurrentScript]![Ref_ID]
 

If anyone can offer any help, it would be much appreciated! This forum has so far yet to fail on pointing out an obvious answer to my problems! :)

Thanks.

[beacon]

Just on the surface, you have the left side of a parenthesis on line two in the last block of code (immediately after the word "AND"). Try removing that and see if it works.

" AND ([Ref_ID]<>" & _
Forms![ScriptCreator]![ScriptCreatorCurrentScript]![Ref_ID]
 

If it doesn't work, I'm sure I'll take a closer look.

Just so I understand, in case my suggestion doesn't work, does the user pick a letter from the alphabet and the subform filters the records for either the first half or the second half of the alphabet? So if I choose "K", I would get the records that fall between "A" and "M" on the subform, and if I choose "V", I would get the records that fall between "N" and "Z" on the subform?

[Adam Tippelt]

Ah, don't know how that got parenthesis got left in there. Taking it out didn't seem to help though.

I think you've misinterpreted my example - probably my fault for trying to write it in short.
What I was trying to say was if I had a list of the following options:

A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z

If I selected option M, then this would be removed from the original list, and leave only the remaining options:

A, B, C, D, E, F, G, H, I, J, K, L, N, O, P, Q, R, S, T, U, V, W, X, Y, Z

Then if I select another option, lets say option A, this is also then removed from the original list:

B, C, D, E, F, G, H, I, J, K, L, N, O, P, Q, R, S, T, U, V, W, X, Y, Z

The code is supposed to query the current list and remove/hide any options that have already been selected. At the moment it just doesn't want to work.

Perhaps I'm putting the code in the wrong place, and should be putting it as a condition of the record source query?

[Adam Tippelt]

I tried to add a where clause on the record source query with the code:

SELECT TestLibrary.Ref_ID, TestLibrary.PageTitle, TestLibrary.PageAddress, TestLibrary.[Field/Element], TestLibrary.TestFor, TestLibrary.Comments, TestLibrary.ExpectedResult, TestLibrary.TypeOfTest, TestLibrary.Company
FROM TestLibrary
WHERE (((TestLibrary.Ref_ID)<>[forms]![ScriptCreator]![ScriptCreatorCurrentScript]![Ref_ID]
ORDER BY TestLibrary.Ref_ID;
 

But this made the continuous form populate as empty.

(I wasn't sure if "forms" was supposed to be in square brackets or not, but I tried both with and without, which resulted in the same outcome.)

[TheSmileyCoder]

Hi Adam

Your code does not take into account that there can be multiple entries in your form, and neither does it take into account if nothing have been added.
Lets add in a function on your form, call it fStrListSelected()

Private Function fStrListSelected() as String
  Dim rsClone as Dao.Recordset
  set rsClone=Me.ScriptCreatorCurrentScript.RecordSetClone
  rsClone.MoveLast 'Not sure if these 2 lines are necessary
  rsClone.MoveFirst
  if rsClone.RecordCount=0 then
    'Nothing Selected Yet
      fStrListSelected=""
  Else
    Do While Not rsClone.Eof
      'May have to replace , with ;
      fStrListSelected=fStrListSelected & rsClone![Ref ID] & "," 
      rsClone.MoveNext
    Loop
    'There will be a trailing , remove it
    fStrListSelected=left(fStrListSelected,len(fStrListSelected)-1)
  End If
  set rsClone=Nothing
End Function

Now back in your main form, where you do the filtering:

  Dim strList as string
  strList=fStrListSelected
  If strList ="" then
    'Nothing selected, no need to add to filter
  Else
    'Something is allready selected, filter it away
    strFilter = strFilter & _ 
                    " AND ([Ref_ID] Not In (" & strList & ") "
  End If

I THINK this should work, I dont really have a good example at hand to try it on. The code is written straight, so it hasn't been tested. I hope you can work with it anyway.

[Adam Tippelt]

Hi Smiley,

Your observations are true, however perhaps I need to explain why that is:

"Your code does not take into account that there can be multiple entries in your form"

The first form, with the original list, will never contain exact duplicates due to the Ref_ID being a primary key and therefore unique.
If you are referring to the second form, then you are quite right about it not taking duplicates into account. The whole point of what I've asked in this question is to tackle that area – prevent the user from creating duplicates in the first place. I’m trying to find a way from removing the item from the original list after its select button is pressed, so that the user does not have the option of having multiple entries of the same item. Once the Select Button next to the item is clicked, the item is added to the new list, and removed from the original (merely filtered out or hidden, not deleted). Therefore no duplicate entry can be created.
(Technically it wouldn’t be a duplicate anyway as these appended records now obtain a new primary key to keep them unique, but that isn’t visible to the user, and that isn’t the point.)

“and neither does it take into account if nothing have been added.”

I was under the impression that the functionality of it would work in the sense of `if it’s put into the second list, filter it out of the first. If it’s not in the second list, leave it in the first.’ If nothing has been added to the second list, then doesn’t that just mean that nothing matches the filter criteria and therefore nothing is filtered out?

With that in mind, is your suggested code still suitable?

Thank you all for your help and suggestions so far. Please forgive me if I’ve got it totally wrong - I’ve had to self teach myself both Access and VBA in the past couple of months and I’m just going on what seems logical.

Thanks.

[TheSmileyCoder]

Hi Adam

Its obvious you've put alot of thought into both your work as well as your posts. Its very nice, and makes the discussing much easier and exact.

Duplicate vs Multiple records
Now, when I spoke of multiple records, I did not mean Duplicate records. As I understand it, if you add "A" and "H" to your second form, they exist as 2 records in some underlying table. As such there is multiple (non-duplicate) records relating to the primary record (your main form). For each (Bound) form (or subform) access ties a recordset. A recordset has a pointer sometimes called a bookmark, essentially saying which row/record the form is currently looking at. Even though its a continues form, it will only have 1 record in "focus", the pointer will only point to 1 record.

So when you reference:

Forms![ScriptCreator]![ScriptCreatorCurrentScript]![Ref_ID]

It will be the Ref_ID of the record currently pointed at. Access does not in such a case automatically handle that there are multiple records.


No records:
Im not 100% sure on this part, but I will write it as I believe it to be.
Now if your second form has no records and you use:

strFilter = strFilter & _
                     " AND ([Ref_ID]<>" & _
                     Forms![ScriptCreator]![ScriptCreatorCurrentScript]![Ref_ID] 

Your telling access you want all the records different from X, but your not telling access what X is, since X is a reference to something that does not exist. (The form has no record which to point at.)


Now lets turn to my code, which I should have explained better.

Private Function fStrListSelected() as String
  'Create a list of the items selected
     Dim rsClone as Dao.Recordset
 
  'Create a copy of the recordset used in the second form
   set rsClone=Me.ScriptCreatorCurrentScript.RecordSetClone
 
  'Move to the last, then the first record, this is sometimes necessary for access to properly count the records.  
    rsClone.MoveLast 'Not sure if these 2 lines are necessary
    rsClone.MoveFirst
 
  'Count the records
   if rsClone.RecordCount=0 then
     'Nothing Selected Yet, so return an empty list
       fStrListSelected=""
   Else
     'Loop through each record in the forms recordset
     ' while recording the ref ID. 
     ' Im assuming the Ref ID is the id of "A","B", and so on
     Do While Not rsClone.Eof
       'May have to replace , with ;, it depends on your regional settings
       fStrListSelected=fStrListSelected & rsClone![Ref ID] & "," 
       'Move to the next record
       rsClone.MoveNext
     Loop
 
     'There will be a trailing , remove it
     fStrListSelected=left(fStrListSelected,len(fStrListSelected)-1)
   End If
   'Remove the copy from memory (cleanup)
   set rsClone=Nothing
End Function

EDIT: Ups, hit post by accident. Continuing:
If you have selected the records "A","B", and "C", and im gonna gues the ref ID of these is 1,2 and 3, then the function will return a string looking like "1,2,3". The second part of my original code, simply formats this into the SQL string. It would look like:

[Ref ID] Not In (1,2,3)

I hope I have managed to help more then I've confused. Im not always the best at explaining myself.

[Adam Tippelt]

Hi Smiley,

Thanks for taking the time to explain in greater depth. You've explained very well and it makes more sense to me now. I shall give this a try and see if it works.

I think my problem was that my experience with other 'filters' has given the impression that Access does handle that there are multiple records and therefore I've got stuck in this idea that that's what I should be looking for. Perhaps as this is a slightly different circumstance, it requires a slightly different solution.


Thanks for all your help. I'll post if it works or not. :)