Use an array to filter a form

[Ruegen]

I have a form with a fairly large set of tables which lock up my records to read only because I have a few many to many junctions involved.

However I would like to open the form, display the records then loop through them all and stuff each ID into an array.

Then I would like to change the record source of the form and give it a less expanded recordset (less the junction tables) and have it be filtered by the array.

so the array would take 223, 224, 224

then filter the form with records that match.



Any advice would be appreciated greatly

[pbaldy]

I personally wouldn't use an array. I'd probably build a string that looked exactly like what you posted, then when building the new SQL use that string in an In() clause. Basically like this, though it's using a multiselect listbox.

http://www.baldyweb.com/multiselect.htm

[Ruegen]

I personally wouldn't use an array. I'd probably build a string that looked exactly like what you posted, then when building the new SQL use that string in an In() clause. Basically like this, though it's using a multiselect listbox.

http://www.baldyweb.com/multiselect.htm

I ended up building the array and a string using it

Dim i As Long
Dim rs As DAO.Recordset
Dim myArray() As Long
Dim txt As String
Set rs = Me.RecordsetClone


ReDim myArray(0 To (rs.RecordCount - 1))


rs.MoveFirst
For i = 0 To (rs.RecordCount - 1)
myArray(i) = rs!BookingsID
txt = txt & myArray(i) & ","
rs.MoveNext
Next i


txt = Left(txt, Len(txt) - 1)


MsgBox txt

I want to be able to manipulate the array.

I still need to figure out how to filter the form using the string or array.

since I have the array now I can build it as anything - which I want to now put as a where

so the filter can get every ID and filter the form to only show those records with the same ID

yet to figure out.

[Ruegen]

Now makes the where string

Dim i As Long
Dim txt As String, strWhere As String
Dim rs As DAO.Recordset
Dim myArray() As Long
Set rs = Me.RecordsetClone


ReDim myArray(0 To (rs.RecordCount - 1))


rs.MoveFirst
For i = 0 To (rs.RecordCount - 1)
myArray(i) = rs!BookingsID
txt = txt & myArray(i) & ","
strWhere = strWhere & "([BookingsID] = " & myArray(i) & ") or "
rs.MoveNext
Next i


txt = Left(txt, Len(txt) - 1)






MsgBox txt
MsgBox strWhere

[Ruegen]

And the working code

Works like it should - would appreciate a look through

Private Sub Form_Load()


Dim i As Long
Dim strWhere As String
Dim rs As DAO.Recordset
Dim myArray() As Long
Set rs = Me.RecordsetClone
    
    'closes form if record count 0
    If rs.RecordCount <= 0 Then
    MsgBox "There are no bookings listed"
    rs.Close
    Set rs = Nothing


    Exit Sub
    End If
    
'gives the array a final number (minus 1 because array starts at 0)
ReDim myArray(0 To (rs.RecordCount - 1))


'move to first record, record it and stuff it into array and builds a string then loops through all
rs.MoveFirst
For i = 0 To (rs.RecordCount - 1)
myArray(i) = rs!BookingsID
strWhere = strWhere & "([BookingsID] = " & myArray(i) & ") or "
rs.MoveNext
Next i


strWhere = Left(strWhere, Len(strWhere) - 4) 'remove the trailing or


Me.RecordSource = "qryAdminStep2_KEEP" ' change recordset to the unlocked recordset


'filter form
Me.Filter = strWhere
Me.FilterOn = True


End Sub

[Ruegen]

The problem I have now is that the string is too long - I can remove [BookingsID] from it and make it shorter however I would like a better solution

[Ruegen]

I'm thinking to write to a text file, then in the sql of the query import the text file as the where statement... if possible.

[ItsMe]

You might try a Variant in place of the string but I don't know what you mean by too long.

replace strWhere with varWhere

Instead of a text file write it to a temp table. Then you could do an inner join to the query that your form is based on and change the form's recordset to that, maybe. And for that matter, you already have the data set so maybe there is an alternative to the Array.

It has been a long day so I may not be thinking clearly.

[Ruegen]

You might try a Variant in place of the string but I don't know what you mean by too long.

replace strWhere with varWhere

Instead of a text file write it to a temp table. Then you could do an inner join to the query that your form is based on and change the form's recordset to that, maybe. And for that matter, you already have the data set so maybe there is an alternative to the Array.

It has been a long day so I may not be thinking clearly.

Not sure you can have a me.filter = varWhere because it isn't a string?

I have made a text file that puts in each where line "([BookingsID] = " & myArray(i) & ") or " but bringing that into the query is unknown to me

[Ruegen]

I have the text file contain

"([BookingsID] = 25780) or "
"([BookingsID] = 25548) or "
"([BookingsID] = 25455) or "

now in the sql on the where I hope to import the directory and file

WHERE = 'directory/file'

or something like WHERE (('c:\QueryImport\WhereStatement.txt') AND ((tblShows.TheatreShow)=False))

update: I have removed the final or in the file

should read

 
([BookingsID] = 25717) or 
([BookingsID] = 25398) or 
([BookingsID] = 25832) or 
([BookingsID] = 25834) or 
([BookingsID] = 25782) or 
([BookingsID] = 24950)

[ItsMe]

Yah, I was not sure what you would do with the Variant once you had it. Just thinking of a way to make room.

You can edit a query object using DAO and query defs. Not the most fun thing in the world. That is why I was thinking to append the booking ID to a temp table. What do you want with the Booking ID any way, open another form? You could place a subform on the existing form and link the Master Child on the Record ID. Then just hide controls on the main form and cause the subform to be visible. Might take a performance hit though.

EDIT : Looking at post #10 it seems the problem is you are storing it as an Array. That is why your string is so large.

[Ruegen]

Yah, I was not sure what you would do with the Variant once you had it. Just thinking of a way to make room.

You can edit a query object using DAO and query defs. Not the most fun thing in the world. That is why I was thinking to append the booking ID to a temp table. What do you want with the Booking ID any way, open another form? You could place a subform on the existing form and link the Master Child on the Record ID. Then just hide controls on the main form and cause the subform to be visible. Might take a performance hit though.

EDIT : Looking at post #10 it seems the problem is you are storing it as an Array. That is why your string is so large.

if I store it as a string I am still going to be over the 255 character limit due to there being so many records...?

[ItsMe]

Yah, I don't know if the limit you are experiencing is characters or mb. I would start by looking for an alternative to the Filter property. I still am not quite sure what you are doing or why. I build my Where Criteria and WHERE statements in a single line/string.

Building a query from the form's dataset may be what you need. I do not know.

[Ruegen]

Yah, I don't know if the limit you are experiencing is characters or mb. I would start by looking for an alternative to the Filter property. I still am not quite sure what you are doing or why. I build my Where Criteria and WHERE statements in a single line/string.

Building a query from the form's dataset may be what you need. I do not know.

Because I have one query that is locked due to having more than one many to many relationships - it locks the query so it can't be edited.

I want to search by performer (which is the extra junction) so I do, then grab all those IDs of the related bookings then give the form an editable query (less that performer junction) that opens all the records to display those booking id that have been filterd by the grab.

It's why I change the recordsource - to swap them out. But because I haven't got the performer table anymore I need to filter it by something. BookingIDs fits.

[June7]

The 255 character limit is for each parameter in a parameterized query. It appears Filter property has the same limit. I wonder if the WHERE CONDITION of OpenForm/OpenReport has the same limit.

Options I see:

1. As ItsMe suggested, a 'temp' table to save the BookingIDs, join Bookings table to the temp table to retrieve only the records that match.

2. Yes/No field in the Bookings table and code sets the value for each record