Results 1 to 3 of 3

Thread: SQL in VBA

  1. #1
    Join Date
    Mar 2005
    Posts
    55

    Exclamation Unanswered: SQL in VBA

    I need to update a fields rowsource based on the selction in another combow box, I have placed the following code in the afterupdate event of the combo box

    Private Sub CBCostCentre_AfterUpdate()
    On Error Resume Next



    Me.RegionID.RowSource = "SELECT TCostCentres.CostCentre, TCostCentres.CM FROM TCostCentres WHERE [Forms]![PlantEditSub].CBCostCentre.Column(0) = TCostCentres.CostCentre"

    End Sub


    This does not work at all. Can someone suggest a way to display the value in column 0 of the combo box into another field

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    I just have a query in the combo box's RowSource that references the other combo box. Then in the when the AfterUpdate event fires, I just requery the other combo box. Something like:

    Private Sub CBCostCentre_AfterUpdate()
    RegionID.Requery
    End Sub

    The key is to have a reference in RegionID's RowSource to CBCostCentre. Something like:

    "SELECT TCostCentres.CostCentre, TCostCentres.CM FROM TCostCentres WHERE [Forms]![PlantEditSub]!CBCostCentre = TCostCentres.CostCentre"

    Hope that makes sense.

    C

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    .value and .rowsource are not the same thing.
    if me.RegionID is a combo and you want to set the .rowsource in a 'cascading combos' scenario then you need some concatenation -- the example SQL in your original post is all literal text and totally ignores any values on your form.

    something like this if CostCenter is

    numeric:
    .rowsouce = "SELECT aaa, bbb FROM ccc WHERE CostCenter = " & me.CBcostCenter

    string:
    .rowsouce = "SELECT aaa, bbb FROM ccc WHERE CostCenter = '" & me.CBcostCenter & "'"

    ...now it is picking up the value from the form and concatenating it into the SQL.


    Can someone suggest a way to display the value in column 0 of the combo box into another field
    i don't see the connection with the other part of your question, but it goes like this:
    me.anotherField = me.theComboBox.column(0)
    ...or simpler still if column 0 is the bound column:
    me.anotherField = me.theComboBox


    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •