Forum Discussion

12Jasper34's avatar
12Jasper34
Copper Contributor
Sep 15, 2022

Forms and Subforms syntax

I have a Form with a sub form.  For ease of this explanation, I will call the form (Form1) and the subform (Subform1)

On Subform1 I have 3 field controls, A, B, C

 I will use a Combo Box tied to a query to select Control A, B, and C data.  The Combo box is bound to control A.  This works.   I am looking for the Combo Box event syntax on the After Update event to push the Control B and C data to their respective controls.

 

Possible Code:  So, the Combo Box After Update event might look something like Control B = Combo Box.Column(1) and Control C =Combo Box.Column(2).

 

It has been a while since I have worked with syntax when referring to an SubForm control?  Do I need to include the Form1 name and the Subform1 name in the Combo box After Update event?  Refer to Possible Code above?  How would this be written?  Please show appropriate syntax.

 

Thanks,

John Schreiber

email address removed for privacy reasons

 

 

  • 12Jasper34 If your combobox is in Form1 then you need to mention Form1 name and Subform1 name. If combobox is in subform1 then just use "Me" like

    Me.ControlB=Me.ComboBox.Column(1)
    Me.ControlC=Me.ComboBox.Column(2)
  • arnel_gp's avatar
    arnel_gp
    Steel Contributor

    12Jasper34 

    why do you need to Duplicate Same info from one table to another?
    you can just write a Query that will link your subform table to your combo Table and Output
    the 2 columns (from the Lookup table).

     

    see this demo particularly Query1 which is the Recordsource of the Subform.

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    12Jasper34 If your combobox is in Form1 then you need to mention Form1 name and Subform1 name. If combobox is in subform1 then just use "Me" like

    Me.ControlB=Me.ComboBox.Column(1)
    Me.ControlC=Me.ComboBox.Column(2)
    • 12Jasper34's avatar
      12Jasper34
      Copper Contributor
      Thank you Harun24HR. The Combo Box is on subform1 and your code above does the job.
      Does the job. I have been retired for two years from a job where I used MS Access extensively, funny how much I have forgotten in those two years.

      John Schreiber
  • 12Jasper34's avatar
    12Jasper34
    Copper Contributor

    12Jasper34 

     

    arnel_gp and Harun24HR.         I am not duplicating information.  Form1 contains data about a show (Dog Show). Subform1 will contain information for different Dogs for that selected show.  The Comb Box is on the Supform1 and provides a list of Dogs that could be in the show. Control A, B, and C on subform1 will be loaded with the Dogs information from the Combo Box.  So, the Combo provides a list of eligible Dog information that will be stored in a table linked to the Show.

     

    I need the syntax to load the selected information from the Combo box columns (3) into fields A, B, and C on subform1.  The A information is easy because it is the Bound control for the Combo Box.  I need the syntax for Combo Box Columns 1 and 2 to be loaded into B and C from the After Update event of the Combo Box.  Again, the Combo Box is on Subform1.  I hope this helps.

     

    John Schreiber

    email address removed for privacy reasons

Resources