Need Help with a Subform that won't display the source data/query 1

[MattGreer]

Please forgive my abuse of the proper terms as it's been a long long time since I've used this knowledge.

I created a database about 10 years ago in MS Access (that I've now managed to update to Access 2016) to help me with keeping track of the songs I was playing at various gigs. It's a pretty simple database, the most complicated part is taking tblSongs, which has all the info about songs (lyrics, author, key, etc.), and sending that over to tblEvents, which links back to tblSongs through a linking table called tblHistLink. If I remember my reasoning correctly, there's a many-to-may relationship between tblSongs and tblEvents, so that's why I needed the linking table.

I have a form, called frmEventInfo, that I use to record the song set I selected in tblSongs into tblEvents.

When I open frmEventInfo, there's a few fields to record the date, category (style of gig), location, etc. Then there's a subform that should display the results of a query I set up called qryService. In tblSongs, there's a checkbox to indicate that a song is "Selected". All qryService does is look at tblSongs and display only the songs where "Selected" is "Yes" (or True).

My issue is that the subform, sfrHistory, is empty whenever I open up frmEventInfo. The Record Source is qryService.
However, if I click the button I created to "submit", the event info does get recorded correctly. It's just that I'd like to see and make sure I'm entering the right songs before I click the button, so having sfrHistory display the songs while I'm entering the other data would be really helpful.

I tried DoCmd.sfrHistory.Requery, attached to a button on the main form, but that didn't work.

I'm sorry if this is a convoluted description. I'd really appreciate your help! Let me know what isn't clear and I'll be happy to explain more.

I'll be back online tomorrow afternoon about 1:00, so forgive me if I don't reply sooner.

Thank you!!

For grins, here's the code that runs the query to add the songs and other info to the event history, in case this makes any more sense. I have to admit that I do not understand the comments. I think that was a carryover from another project...

Private Sub cmdAddEventSongs_Click()
On Error GoTo Err_cmdAddEventSongs_Click

    Dim stDocName As String
    Dim intResponse As Integer

'Prevent errors from when you save the edited expanded glossary
'Otherwise you will lose your edits to the 'code' window if you
'make changes to it and then change the expanded glossary
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

    
'Turn the warnings off
    DoCmd.SetWarnings False

    stDocName = "qryAddEventSongs"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    intResponse = MsgBox("Record Addition Successful!" & Chr$(13) & Chr$(10) & _
        "Would you like to deselect the current SongList?", vbYesNo)
        
    If intResponse = vbYes Then
        DoCmd.OpenQuery "qryResetSelected"
        DoCmd.OpenQuery "qryResetOrder"
        DoCmd.Requery
        DoCmd.GoToRecord , , acLast
    End If

'Turn the warnings back on
    DoCmd.SetWarnings True

Exit_cmdAddEventSongs_Click:
    Exit Sub

Err_cmdAddEventSongs_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEventSongs_Click
    
End Sub

Thanks!!


Matt

 

[dhookom]

We don't know what is going on behind the curtains. Can you share the SQL views of your queries?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

OK, I'm not sure exactly what you need so you're going to get everything I can think of that might pertain to the issue. Sorry if this goes on too long. I really appreciate whatever help you can provide.

Here's the database design:

When the database opens, you get this form:

When you click the "Record Event Information", here's the code:

Private Sub cmdEventInfo_Click()
On Error GoTo Err_cmdEventInfo_Click

    stDocName = "frmEventInfo"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdEventInfo_Click:
    Exit Sub

Err_cmdEventInfo_Click:
    MsgBox Err.Description
    Resume Exit_cmdEventInfo_Click

End Sub

So that opens up this form, frmEventInfo:

Private Sub Form_Open(Cancel As Integer)

    OrderByOn = True
    DoCmd.GoToRecord , , acNewRec
    Me.Date = Now()
     
End Sub

The source data for the main form is the table "tblEvents", which is where the data, once entered, should go.

The subform properties (under "History Details") are as follows:

Record Source: qryService (this picks all songs from tblSongs that have the "Selected" checkbox checked

SELECT tblSongs.SongID, tblSongs.Title, tblSongs.Author, tblSongs.Selected
FROM tblSongs
WHERE (((tblSongs.Selected)=Yes))
ORDER BY tblSongs.Order;

When I open the subform by itself, it shows the songs that I selected for the service.

Also, when I open the form "frmEventInfo" it creats an entry in tblEvents with no link to the songs, so it's essentially a blank entry if I don't hit the "Add Songs to History" button.

Here's the code behind "Add Songs to History". The code works. If I ignore the fact that I can't see the selected songs in sfrHistory and hit the "Add Songs to History" button, I get the correct entries in the tblEvents and tblHistLink.

I just can't see the "selected" songs in sfrHistory when I open frmEventInfo.

Private Sub cmdAddEventSongs_Click()
On Error GoTo Err_cmdAddEventSongs_Click

    Dim stDocName As String
    Dim intResponse As Integer

'Prevent errors from when you save the edited expanded glossary
'Otherwise you will lose your edits to the 'code' window if you
'make changes to it and then change the expanded glossary
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

    
'Turn the warnings off
    DoCmd.SetWarnings False

    stDocName = "qryAddEventSongs"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    intResponse = MsgBox("Record Addition Successful!" & Chr$(13) & Chr$(10) & _
        "Would you like to deselect the current SongList?", vbYesNo)
        
    If intResponse = vbYes Then
        DoCmd.OpenQuery "qryResetSelected"
        DoCmd.OpenQuery "qryResetOrder"
        DoCmd.Requery
        DoCmd.GoToRecord , , acLast
    End If

'Turn the warnings back on
    DoCmd.SetWarnings True

Exit_cmdAddEventSongs_Click:
    Exit Sub

Err_cmdAddEventSongs_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEventSongs_Click
    
End Sub

Again, I don't know what the comments mean as it's been a while and I think it pertains to another project where I may have copied the code out of.

qryAddEventSongs is as follows:

INSERT INTO tblHistLink ( SongID, EventID )
SELECT tblSongs.SongID, [Forms]![frmEventInfo]![EventID] AS Expr1
FROM tblSongs
WHERE (((tblSongs.Selected)=True));

Thanks!!


Matt

 

[dhookom]

Why would the [Selected] field be in tblSongs? Is this just used as a temporary selection when entering a new event and then reset after the SongID and EventID are appended to tblHistLink?

Typically the subform would have a record source of tblHistLink and the link master/child properties would be set to the EventID. You would have a dropdown of the SongID to select songs played during the event.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

Thanks again for your help with this. I appreciate your time so very much.

[Selected] for me, I felt that it needed to have some permanence as I go through the song list, pick songs, print out chord charts and lyrics for the other band members (I have other buttons/macros to do this), perhaps go back and edit them, and I may not do all of this in one sitting. After the performance I'll record what we did as I might throw an impromptu song in there or there may be a request.

So are you saying that I can't have the list of songs I selected show up in the subform when I go to record the event? I guess it's just a separate action where I "know" that there is a list of songs, I make sure the list is correct separately, and then I go in and just add the extra info for the particular event.



Thanks!!


Matt

 

[dhookom]

You could have a "select song" continuous form based on tblSongs that allows the user to update the value of the Selected field. Command buttons could be used to Select All, Deselect All, and some type of action like append to tblHistLink with a specific EventID.

Then, the main event form would have a subform as I described with the link properties set.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

What you say makes a lot of sense. I'm afraid I lack the technical abilities at this time to get there!

I think the part I understand least is:

Then, the main event form would have a subform as I described with the link properties set.

Do you mean then, that it would be a static form that just displays the record in tblEvents and the associated songs?

Thanks!!


Matt

 

[dhookom]

Yes, the main event form would look like frmEventInfo but the subform would have a record source of tblHistLink with a combo box bound to the SongID to add or modify the songs for the event. This type of form and subform is used in almost every application like Northwind with the main form based on Orders and the subform based on Order Details.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

Gotcha. OK, thanks for helping me resolve this.

What I wanted was a way to bring up the [Selected] list of songs, and then have a couple other fields to enter information, and then that would get appended to tblEvents. It seems like a pretty straightforward thing to do, no?

Thanks!!


Matt

 

[dhookom]

I would simply open your frmEventInfo and have a button to open the "select song" form in dialog mode. A button on the select song form would append the selected songs with the EventID to the tblHistLink. When the select song form is closed the subform on frmEventInfo is requeried.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

I use the selected idea a lot, it is a nice way to simulate a listbox with selections. I like this better than a multiselect listbox. Works nice when you have a very big list to view and want to add some features. I do not think you fully flushed out the design if you want to show the selection list and the choosen songs. Every time you go to a new master record you have to clear out the previous selections, reload the selections based on the current master record and its values in the link table. Then when you select/deselect you have to add or delete in the link table.

If you want to show which records have been selected every time you move to a new event you need to clear out the selected field, then repopulate the selected field for the new event pulling that information from the link table.

My demo is very similar but instead of events and songs it is Customers and Products. In the mainform it has the following code to clear out the selection list from the product table and then repopulate the correct selections for that customer displayed in the main form with their selections stored in the link table.

Private Sub Form_Current()
  ClearSelections
  LoadCustomerSelections Me.CustomerID
  Me.Refresh
End Sub
Public Sub ClearSelections()
  'Clear the selections from the Products Table
  Dim strSql As String
  strSql = "UPDATE Products SET Products.Selected = False"
  CurrentDb.Execute strSql
End Sub
Public Sub LoadCustomerSelections(CustomerID As Long)
  'Select the products in the product table for that customer
  Dim strSql As String
  strSql = "UPDATE Products INNER JOIN tblCustomers_Products ON Products.ProductID = tblCustomers_Products.Product_ID_FK "
  strSql = strSql & " SET Products.Selected = True WHERE tblCustomers_Products.Customer_ID_FK = " & CustomerID
  CurrentDb.Execute strSql
End Sub

Now in the sub form every time I click on the selected field I need to write to or delete from the link table

Private Sub Selected_AfterUpdate()
  If Selected Then
    InsertSelected Me.Parent.CustomerID, Me.ProductID
  Else
    RemoveSelected Me.Parent.CustomerID, Me.ProductID
  End If
  Me.Parent.subFrmCustomersProducts.Requery
End Sub
Public Sub RemoveSelected(CustomerID As Long, ProductID As Long)
  Dim strSql As String
  strSql = "Delete * from tblCustomers_Products where Customer_ID_FK = " & CustomerID & " AND Product_ID_FK = " & ProductID
  CurrentDb.Execute strSql
End Sub
Public Sub InsertSelected(CustomerID, ProductID)
  Dim strSql As String
  strSql = "INSERT INTO tblCustomers_Products (Customer_ID_FK, Product_ID_FK) "
  strSql = strSql & " Values( " & CustomerID & " , " & ProductID & ")"
  CurrentDb.Execute strSql
End Sub

I added a Frame at the top of this subform to then show All Records, those selected, or those unselected. I added a second subform to show just those selected records for that customer; however, not really needed.

I think your code is correct, but the design is not completed. The way Duane describes the subform is the traditional built in way to do it in Access. However, it has limitations especially when you have a big list and want to see what is available and what you have already selected. Also you can add some features then to sort, filter, and search the list.

See attached for demo.

 

[MattGreer]

Wow, hey, thanks MajP. I'll definitely check that out soon and get back to you.

I used to know so much more... I do miss working with databases, especially when I'm the one who benefits from them!

Thanks!!


Matt