Forum Discussion

AmberH675's avatar
AmberH675
Copper Contributor
Sep 09, 2024

Update combobox and subform after new record added

Hello,

 

I'm having two separate issues with updating a form/subform.


First, I have a combobox on my main form, that is populated with client names. 

I have a button on this form, that opens a form where I can create new clients.

When I close this form, I want the combobox to reflect the newly created client immediately. Right now I have to close and re-open the client form before it's updated.

 

I'm using  Forms![frmMain]!cboClient.Requery on a button that closes the 'create new client' formcmdSaveClose_Click()

 

What am I doing wrong here?

 

Second, and somewhat similar...

I have a subform on this form that contains 0 to many (in datasheet format) 'order' records associated with the current client that is selected.

I have a button on my main form that opens a form and allows me to enter new 'orders' for this client. When I close this form, I want the subform to show any new records. But I have to move off this client, and back to this client, before the subform updates.

 

For this I am using 

   Forms![frmClient]![frmSub].Requery on the cmdSaveClose_Click() event.

 

Again - obviously I'm doing something wrong - but can't figure it out.


Thanks in advance! 🙂

Amber

access
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Sep 09, 2024

    AmberH675 

    The rowsource for a combo or list box does not automatically update when a record is added to the table from the rowsource is retrieved. You, the developer, can cause the combo or list box rowsource to be requeried, updating it to include the newly added records.

     

    In the command button which opens the new client form, make two changes.

     

    • Open the new  client form using syntax like this
      • DoCmd.OpenForm FormName:="frmYourFormNameGoesHere", view:=acNormal, windowmode:=acDialog
    • Add this line to the end of the procedure
      • Me.cboYourComboBoxNameGoesHere.Requery

    Opening the client form as a dialog suspends that procedure until the form thus opened closes again. Then the code in the calling procedure resumes, which causes the combo or list box to be requeried to include the newly added record.

     

    Similarly, you can requery the subform after adding neworders, by using windowmode:=acDialog in the code that opens the order form and a requery after that form closes.

    • AmberH675's avatar
      AmberH675
      Copper Contributor
      Sep 09, 2024
      Thanks George,
      For the command button - Thanks! It works.
      Before, I was using the line DoCmd.GoToRecord , , acNewRec to start on a new record, but this no longer happens. Is there a way to have it open on a blank new record?

      And for the sub form, that doesn't seem to work...

      I have the following code when the 'add order' button is clicked:

      DoCmd.OpenForm FormName:="frmHamperDt", view:=acNormal, windowmode:=acDialog, OpenArgs:=ClientID


      And I have the following code on the subForm itself
      Private Sub Form_Close()
      Forms![frmClientHampers]![frmHampersSub].Requery
      End Sub
      Should I have that requery code somewhere else?

      Thanks,
      Amber
      • George_Hepworth's avatar
        George_Hepworth
        Silver Contributor
        Sep 09, 2024

        AmberH675 

         

        "... DoCmd.GoToRecord , , acNewRec to start on a new record,"

         

        It depends on where that line of code is. As I noted, the code is suspended by opening the second form as a dialog, so this line will have to be in the second form, not the calling form. You could put it in the Open event of the second form.

         

        Again, the requery should be in the calling code, i.e. the code which opens the Order form. Try this approach. 

         

        DoCmd.OpenForm FormName:="frmHamperDt", view:=acNormal, windowmode:=acDialog, OpenArgs:=ClientID

        Me.[frmHampersSub].Form.Requery