Use an array to filter a form

[Ruegen]

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

2 - they are locked. How would I set yes/no?

[June7]

Changing value in table, not query and not on the form. You use code to write booking ID to array, could just as easily run an edit and update on a recordset of the table.

[Ruegen]

Changing value in table, not query and not on the form. You use code to write booking ID to array, could just as easily run an edit and update on a recordset of the table.

Sounds good - how do I write to the matching row in the table?

Create another dao? With the table as the recordset?

[Ruegen]

I'm thinking...

Private Sub Form_Load()




Dim i As Long
Dim strWhere As String, strDo 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
    DoCmd.Close acForm, "frmBookingEditAdminStep"


    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
strDo = "UPDATE tblTradingAs INNER JOIN (tblShows INNER JOIN tblBookings ON tblShows.ShowsID = tblBookings.ShowsID) ON tblTradingAs.TradingAsID = tblShows.TradingAsID SET tblBookings.BookingReflowTool = True WHERE (((tblBookings.BookingDate)=" & myArray(i) & ") AND ((tblShows.TheatreShow)=False));"
DoCmd.RunSQL strDo
rs.MoveNext
Next i




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


rs.MoveFirst
For i = 0 To (rs.RecordCount - 1)
myArray(i) = rs!BookingsID
strDo = "UPDATE tblTradingAs INNER JOIN (tblShows INNER JOIN tblBookings ON tblShows.ShowsID = tblBookings.ShowsID) ON tblTradingAs.TradingAsID = tblShows.TradingAsID SET tblBookings.BookingReflowTool = false WHERE (((tblBookings.BookingDate)=" & myArray(i) & ") AND ((tblShows.TheatreShow)=False));"
DoCmd.RunSQL strDo
rs.MoveNext
Next i


rs.Close
Set rs = Nothing


End Sub

[June7]

Can use DAO recordset of the table and FindFirst method.

Or

CurrentDb.Execute "UPDATE tablename SET fieldname=True WHERE ID=" & Me.ID

Problem with the Yes/No field idea is if there are multiple simultaneous users, they will interfere with each other.

The temp table (in frontend) might be best approach.

[Ruegen]

Can use DAO recordset of the table and FindFirst method.

Or

CurrentDb.Execute "UPDATE tablename SET fieldname=True WHERE ID=" & Me.ID

Problem with the Yes/No field idea is if there are multiple simultaneous users, they will interfere with each other.

The temp table (in frontend) might be best approach.

They'd have to search the records at the exact same time - I will have it set to false right after it has executed.

Problem I have is that the above code gives me a about to change 0 records

[June7]

What would prevent multiple users performing simultaneous search?

Why are you using SQL that is a join of tables? If you have the desired booking IDs in the array, why is additional filter criteria needed?

[Ruegen]

Cleaned up - execute gives me an error

Dim i As Long
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
    DoCmd.Close acForm, "frmBookingEditAdminStep"
    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
CurrentDb.Execute = "UPDATE tblBookings SET tblBookings.BookingReflowTool = True WHERE (((tblBookings.BookingsID)=" & myArray(i) & "));"
rs.MoveNext
Next i




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


Set rs = Me.RecordsetClone
rs.MoveFirst
For i = 0 To (rs.RecordCount - 1)
myArray(i) = rs!BookingsID
CurrentDb.Execute = "UPDATE tblBookings SET tblBookings.BookingReflowTool = False;"
rs.MoveNext
Next i

[Ruegen]

What would prevent multiple users performing simultaneous search?

Why are you using SQL that is a join of tables? If you have the desired booking IDs in the array, why is additional filter criteria needed?

I swap the form's recordsource to an unlocked one (due to many to many tables)

[Ruegen]

Also I should be using DoCmd.RunSQL ("UPDATE tblBookings SET tblBookings.BookingReflowTool = True WHERE (((tblBookings.BookingsID)=" & myArray(i) & "));"), -1

not sure if the -1 gets rid of the update table prompt

[Ruegen]

This works

I'd appreciate a look through

Private Sub Form_Load()




Dim i As Long
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
    DoCmd.Close acForm, "frmBookingEditAdminStep"
    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
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblBookings SET tblBookings.BookingReflowTool = False WHERE (((tblBookings.BookingReflowTool)=True));"), -1
For i = 0 To (rs.RecordCount - 1)
myArray(i) = rs!BookingsID
DoCmd.RunSQL ("UPDATE tblBookings SET tblBookings.BookingReflowTool = True WHERE (((tblBookings.BookingsID)=" & myArray(i) & "));"), -1
rs.MoveNext
Next i




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


Set rs = Me.RecordsetClone
rs.MoveFirst
For i = 0 To (rs.RecordCount - 1)
myArray(i) = rs!BookingsID
DoCmd.RunSQL ("UPDATE tblBookings SET tblBookings.BookingReflowTool = False WHERE (((tblBookings.BookingReflowTool)=True));"), -1
rs.MoveNext
Next i
DoCmd.SetWarnings True
rs.Close
Set rs = Nothing


End Sub

[June7]

So as long as the form is not refreshed/requeried, the user has a form filtered to the desired records?

I really don't see need for array and don't have to declare recordsetclone object.

With Me.RecordsetClone
.MoveFirst
While Not .EOF
CurrentDb.Execute "UPDATE tblBookings SET BookingReflowTool = True WHERE BookingsID=" & !BookingID
.MoveNext
Wend
Me.RecordSource = "qryAdminStep2_KEEP" ' change recordset to the unlocked recordset
CurrentDb.Execute "UPDATE tblBookings SET BookingReflowTool = False"
End With

Although with this approach, will see each record get focus on the form. Establishing a RecordsetClone object won't do that.

With CurrentDb.Execute method, don't need SetWarnings.

[Ruegen]

So as long as the form is not refreshed/requeried, the user has a form filtered to the desired records?

I really don't see need for array and don't have to declare recordsetclone object.

With Me.RecordsetClone
.MoveFirst
While Not .EOF
CurrentDb.Execute "UPDATE tblBookings SET BookingReflowTool = True WHERE BookingsID=" & !BookingID
.MoveNext
Wend
Me.RecordSource = "qryAdminStep2_KEEP" ' change recordset to the unlocked recordset
CurrentDb.Execute "UPDATE tblBookings SET BookingReflowTool = False"
End With

Although with this approach, will see each record get focus on the form. Establishing a RecordsetClone object won't do that.

With CurrentDb.Execute method, don't need SetWarnings.

Yeah I don't want to get any focus, I guess I could ditch the array (though it was a good learning experience)

I noticed you used while not instead of do until > any reason?

also >> Wend <<?

whileend

[ItsMe]

Close While using Wend

Close Do using Loop

Do While
Something
Loop

Do Until
Something
Loop

While
Something
Wend

These are the rules I use:
If you want to write code that you can exit a loop then use Do and Loop. If you do not write code in the loop to "Exit Do" then leave Do out entirely. Another reason to use Do is when you need to evaluate "Until".

[Ruegen]

Thanks June, your way is far better

I changed some of it to speed it up (It's a lot of records to go through)
Me.RecordSource = "qryAdminStep2_KEEP" ' change recordset to the unlocked recordset
CurrentDb.Execute "UPDATE tblBookings SET tblBookings.BookingReflowTool = False WHERE (((tblBookings.BookingReflowTool)=True));"