Many to Many junctions locking query

[Ruegen]

I have a record source on a form - I have two junction tables for many to many and as soon as I add the second junction it locks the query and I can't edit the details/fields content.

Is there a way around this and why does this even happen?

[June7]

This is expected. With multiple many-to-many relationships in same query, the result will be a non-editable dataset.

Use a form to edit only one table. Use separate subforms for the junction tables.

[Ruegen]

This is expected. With multiple many-to-many relationships in same query, the result will be a non-editable dataset.

Use a form to edit only one table. Use separate subforms for the junction tables.

I need to search a booking (attached to 1st juntion by performer (attached to 2nd junction)

I have a form which does a date search and opens a booking form - here you can edit the details

I have to also now search by performer

If I have the query in the record source in booking form include the performer it will lock up

so I don't know how to search by performer through bookings without locking up the record source

I'm thinking if I can use sql vba to run a search then bring up only the records that match the same booking id then I won't have to add the performer junction to the record source of the booking form...

[June7]

I guess you will have to search a query that is not used as the form RecordSource.

[Ruegen]

I guess you will have to search a query that is not used as the form RecordSource.

I was thinking of using something like this on the button

strSql = "SELECT tblBookings.BookingDate, tblBookings.BookingsID, tblBookings.BookingYear, tblBookings.OrderDate, tblBookings.ShowsID, " _
& "tblBookings.SHOW , tblBookings.ShowPricePerStudent, tblBookings.BookingMinimum, tblBookings.ShowTime1st, tblBookings.ShowTime2nd," _
& "tblBookings.ShowTime3rd , tblBookings.ShowTime4th, tblBookings.ShowTime5th, tblBookings.EstimatedAttendance, tblBookings.TeachersAttending, " _
& "tblBookings.MemoPerformer , tblBookings.MemoPerformerPriceChange, tblBookings.MemoSchool, tblBookings.MemoBooking, tblBookings.StatusID, " _
& "tblBookings.DateProposed , tblBookings.TimeProposed1st, tblBookings.TimeProposed2nd, tblBookings.TimeProposed3rd, tblBookings.TimeProposed4th, " _
& "tblBookings.TimeProposed5th , tblBookings.DateAccepted, tblBookings.RejectedDateReason, tblBookings.OriginalBookingDate, tblBookings.BookerIDBonus, " _
& "tblBookings.BookerIDBonusName , tblBookings.BookerIDName, tblBookings.CancellationDate, tblBookings.CancellationContact, tblBookings.CancellationNumber, " _
& "tblBookings.CancellationNotification , tblBookings.CancellationReasonID, tblBookings.ContactAcceptedName, tblBookings.ContactAcceptedSurname, tblJncShows.PerformersID " & vbCrLf & _
"FROM tblPerformers RIGHT JOIN ((tblShows RIGHT JOIN tblBookings ON tblShows.ShowsID = tblBookings.ShowsID) LEFT JOIN tblJncShows ON tblShows.ShowsID = tblJncShows.ShowsID) ON"   _
& "tblPerformers.PerformersID = tblJncShows.PerformersID " & vbCrLf & _
"WHERE (((tblJncShows.PerformersID)=[forms]![frmBookingEditAdminStepSearch]![cmbPerformer])) " & vbCrLf & _
"ORDER BY tblBookings.BookingDate;"

have the form open with the criteria restrictions as above - but then add it to the date criteria on the record source of the opening form or just combine the date field in the code above

[Ruegen]

Attachment 14403
This would be the first form - it would search the performer and date - the button search would have the command to open the second form.

I figure if I can open the next form filtered from this form but have it's own record source then this might work.

[Ruegen]

Also I don't have a clue as to how to open a form from a query result to have the same results...

[June7]

I expect there are several ways to accomplish. One possibility is form bound to the non-editable dataset. Enter search criteria that will filter records. User selects specific booking from that limited listing. Open editable query/form with selected booking ID as filter parameter.

[Ruegen]

I expect there are several ways to accomplish. One possibility is form bound to the non-editable dataset. Enter search criteria that will filter records. User selects specific booking from that limited listing. Open editable query/form with selected booking ID as filter parameter.

Because the user is using a form where they can step from one record to the next I can't have it open from another form one by one as that would take longer for the user (not me personally).

If I can filter a query first get the results and then have the form's record source open the same results based by Booking ID then that would help

so if query brought up

123
234
345

then open the form and have the same records filtered.

[June7]

The would require code that loops through the recordset and builds a set of values that could be used to construct an SQL that uses IN clause and then set the RecordSource property of form.

Here is example of building IN criteria with items from multi-select listbox http://allenbrowne.com/ser-50.html

[Ruegen]

The would require code that loops through the recordset and builds a set of values that could be used to construct an SQL that uses IN clause and then set the RecordSource property of form.

Here is example of building IN criteria with items from multi-select listbox http://allenbrowne.com/ser-50.html

If I do that then won't the forms record source become locked? The idea is to open the form minus the performer table so that it stays unlocked...

[June7]

Wouldn't you be opening another form with it's own RecordSource without the performer table?

[Ruegen]

Wouldn't you be opening another form with it's own RecordSource without the performer table?

I'd be using the search form to open a second form that displays all records but is filtered by the first. I can't add performer table to the second because it will lock it yet I still need it filtered by the performer table. If the results are gathered before that second form is opened I was hoping to collect all the booking IDs that were filtered by the first and then ask the second form to filter. Yes the second form will have it's own record source - but I need to alter it according to the results I get from the first form without introducing the performer table.

[June7]

Right, see post #10. That would be constructing the RecordSource of second form, or at least a WHERE criteria that could be applied to its Filter property.

[Ruegen]

Right, see post #10. That would be constructing the RecordSource of second form, or at least a WHERE criteria that could be applied to its Filter property.

ok so basically you keep the record source unaltered but add the filter of booking ids to the filter property?