Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Basing items in a combo box on the value of another entry in the t

Discussion in 'Information Technology' started by rg, Jul 28, 2006.

  1. rg

    rg
    Expand Collapse
    Guest

    I know I have read this somewhere, but unfortunately I can't find it again.
    I have a combo box that is populated from records in a separate table. I
    want to list only those records that match a certain criteria. This part is
    working. The problem is that the criteria is based on the value entered in
    another combo box on the form. When I am entering new records, the items in
    the combo box are based on the last record I entered, not on the current
    record that is being entered now. How can I make the form update the value
    I'm using for the criteria before it populates my second combo box?
    -rg
     
  2. Loading...

    Similar Threads Forum Date
    Aim of Debasing Oneself in Gurbani Sikh Sikhi Sikhism Nov 23, 2010
    General SikhiStore.com - UK Based Sikhi Shop offering Kakaar and other Sikh items & accessories Worldwide Hard Talk Feb 22, 2012
    Canada Must watch Omni Punjabi News items! Breaking News Feb 17, 2012
    Sikh News Punjab To Withdraw 4% Entry Tax on Several Items (Express India) Breaking News Jul 24, 2008
    Sikh News Punjab Decides To Withdraw Entry Tax on Several Items (The Economic Times) Breaking News Jul 23, 2008

  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Basing items in a combo box on the value of another entry inthe t

    Hi rg

    since you didnn't specify any data structures, here is an
    anaology for you...

    limit combobox when you enter, show all when you leave

    on the gotFocus event of the Product combobox, assign this:

    =SetProductSource(true)

    on the lostFocus event of the Product combobox, assign this:

    =SetProductSource(false)

    put this code behind the subform with the combobox -- and
    compile it before testing

    '~~~~~~~~~~~
    private function SetProductSource(pBooCriteria as boolean)

    on error goto Err_proc

    dim s as string, mSupplierID as long

    s = "SELECT ProductID, Product, PartNumber" _
    & " FROM Products "

    if pBooCriteria then
    if not isnull(me.SupplierID_controlname) then
    mSupplierID = nz(me.SupplierID_controlname)
    if mSupplierID <> 0 then
    s = s & " WHERE (SupplierID =" _
    & mSupplierID & ") "
    end if
    end if
    end if

    s = s & "ORDER BY Product;"

    debug.print s

    me.combobox_controlname.RowSource = s
    me.combobox_controlname.Requery

    exit function

    Err_proc:
    msgbox err.description,,"ERROR " & err.number & "
    SetProductSource"
    'press F8 to step through code and fix problem
    Stop
    Resume
    End function

    '~~~~~~~~



    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    rg wrote:
    > I know I have read this somewhere, but unfortunately I can't find it again.
    > I have a combo box that is populated from records in a separate table. I
    > want to list only those records that match a certain criteria. This part is
    > working. The problem is that the criteria is based on the value entered in
    > another combo box on the form. When I am entering new records, the items in
    > the combo box are based on the last record I entered, not on the current
    > record that is being entered now. How can I make the form update the value
    > I'm using for the criteria before it populates my second combo box?
    > -rg
     
  4. rg

    rg
    Expand Collapse
    Guest

    Re: Basing items in a combo box on the value of another entry in t

    Crystal,
    Thanks for your reply.
    I did not specify data structures because what I really want to know is how
    to make the selection from one combo box (which inserts the choice into a
    field in the current record) update before the code I've written to fill the
    next combo box runs. I'm getting the second combo box filled with the
    information from the previous record, not the current one. Is what you've
    posted the best way to get this info to update before I run the query for the
    second combobox?
    -rg

    "strive4peace" <"strive4peace2006 at yaho" wrote:

    > Hi rg
    >
    > since you didnn't specify any data structures, here is an
    > anaology for you...
    >
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Basing items in a combo box on the value of another entry int

    Hi,

    I wouldn't say it is the BEST way, but it does work ...

    the Requery statement rebuilds the list and the code
    replaces the RowSource...

    I like to use this because if you have a continuous form,
    all the data will show (if you are not IN the combo) even if
    they are not within the criteria specified by the current
    record since the combo is only filtered while you are in it...

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    rg wrote:
    > Crystal,
    > Thanks for your reply.
    > I did not specify data structures because what I really want to know is how
    > to make the selection from one combo box (which inserts the choice into a
    > field in the current record) update before the code I've written to fill the
    > next combo box runs. I'm getting the second combo box filled with the
    > information from the previous record, not the current one. Is what you've
    > posted the best way to get this info to update before I run the query for the
    > second combobox?
    > -rg
    >
    > "strive4peace" <"strive4peace2006 at yaho" wrote:
    >
    >
    >>Hi rg
    >>
    >>since you didnn't specify any data structures, here is an
    >>anaology for you...
    >>

    >
    >
     
  6. huskychemist

    huskychemist
    Expand Collapse
    Guest

    Re: Basing items in a combo box on the value of another entry in t

    This is similar to a question I have...so I'd like to give a little bit more
    specific example...

    Within a form, I'd like to restrict the choices for a field based on the
    value from the previous field.

    Field 1 Possible Values: Value1, Value2, Value3, and so on...

    Field 2 Possible Values: Value1A, Value1B, Value1C, Value2A, Value2B,
    Value3A, Value3B, Value3C, Value3D, and so on...

    If Value1 is chosen for Field 1, I want to only see Value1A, Value1B and
    Value1C as choices for Field 2.

    If Value2 is chosen for Field 1, I want to only see Value2A and Value2B as
    choices for Field 2.

    And so on...


    Thanks!

    Lowell






    "strive4peace" <"strive4peace2006 at yaho" wrote:

    > Hi rg
    >
    > since you didnn't specify any data structures, here is an
    > anaology for you...
    >
    > limit combobox when you enter, show all when you leave
    >
    > on the gotFocus event of the Product combobox, assign this:
    >
    > =SetProductSource(true)
    >
    > on the lostFocus event of the Product combobox, assign this:
    >
    > =SetProductSource(false)
    >
    > put this code behind the subform with the combobox -- and
    > compile it before testing
    >
    > '~~~~~~~~~~~
    > private function SetProductSource(pBooCriteria as boolean)
    >
    > on error goto Err_proc
    >
    > dim s as string, mSupplierID as long
    >
    > s = "SELECT ProductID, Product, PartNumber" _
    > & " FROM Products "
    >
    > if pBooCriteria then
    > if not isnull(me.SupplierID_controlname) then
    > mSupplierID = nz(me.SupplierID_controlname)
    > if mSupplierID <> 0 then
    > s = s & " WHERE (SupplierID =" _
    > & mSupplierID & ") "
    > end if
    > end if
    > end if
    >
    > s = s & "ORDER BY Product;"
    >
    > debug.print s
    >
    > me.combobox_controlname.RowSource = s
    > me.combobox_controlname.Requery
    >
    > exit function
    >
    > Err_proc:
    > msgbox err.description,,"ERROR " & err.number & "
    > SetProductSource"
    > 'press F8 to step through code and fix problem
    > Stop
    > Resume
    > End function
    >
    > '~~~~~~~~
    >
    >
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > rg wrote:
    > > I know I have read this somewhere, but unfortunately I can't find it again.
    > > I have a combo box that is populated from records in a separate table. I
    > > want to list only those records that match a certain criteria. This part is
    > > working. The problem is that the criteria is based on the value entered in
    > > another combo box on the form. When I am entering new records, the items in
    > > the combo box are based on the last record I entered, not on the current
    > > record that is being entered now. How can I make the form update the value
    > > I'm using for the criteria before it populates my second combo box?
    > > -rg

    >
     
  7. rg

    rg
    Expand Collapse
    Guest

    Re: Basing items in a combo box on the value of another entry in t

    I found the place where I read this before, here is the url.
    http://office.microsoft.com/en-us/assistance/HA011730581033.aspx
    -rg

    "huskychemist" wrote:

    > This is similar to a question I have...so I'd like to give a little bit more
    > specific example...
     
  8. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Basing items in a combo box on the value of another entry int

    Hi Lowell,

    I assume, then, that the source for your second combo is a
    value list...

    on the AfterUpdate (or BeforeUpdate) event of thie first combo:

    '~~~~~~~~~~~~~`
    dim mValues as string
    select case me.firstcombo_controlname
    case "firstvalue"
    mValues = _
    "'value1';'value2';'value3'"
    case "secondvalue"
    mValues = _
    "'value4';'value5';'value6'"
    case else
    mValues = _
    whatever is your default
    end select
    'clear current value
    me.second_combo_controlname = null

    'rebuild list
    me.second_combo_controlname.rowSource = mValues
    me.second_combo_controlname.requery

    '~~~~~~~~~~~~~

    I have the Value list for the second combo is a text list --
    if it is numbers, then remove the single quote delimiters

    I have also assumed the first combo return a text value
    since it is probably not bound...

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    huskychemist wrote:
    > This is similar to a question I have...so I'd like to give a little bit more
    > specific example...
    >
    > Within a form, I'd like to restrict the choices for a field based on the
    > value from the previous field.
    >
    > Field 1 Possible Values: Value1, Value2, Value3, and so on...
    >
    > Field 2 Possible Values: Value1A, Value1B, Value1C, Value2A, Value2B,
    > Value3A, Value3B, Value3C, Value3D, and so on...
    >
    > If Value1 is chosen for Field 1, I want to only see Value1A, Value1B and
    > Value1C as choices for Field 2.
    >
    > If Value2 is chosen for Field 1, I want to only see Value2A and Value2B as
    > choices for Field 2.
    >
    > And so on...
    >
    >
    > Thanks!
    >
    > Lowell
    >
    >
    >
    >
    >
    >
    > "strive4peace" <"strive4peace2006 at yaho" wrote:
    >
    >
    >>Hi rg
    >>
    >>since you didnn't specify any data structures, here is an
    >>anaology for you...
    >>
    >>limit combobox when you enter, show all when you leave
    >>
    >>on the gotFocus event of the Product combobox, assign this:
    >>
    >>=SetProductSource(true)
    >>
    >>on the lostFocus event of the Product combobox, assign this:
    >>
    >>=SetProductSource(false)
    >>
    >>put this code behind the subform with the combobox -- and
    >>compile it before testing
    >>
    >>'~~~~~~~~~~~
    >>private function SetProductSource(pBooCriteria as boolean)
    >>
    >> on error goto Err_proc
    >>
    >> dim s as string, mSupplierID as long
    >>
    >> s = "SELECT ProductID, Product, PartNumber" _
    >> & " FROM Products "
    >>
    >> if pBooCriteria then
    >> if not isnull(me.SupplierID_controlname) then
    >> mSupplierID = nz(me.SupplierID_controlname)
    >> if mSupplierID <> 0 then
    >> s = s & " WHERE (SupplierID =" _
    >> & mSupplierID & ") "
    >> end if
    >> end if
    >> end if
    >>
    >> s = s & "ORDER BY Product;"
    >>
    >> debug.print s
    >>
    >> me.combobox_controlname.RowSource = s
    >> me.combobox_controlname.Requery
    >>
    >> exit function
    >>
    >>Err_proc:
    >> msgbox err.description,,"ERROR " & err.number & "
    >>SetProductSource"
    >> 'press F8 to step through code and fix problem
    >> Stop
    >> Resume
    >>End function
    >>
    >>'~~~~~~~~
    >>
    >>
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> *
    >> Have an awesome day ;)
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >>
    >> *
    >>
    >>rg wrote:
    >>
    >>>I know I have read this somewhere, but unfortunately I can't find it again.
    >>>I have a combo box that is populated from records in a separate table. I
    >>>want to list only those records that match a certain criteria. This part is
    >>>working. The problem is that the criteria is based on the value entered in
    >>>another combo box on the form. When I am entering new records, the items in
    >>>the combo box are based on the last record I entered, not on the current
    >>>record that is being entered now. How can I make the form update the value
    >>>I'm using for the criteria before it populates my second combo box?
    >>>-rg

    >>
     

Share This Page