Requery a Combo Box on a Main Form from a the On-click of Subform

[tchew]

Key:
Tables
Queries
Main Forms
Sub Forms
Controls​

I've been reading-up on this over the internet for about, oh, five hours or so now and haven't been able to get what seems like a fairly simply thing to work.

I'd better start from the beginning:

There's a table called PartnerSchedules.

From this table is a query (let's call this PartnerSchedulesLabelled) - nothing fancy, just a few more fields matched to the original source table and an ascending sort applied to one of the columns.

From the query PartnerSchedulesLabelled is another query that simply uses a 'group by' on one of the columns to give me a set of unique entries (let's call this query PartnerSchedulesGrouped).

I've then used PartnerSchedulesGrouped as the source for a Main Form (let's call this PartnerMainForm) as I want to use the list of unique entries this query provides to be options in a combo box for the user (this is called PartnerCombo).

I've then connected a subform to this PartnerMainForm (let's call this connected subform PartnerSubForm1). PartnerSubForm1 uses as it's record source the query PartnerSchedulesLabelled mentioned earlier. The PartnerMainForm and PartnerSubForm1 are linked by a common field so that when the user selects an option from the PartnerCombo, the records displayed in PartnerSubForm1 are filtered accordingly.

Finally, I have a second subform on this Main Form that is not connected to any of the forms (let's call this PartnerSubForm2). The record source for this is that table I mentioned at the beginning of this David Foster Wallace length oddysee (PartnerSchedules). Within this PartnerSubForm2 I have added a buttons to allow the user to add and save new records to the PartnerSchedules table.

The idea behind this form is that the user can view the contents of the PartnerSchedules table using a query to present this information in alphabetical order and they can review the list in bite-sized chunks by using the filter offered by the combo box.

This is all working fine, but I'm feeling bit cocky, so I want to do something ever so slightly whizzy...

I've noticed that when the user saves a new record to the PartnerSchedules table that the new entry should really make it's way through the queries based on this table and out in to the combo box as a new option as well as being listed in the data presented by the PartnerSubForm1.

My understanding from what I have read over the internet (for what feels like weeks now) is that I need to requery the combo box somehow.

I've tried many of the ways suggested out there, but the sticking point seems to be how you go about referencing the forms and their controls and from where you do it from.

I've seen so many uses of this that now my brain kinda sounds like a meltdown during a therapy session...

Me.Parent!Control.Requery!Forms.Me.Parent.Parental.Control.Requery.Form.Forms.Forms.More.Forms.More.Parents.Requery.Requited.Control.Me.Control.Parents.Control.Me.Parents.Control.Me.Me.Me.

I'm also trying to do this task via VBA so I can hopefully include the elusive requery statement in the code I already have for my Save Button command's on click event:

Private Sub Command22_Click()
    If Not Me.Dirty Then
        MsgBox "No additions or changes made. No need to save anything"
    Else
        txtClicked = "Yes" 'SAVE button has been pressed
        DoCmd.RunCommand acCmdSaveRecord
        txtClicked = "No" 'Reset value, ready for further changes                     
    End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If txtClicked = "Yes" Then
        'SAVE button was clicked - allow update to continue.
    Else
            If Me.NewRecord Then
                MsgBox "You must press the SAVE button to save this record."
            Else 'Existing record being modified
                MsgBox "You must press the SAVE button to modify this record."
            End If
        DoCmd.CancelEvent
    End If
End Sub

Can someone put me out of my misery and help get the line I'm looking for in this code?

Here's the names of all the players involved (hopefully this is all of them):

• Main form name = PartnerMainForm
• Main form's combo box name = PartnerCombo
• Subform 1's name = PartnerSubForm1
• Subform 2's name = PartnerSubForm2
• Subform 2's Save Button Command name = Command22

Thank you in advance!

 

[John Big Booty]

Try;

Me.PartnerMainForm!PartnerCombo.Requery

This code assumes you are on a Subform of the Mainform. Alternately try;

Forms!PartnerMainForm!PartnerCombo.Requery

Which should work from anywhere.

If you've not already seen this link it is very useful for getting the correct syntax for this sort of thing.

 

[tchew]

Thanks for the reply John. I wasn't sure where to put your code, but had a guess. I decided to remove my VBA code for the Save record commands and instead focus on getting this requery command to work first. The requery code will need something to requery so I'm using the Save Record command that it's in the Access ribbon for now.

This is how I set up the VBA code quoted below. I highlighted my button in design mode and went to the button's property sheet. On the events tab I went to the [...] button and opened the code builder. This opened a new Form_PartnerSubForm2 Class Object. Here I tried the following code, each giving me the following errors...

Private Sub Command22_Click()
Me.PartnerMainForm!PartnerCombo.Requery
End Sub

Gets the following error:

Compile error:
Method or data memeber not found
​

Private Sub Command22_Click()
Forms!PartnerMainForm!PartnerCombo.Requery
End Sub

Gets the following error:

Run-time error '2465'
Microsoft Access can't find the field 'PartnerCombo' referred to in your expression.
​

What have I done wrong?

 

[John Big Booty]

Is PartnerCombo the actual combo name or simply it's label?

 

[tchew]

Thanks - I hadn't spotted that the name I gave it in the wizard was just a label.

I've use the following:

Private Sub Command2_Click()
Forms!PartnerMainForm!Combo5.Requery
End Sub

This almost works now, in that it updates the combo box list. Unfortunately though, when you choose a new option that appears in the combo box after the requery, the PartnerSubForm1 doesn't update to show records that match what was entered in the combo box.

This sounds to me like the PartnerSubForm1 will need requerying too. I've tried to do this as follows, but again I can't get it to work as I'm not sure how to reference it or if this is the right way to go about it.

Private Sub Command2_Click()
Forms!PartnerMainForm!Combo5.Requery
Forms!PartnerMainForm!PartnerSubForm1.Requery
End Sub

The subform that's not updating is named PartnerSubForm1.