Welcome to SPN

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

Sign Up Now!

Query to a combo box w/ criteria

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

  1. Tdahlman

    Tdahlman
    Expand Collapse
    Guest

    [frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
    other fields. [OrderInfoID] automatically updates into form
    [frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
    [Combo40] an option based on a query. The query need to be specific to the
    [ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
    gives me options based on all the [ModelID] entries. How do i make it only
    give me options in my combobox that relate to the ModelID?
    Thanks in advance.
    Travis
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Travis,

    actually, you want you combo's row source to be all the records, but
    only ModelID records when you are in it and may want to pick from the
    list...

    you can modify the SQL for your combobox RowSource on the GotFocus and
    LostFocus events

    here is an example you can pattern after:

    limit the combobox to specific records when it gets the focus, show all
    records when you leave it

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

    =SetRowSource(true)


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

    =SetRowSource(false)


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

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

    private function SetRowSource(pBooCriteria as boolean)

    on error goto Err_proc

    dim s as string, mRecordID as long

    s = "SELECT ModelID, Model " _
    & " FROM Stores"

    if pBooCriteria then
    mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
    if mRecordID <> 0 then
    s = s & " WHERE (ModelID =" _
    & mRecordID & ") "
    end if
    end if

    s = s & "ORDER BY Model;"

    'comment next line after everything works ok
    debug.print s

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

    Exit_Proc:
    exit function

    Err_proc:
    msgbox err.description,, _
    "ERROR " & err.number & " SetRowSource"
    'press F8 to step through code and fix problem
    'comment next line after debugged
    Stop: Resume

    resume Exit_Proc
    End function

    '~~~~~~~~


    Warm Regards,
    Crystal
    *
    :) have an awesome day :)
    *
    MVP Access
    Remote programming and Training
    strive4peace2006 at yahoo.com
    *



    Tdahlman wrote:
    > [frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
    > other fields. [OrderInfoID] automatically updates into form
    > [frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
    > [Combo40] an option based on a query. The query need to be specific to the
    > [ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
    > gives me options based on all the [ModelID] entries. How do i make it only
    > give me options in my combobox that relate to the ModelID?
    > Thanks in advance.
    > Travis
    >
     
  4. Tdahlman

    Tdahlman
    Expand Collapse
    Guest

    I typed in everything you said too, but when I click on the combo box it give
    me this message
    "The expression On Got Focus you entered as the event property setting
    produced the following error: The expression you entered has a function name
    that Microsoft Accesss can't find.
    *The expression may not result in the name of a macro, the name of a
    user-defined function, or [Event Procedure].
    *There may have been an error evaluationg the function, event, or macro."

    The if I click OK it doesn't limit my options in my combo box.

    What might I be doing wrong?

    "strive4peace" wrote:

    > Hi Travis,
    >
    > actually, you want you combo's row source to be all the records, but
    > only ModelID records when you are in it and may want to pick from the
    > list...
    >
    > you can modify the SQL for your combobox RowSource on the GotFocus and
    > LostFocus events
    >
    > here is an example you can pattern after:
    >
    > limit the combobox to specific records when it gets the focus, show all
    > records when you leave it
    >
    > on the gotFocus event of the Product combobox, assign this:
    >
    > =SetRowSource(true)
    >
    >
    > on the lostFocus event of the Product combobox, assign this:
    >
    > =SetRowSource(false)
    >
    >
    > put this code behind the form/subform with the combobox -- and compile
    > it before testing
    >
    > '~~~~~~~~~~~
    >
    > private function SetRowSource(pBooCriteria as boolean)
    >
    > on error goto Err_proc
    >
    > dim s as string, mRecordID as long
    >
    > s = "SELECT ModelID, Model " _
    > & " FROM Stores"
    >
    > if pBooCriteria then
    > mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
    > if mRecordID <> 0 then
    > s = s & " WHERE (ModelID =" _
    > & mRecordID & ") "
    > end if
    > end if
    >
    > s = s & "ORDER BY Model;"
    >
    > 'comment next line after everything works ok
    > debug.print s
    >
    > me.combobox_controlname.RowSource = s
    > me.combobox_controlname.Requery
    >
    > Exit_Proc:
    > exit function
    >
    > Err_proc:
    > msgbox err.description,, _
    > "ERROR " & err.number & " SetRowSource"
    > 'press F8 to step through code and fix problem
    > 'comment next line after debugged
    > Stop: Resume
    >
    > resume Exit_Proc
    > End function
    >
    > '~~~~~~~~
    >
    >
    > Warm Regards,
    > Crystal
    > *
    > :) have an awesome day :)
    > *
    > MVP Access
    > Remote programming and Training
    > strive4peace2006 at yahoo.com
    > *
    >
    >
    >
    > Tdahlman wrote:
    > > [frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
    > > other fields. [OrderInfoID] automatically updates into form
    > > [frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
    > > [Combo40] an option based on a query. The query need to be specific to the
    > > [ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
    > > gives me options based on all the [ModelID] entries. How do i make it only
    > > give me options in my combobox that relate to the ModelID?
    > > Thanks in advance.
    > > Travis
    > >

    >
     
  5. Tdahlman

    Tdahlman
    Expand Collapse
    Guest

    How do i delete the Code that I entered. I can't find it in my visual basics.
    I want to delete it and try a new method but I can't even find it.

    "Tdahlman" wrote:

    > I typed in everything you said too, but when I click on the combo box it give
    > me this message
    > "The expression On Got Focus you entered as the event property setting
    > produced the following error: The expression you entered has a function name
    > that Microsoft Accesss can't find.
    > *The expression may not result in the name of a macro, the name of a
    > user-defined function, or [Event Procedure].
    > *There may have been an error evaluationg the function, event, or macro."
    >
    > The if I click OK it doesn't limit my options in my combo box.
    >
    > What might I be doing wrong?
    >
    > "strive4peace" wrote:
    >
    > > Hi Travis,
    > >
    > > actually, you want you combo's row source to be all the records, but
    > > only ModelID records when you are in it and may want to pick from the
    > > list...
    > >
    > > you can modify the SQL for your combobox RowSource on the GotFocus and
    > > LostFocus events
    > >
    > > here is an example you can pattern after:
    > >
    > > limit the combobox to specific records when it gets the focus, show all
    > > records when you leave it
    > >
    > > on the gotFocus event of the Product combobox, assign this:
    > >
    > > =SetRowSource(true)
    > >
    > >
    > > on the lostFocus event of the Product combobox, assign this:
    > >
    > > =SetRowSource(false)
    > >
    > >
    > > put this code behind the form/subform with the combobox -- and compile
    > > it before testing
    > >
    > > '~~~~~~~~~~~
    > >
    > > private function SetRowSource(pBooCriteria as boolean)
    > >
    > > on error goto Err_proc
    > >
    > > dim s as string, mRecordID as long
    > >
    > > s = "SELECT ModelID, Model " _
    > > & " FROM Stores"
    > >
    > > if pBooCriteria then
    > > mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
    > > if mRecordID <> 0 then
    > > s = s & " WHERE (ModelID =" _
    > > & mRecordID & ") "
    > > end if
    > > end if
    > >
    > > s = s & "ORDER BY Model;"
    > >
    > > 'comment next line after everything works ok
    > > debug.print s
    > >
    > > me.combobox_controlname.RowSource = s
    > > me.combobox_controlname.Requery
    > >
    > > Exit_Proc:
    > > exit function
    > >
    > > Err_proc:
    > > msgbox err.description,, _
    > > "ERROR " & err.number & " SetRowSource"
    > > 'press F8 to step through code and fix problem
    > > 'comment next line after debugged
    > > Stop: Resume
    > >
    > > resume Exit_Proc
    > > End function
    > >
    > > '~~~~~~~~
    > >
    > >
    > > Warm Regards,
    > > Crystal
    > > *
    > > :) have an awesome day :)
    > > *
    > > MVP Access
    > > Remote programming and Training
    > > strive4peace2006 at yahoo.com
    > > *
    > >
    > >
    > >
    > > Tdahlman wrote:
    > > > [frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
    > > > other fields. [OrderInfoID] automatically updates into form
    > > > [frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
    > > > [Combo40] an option based on a query. The query need to be specific to the
    > > > [ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
    > > > gives me options based on all the [ModelID] entries. How do i make it only
    > > > give me options in my combobox that relate to the ModelID?
    > > > Thanks in advance.
    > > > Travis
    > > >

    > >
     
  6. strive4peace

    strive4peace
    Expand Collapse
    Guest

    The code needs to be in the module behind the form where you are using it.

    go to the design view of your form

    choose View, Code from the menu

    paste the SetRowSource function in there

    and then, choose Debug, Compile from the menu


    Warm Regards,
    Crystal
    *
    :) have an awesome day :)
    *
    MVP Access
    Remote programming and Training
    strive4peace2006 at yahoo.com
    *



    Tdahlman wrote:
    > I typed in everything you said too, but when I click on the combo box it give
    > me this message
    > "The expression On Got Focus you entered as the event property setting
    > produced the following error: The expression you entered has a function name
    > that Microsoft Accesss can't find.
    > *The expression may not result in the name of a macro, the name of a
    > user-defined function, or [Event Procedure].
    > *There may have been an error evaluationg the function, event, or macro."
    >
    > The if I click OK it doesn't limit my options in my combo box.
    >
    > What might I be doing wrong?
    >
    > "strive4peace" wrote:
    >
    >> Hi Travis,
    >>
    >> actually, you want you combo's row source to be all the records, but
    >> only ModelID records when you are in it and may want to pick from the
    >> list...
    >>
    >> you can modify the SQL for your combobox RowSource on the GotFocus and
    >> LostFocus events
    >>
    >> here is an example you can pattern after:
    >>
    >> limit the combobox to specific records when it gets the focus, show all
    >> records when you leave it
    >>
    >> on the gotFocus event of the Product combobox, assign this:
    >>
    >> =SetRowSource(true)
    >>
    >>
    >> on the lostFocus event of the Product combobox, assign this:
    >>
    >> =SetRowSource(false)
    >>
    >>
    >> put this code behind the form/subform with the combobox -- and compile
    >> it before testing
    >>
    >> '~~~~~~~~~~~
    >>
    >> private function SetRowSource(pBooCriteria as boolean)
    >>
    >> on error goto Err_proc
    >>
    >> dim s as string, mRecordID as long
    >>
    >> s = "SELECT ModelID, Model " _
    >> & " FROM Stores"
    >>
    >> if pBooCriteria then
    >> mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
    >> if mRecordID <> 0 then
    >> s = s & " WHERE (ModelID =" _
    >> & mRecordID & ") "
    >> end if
    >> end if
    >>
    >> s = s & "ORDER BY Model;"
    >>
    >> 'comment next line after everything works ok
    >> debug.print s
    >>
    >> me.combobox_controlname.RowSource = s
    >> me.combobox_controlname.Requery
    >>
    >> Exit_Proc:
    >> exit function
    >>
    >> Err_proc:
    >> msgbox err.description,, _
    >> "ERROR " & err.number & " SetRowSource"
    >> 'press F8 to step through code and fix problem
    >> 'comment next line after debugged
    >> Stop: Resume
    >>
    >> resume Exit_Proc
    >> End function
    >>
    >> '~~~~~~~~
    >>
    >>
    >> Warm Regards,
    >> Crystal
    >> *
    >> :) have an awesome day :)
    >> *
    >> MVP Access
    >> Remote programming and Training
    >> strive4peace2006 at yahoo.com
    >> *
    >>
    >>
    >>
    >> Tdahlman wrote:
    >>> [frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
    >>> other fields. [OrderInfoID] automatically updates into form
    >>> [frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
    >>> [Combo40] an option based on a query. The query need to be specific to the
    >>> [ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
    >>> gives me options based on all the [ModelID] entries. How do i make it only
    >>> give me options in my combobox that relate to the ModelID?
    >>> Thanks in advance.
    >>> Travis
    >>>
     
  7. strive4peace

    strive4peace
    Expand Collapse
    Guest

    to find the function, press CTRL-F for the Find Dialog box

    find what --> SetRowSource

    search--> current project

    Warm Regards,
    Crystal
    *
    :) have an awesome day :)
    *
    MVP Access
    Remote programming and Training
    strive4peace2006 at yahoo.com
    *



    Tdahlman wrote:
    > How do i delete the Code that I entered. I can't find it in my visual basics.
    > I want to delete it and try a new method but I can't even find it.
    >
    > "Tdahlman" wrote:
    >
    >> I typed in everything you said too, but when I click on the combo box it give
    >> me this message
    >> "The expression On Got Focus you entered as the event property setting
    >> produced the following error: The expression you entered has a function name
    >> that Microsoft Accesss can't find.
    >> *The expression may not result in the name of a macro, the name of a
    >> user-defined function, or [Event Procedure].
    >> *There may have been an error evaluationg the function, event, or macro."
    >>
    >> The if I click OK it doesn't limit my options in my combo box.
    >>
    >> What might I be doing wrong?
    >>
    >> "strive4peace" wrote:
    >>
    >>> Hi Travis,
    >>>
    >>> actually, you want you combo's row source to be all the records, but
    >>> only ModelID records when you are in it and may want to pick from the
    >>> list...
    >>>
    >>> you can modify the SQL for your combobox RowSource on the GotFocus and
    >>> LostFocus events
    >>>
    >>> here is an example you can pattern after:
    >>>
    >>> limit the combobox to specific records when it gets the focus, show all
    >>> records when you leave it
    >>>
    >>> on the gotFocus event of the Product combobox, assign this:
    >>>
    >>> =SetRowSource(true)
    >>>
    >>>
    >>> on the lostFocus event of the Product combobox, assign this:
    >>>
    >>> =SetRowSource(false)
    >>>
    >>>
    >>> put this code behind the form/subform with the combobox -- and compile
    >>> it before testing
    >>>
    >>> '~~~~~~~~~~~
    >>>
    >>> private function SetRowSource(pBooCriteria as boolean)
    >>>
    >>> on error goto Err_proc
    >>>
    >>> dim s as string, mRecordID as long
    >>>
    >>> s = "SELECT ModelID, Model " _
    >>> & " FROM Stores"
    >>>
    >>> if pBooCriteria then
    >>> mRecordID = nz(forms!frm_NewQuote.ModelID_controlname )
    >>> if mRecordID <> 0 then
    >>> s = s & " WHERE (ModelID =" _
    >>> & mRecordID & ") "
    >>> end if
    >>> end if
    >>>
    >>> s = s & "ORDER BY Model;"
    >>>
    >>> 'comment next line after everything works ok
    >>> debug.print s
    >>>
    >>> me.combobox_controlname.RowSource = s
    >>> me.combobox_controlname.Requery
    >>>
    >>> Exit_Proc:
    >>> exit function
    >>>
    >>> Err_proc:
    >>> msgbox err.description,, _
    >>> "ERROR " & err.number & " SetRowSource"
    >>> 'press F8 to step through code and fix problem
    >>> 'comment next line after debugged
    >>> Stop: Resume
    >>>
    >>> resume Exit_Proc
    >>> End function
    >>>
    >>> '~~~~~~~~
    >>>
    >>>
    >>> Warm Regards,
    >>> Crystal
    >>> *
    >>> :) have an awesome day :)
    >>> *
    >>> MVP Access
    >>> Remote programming and Training
    >>> strive4peace2006 at yahoo.com
    >>> *
    >>>
    >>>
    >>>
    >>> Tdahlman wrote:
    >>>> [frm_NewQuote] auto assigns [OrderInfoID], then I select [ModelID] and a few
    >>>> other fields. [OrderInfoID] automatically updates into form
    >>>> [frm_QuoteDetails]. Now in [frm_QuoteDetails] I want to select in combo box
    >>>> [Combo40] an option based on a query. The query need to be specific to the
    >>>> [ModelID] in [frm_NewQuote]. I built a query but when I use it in my form it
    >>>> gives me options based on all the [ModelID] entries. How do i make it only
    >>>> give me options in my combobox that relate to the ModelID?
    >>>> Thanks in advance.
    >>>> Travis
    >>>>
     

Share This Page