none
How to reference a combo box field in a record set using vba? (can't use "Me" as code run from another form)

    Question

  • How to reference a combo box field in a record set using vba?

    When clicking on the combo box in a sub form to make a selection or whatever... I using something like...

    Me.cboProductName.RowSource 'to see or save or set the sql used to define the Combo Box Drop Down choices
    Me.cboProductName.Column(0) 'the idProduct
    Me.cboProductName.Column(1) 'the ProductName field for the associated idProduct

    or maybe I use the ! bang character (topic for another discussion)

    Here is my issue...

    I have a button on the main form and when clicked I want to automatically make a different selection for each combo box in a record set using vba

    In this case I don't think I can reference the Combo Box for a particular record using Me.ComboBoxName.whatever as the code is attached to a control on another form... the main form

    Each record in the record set has a OrderNumber and an ItemNumber...
    So for each record in the record set with OrderNumber=X and ItemNumber=y
    I want to change the ProductName and associated idProduct shown in the Combo Box to whatever

    How can I with code called by a button on the main form... reference & change the value for a combo box for a specefic record on the sub form

    Will something kinda like this work?

    db.Execute ("UPDATE tOrdsProds SET tOrdsProds.cboProdName = " & newProdName & " WHERE tOrdsProds.OrdNum=" & Onum "and tOrdsProds.ItemNum = " & Inum)

    Thanks for any help!

    Tuesday, February 05, 2013 9:10 PM

Answers

  • Imb, thanks for the help. In my example I intended to say formSub is a 'sub form' of formMain.

    How would that change the code?

    thanks again for your assistance.

    Hi Mel_3,

    In a subform you can refer to the main form through the Parent property. But I have not too much practial experience with subforms, because when I use them it is in a complete different setting. At least not in the "normal" Parent-Child relation.

    But you can read your Help, or maybe an other person can better answer this question.

    Imb.

    Wednesday, February 06, 2013 7:26 PM
  • You seem to have 2 questions.

    (1) Referencing controls on forms and subforms.  You may find this link useful

    http://access.mvps.org/access/forms/frm0031.htm

    (2) Plugging data from controls into an SQL statement

    With regard to an SQL statement that references data from controls on a form I have found it useful to use Query Designer to generate an SQL statement, initially with actual data (order number, item number, etc.) then when you get what you want running the query, paste the SQL statement into a sub or function, then edit that resulting string in the sub to replace the actual data with a variable that contains the data from a control.

    For instance  in the Query Designer create an SQL statment like "UPDATE tOrdsProds SET tOrdsProd.ProductName = "Latest Widget" WHERE tOrdsProds.OrdNum = 5722 and tOrdsProds.ItemNum =64"

    Then in your sub

    strProductName = me.cboProductName or a parent reference if on a parent form.

    lngOrdNum = me.cboOrderNumber, and etc. for ItemNum

    Then with the variables filled replace the dummy values  (5722) withe the varible names (" & lngOrdNum & ") in the strSQL string and execute it.

    You can also use the expression builder in the Query Designer to grab values from a form and doing that will help you with the syntax to access the control, but I usally find it easier to debug the way I've mentioned above.


    Thursday, February 07, 2013 6:17 AM

All replies

  • Sometimes just writing something down and walking away for a few minutes makes the light bulb go off !

    first the table linking Orders and Products knows nothing about combo box'e... that's form stuff

    So I'm guessing I can redo the UPDATE query to simply update the fields the combo box references in tOrdsProds directly by directly modifying the fields in the table... then refreshing the form to show the new values...

    Or some such... right?

    Tuesday, February 05, 2013 9:27 PM
  • Sometimes just writing something down and walking away for a few minutes makes the light bulb go off !

    first the table linking Orders and Products knows nothing about combo box'e... that's form stuff

    So I'm guessing I can redo the UPDATE query to simply update the fields the combo box references in tOrdsProds directly by directly modifying the fields in the table... then refreshing the form to show the new values...

    Or some such... right?

    Hi Mel_3,

    When you pass the calling form's name to the called form through OpenArgs, then in the called form you can use:

          Set prev_form = Forms(Me.OpenArgs).

    From here on you can refer to any control in the called form through  Me , and in the same way to any control in the calling form throuh  prev_form .

    Imb.

    Tuesday, February 05, 2013 9:54 PM
  • Hmmm... I don't think I've used OpenArgs so I wonder if you could provide a little more code for this example...

    formMain with buttonMain and field OrderNum

    formSub with fields OrderNum and ItemNum

    so how you add On Click code to buttonMain such that you could read or write to the two fields on formSub using Me.blabla

    thanks for the help !

    Wednesday, February 06, 2013 12:39 PM
  • Hmmm... I don't think I've used OpenArgs so I wonder if you could provide a little more code for this example...

    formMain with buttonMain and field OrderNum

    formSub with fields OrderNum and ItemNum

    so how you add On Click code to buttonMain such that you could read or write to the two fields on formSub using Me.blabla

    thanks for the help !

    Hi Mel_3,

    I assume formMain and formSub are independant of each other. If formSub is a SubForm of formMain other rules apply.

    In the OnClick code of buttonMain you could include:

        DoCmd.OpenForm "formSub", acNormal, , , , , "formMain"

    In the module of formSub you can declare the variable prev_form As Form. Then in the Load or Open event of formSub you can add the line

        Set prev_form = Forms(Me.OpenArgs)

    Now you can read or write (if formMain is allowed to edit) any control of formMain from formSub. For instance, in formSub you could make a command button, with in the Click event:

        MsgBox prev_form.ActiveControl 

    to see the value of the active control of the calling form.

    Just try it.

    Imb.

    Wednesday, February 06, 2013 6:31 PM
  • Imb, thanks for the help. In my example I intended to say formSub is a 'sub form' of formMain.

    How would that change the code?

    thanks again for your assistance.

    Wednesday, February 06, 2013 7:14 PM
  • Imb, thanks for the help. In my example I intended to say formSub is a 'sub form' of formMain.

    How would that change the code?

    thanks again for your assistance.

    Hi Mel_3,

    In a subform you can refer to the main form through the Parent property. But I have not too much practial experience with subforms, because when I use them it is in a complete different setting. At least not in the "normal" Parent-Child relation.

    But you can read your Help, or maybe an other person can better answer this question.

    Imb.

    Wednesday, February 06, 2013 7:26 PM
  • You seem to have 2 questions.

    (1) Referencing controls on forms and subforms.  You may find this link useful

    http://access.mvps.org/access/forms/frm0031.htm

    (2) Plugging data from controls into an SQL statement

    With regard to an SQL statement that references data from controls on a form I have found it useful to use Query Designer to generate an SQL statement, initially with actual data (order number, item number, etc.) then when you get what you want running the query, paste the SQL statement into a sub or function, then edit that resulting string in the sub to replace the actual data with a variable that contains the data from a control.

    For instance  in the Query Designer create an SQL statment like "UPDATE tOrdsProds SET tOrdsProd.ProductName = "Latest Widget" WHERE tOrdsProds.OrdNum = 5722 and tOrdsProds.ItemNum =64"

    Then in your sub

    strProductName = me.cboProductName or a parent reference if on a parent form.

    lngOrdNum = me.cboOrderNumber, and etc. for ItemNum

    Then with the variables filled replace the dummy values  (5722) withe the varible names (" & lngOrdNum & ") in the strSQL string and execute it.

    You can also use the expression builder in the Query Designer to grab values from a form and doing that will help you with the syntax to access the control, but I usally find it easier to debug the way I've mentioned above.


    Thursday, February 07, 2013 6:17 AM
  • Imb,

    Thanks again for your assistance. I will research further as you suggest.

    Once more, thank you.

    Thursday, February 07, 2013 4:18 PM