Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007

    Question Unanswered: Hello!! I Just Registered!!! Please Help!


    I am pulling my hair out.

    I enter data in to a form (lets say has field1-10)
    I have a combo box (field2 of the form) which I drop down and select [COSTID] (based on a query about costs)
    lets say the combo (field2 of the form) is bound to column 1 from the drop down query.

    If the query has three fields, how do I get data (from the query drop down) into the field3 & field4 of the form based on that query?

    Ie when I select from the drop down, I want various other fields updated respectively.

    I know I could just place the control box in the form directly from the dropdown query but if I change the amounts in the query at a later date, so will the amounts in the form accordingly.

    I tried all sort of simple codes etc.

    Please help!! Dont let my nice hair dissapear or turn grey!

    Thank you soooo much!!

  2. #2
    Join Date
    Mar 2004
    it sounds like you want field2 field3 etc to be populated with values assosiated with the value selected in the combo box in field1?

    if so, try using the afterupdate property of the combobox to set the values in field2 field3 etc.

    you can do this using a macro and use the setvalue function and set the value of field 2 to dlookup ("[requird_value]", "[tablewithvalue]","[requird_value_criteria] = [forms]![form1 subform]![field1]")

    you can use this function in visual basic too.

    you might want to check that the syntax is correct for the function.

  3. #3
    Join Date
    Jan 2007
    Thanks L Plate. I will try that out once I fully understand and grasp it.

    Any further clarification would be AWESOME!!

    Thank you!

  4. #4
    Join Date
    Jan 2007
    Just to clarify, I want field2 to be updated with 2nd column of what was in the combo box, ie when I drop down I see three fields, field1 of teh combo goes into field1 in the form, no problem, but I want field2 of the drop down to go into field2 of the form.

    Hope that makes sense. Does that put a different spin on things?

  5. #5
    Join Date
    Dec 2002
    Préverenges, Switzerland
    combos and lists use a zero-based index, 0,1,2,3 etc
    something = me.mycombo.column(2)
    puts the value of the third column into something

    currently using SS 2008R2

  6. #6
    Join Date
    Jan 2007
    Thanks for all your help guys. I DID IT!!!!!!!!!!
    I am sure there is a better way but this works for me now.
    What I did was this.

    Create a form that the drop down query would be in (call this form2)
    Then created a macro that opened that form2 in hidden mode "where condition" = value from the drop down.

    Then applied the set value of control box in form1 = control box from form2
    When I now use the drop down all my values get updated automatically.

    I am SURE this is a long way around and some of you are just laughing your guts out, but it works for me!

    The only reason I did it this way, is cos the macro kept halting saying that it couldnt find the form2. So I had to open it in hidden mode.

    I could not have done this without you guys pointing me in the right direction.

    I wish I could give you beans but I am new and I dont think it will let me.

    Anyway, thank you sooooooooooooo much.

    If anyone can tell me an easier way then I would be happy to learn it.

  7. #7
    Join Date
    Oct 2002
    Leicester - UK
    you want something like this

    'Untested Code
    Private Sub textbox1_AfterUpdate()
        Set rs = CurrentDb.OpenRecordset("SELECT field2, field3 FROM table WHERE Field1 = "& textbox1.value &";")
        textbox2.Value = rs(0).Value
        textbox3.Value = rs(1).Value
    End Sub
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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