Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2014
    Posts
    12

    Unanswered: How do you associate a query pulled into a form to a drop down on the form?

    I figure I minus well ask, as my what I tried didn't work. What I'm trying to do is to link the following query to a drop down menu.

    SELECT Table.Unit, Count(Table.Unit) AS CountOfUnit, Sum(IIf(Table.Gender="M",1,0)) AS M, Sum(IIf(Table.Gender="M",0,1)) AS F, Sum(IIf([Table].[Rank]="O6",1,0)) AS O6, Sum(IIf([Table].[Rank]="O5",1,0)) AS O5, Sum(IIf([Table].[Rank]="O4",1,0)) AS O4, Sum(IIf([Table].[Rank]="O3",1,0)) AS O3, Sum(IIf([Table].[Rank]="O2",1,0)) AS O2, Sum(IIf([Table].[Rank]="O1",1,0)) AS O1, Sum(IIf([Table].[Rank]="E9",1,0)) AS E9, Sum(IIf([Table].[Rank]="E8",1,0)) AS E8, Sum(IIf([Table].[Rank]="E7",1,0)) AS E7, Sum(IIf([Table].[Rank]="E6",1,0)) AS E6, Sum(IIf([Table].[Rank]="E5",1,0)) AS E5, Sum(IIf([Table].[Rank]="E4",1,0)) AS E4, Sum(IIf([Table].[Rank]="E3",1,0)) AS E3, Sum(IIf([Table].[Rank]="E2",1,0)) AS E2, Sum(IIf([Table].[Rank]="E1",1,0)) AS E1
    FROM Table
    GROUP BY Table.Unit;

    I was able to take what was provided by Pat P in my previous request and expand upon it to what is above. What I did afterwards was create a form on top of the query, so when I open the form, it gives me the query results.

    What I would like to do is use a drop down menu to provide the information based on the unit selected in the drop down. Therefore, I would use the drop down to select the unit and the information displayed would be the information from the query that is associated with that unit.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what you could do is:-
    have a combo or list box with units
    have a command button control alongside which toggles the filter status

    lets say the commandbutton is called cmdToggleFilter and its caption is set to "Set & Filter"
    then put some code behind the buttons on click event

    private sub cmdToggleFilter_click()
    if cmdToggleFilter.caption = "Clear &Filter"
    me.filteron = false
    cmdToggleFilter.caption = "Set &Filter"
    else
    me.filteron=true
    cmdToggleFilter.caption = "Clear &Filter"
    endif
    end sub

    Say your listbox is called libSelectUnit, pace some code behind the controls on click event
    Private Sub libSelectUnit_Click()
    me.filter = "Unit =" & libSelectUnit.value 'set the form filter to the currently selected unit in libSelectUnit
    cmdToggleFilter.caption = "Clear &Filter"
    me.filteron=true
    End Sub
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2014
    Posts
    12
    Quote Originally Posted by healdem View Post
    so what you could do is:-
    have a combo or list box with units
    have a command button control alongside which toggles the filter status

    lets say the commandbutton is called cmdToggleFilter and its caption is set to "Set & Filter"
    then put some code behind the buttons on click event

    private sub cmdToggleFilter_click()
    if cmdToggleFilter.caption = "Clear &Filter"
    me.filteron = false
    cmdToggleFilter.caption = "Set &Filter"
    else
    me.filteron=true
    cmdToggleFilter.caption = "Clear &Filter"
    endif
    end sub

    Say your listbox is called libSelectUnit, pace some code behind the controls on click event
    Private Sub libSelectUnit_Click()
    me.filter = "Unit =" & libSelectUnit.value 'set the form filter to the currently selected unit in libSelectUnit
    cmdToggleFilter.caption = "Clear &Filter"
    me.filteron=true
    End Sub
    Thank you Healdem for your response.

    I'm understanding that what you have provided is based on a set list of units. Please confirm or correct my understanding on this.

    The reason I ask is because the list of units can change at any time by increasing the units in the list. The overall endstate of the project I'm working on would be to have a database that allows for a file to be uploaded into the database, and then select the units after the fact.

    To add some clarity. A group shows up. We add them to the database, which coincidentally adds them to the list of units. I can now use the query to select the information on the unit that is new and any other units in that list.

    I thought about a search box, as it allows the user to just search for the unit, but figured a drop down might not be a bad idea. However, I'm starting to wonder if a search box would be a better option as oppose to a drop down. I figure a drop down makes it easy for some to select the unit in the available list and then filter.

    Getting back to my why I asked my question regarding my understanding of your response, would this apply to a set list, or would this allow me to continuously add to the database and the unit would then display in the list?

    Please advise, and thanks again for your response regarding this.
    Chas

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assumign you have designed yopur db with units as a separate table AND you have set the rowsource for the list box to be that table, then whenever you add a unit to the underlying tabel the list box will display that table. NOTE if the form containing the listbox is already open when you add a new unit then you will need to requery the list box. I generally place a command button alongside listboxes, or at the top right of a form that does this

    whetrehr you use a search box or list/combo box is up to you. I like the list/combo box approach as it shows the user the choices they have. however if you have hundreds or more items it can be come a pain. in that event I often adfd a search button.... but the list /combo box is aleasy present.

    implementing a search is a bit more complex than selecting from a list box

    search is good when there are god knows how many items in a list box, or if you are on a networked app that is struggling for performance reasons
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2014
    Posts
    12
    Thanks again Healdem for your response. I'm failing miserably with what you have suggested, but I'm going to keep trying.

  6. #6
    Join Date
    Jan 2014
    Posts
    12
    Okay, I'm looking at everything and it seems to be stopping at the following:

    if cmdToggleFilter.caption = "Clear &Filter"

    To make sure I'm doing this correctly, I set everything as per your example above and that is where it seems to be my issue. Any thoughts on this?

    I have another table set up with unit names for my combo/list box to pull from the table that has the list of units. Any additional thougths regarding this would be appreciated.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    whats the error message
    do you have a command button called cdtogglefilter

    oops mea culpa should read
    if cmdToggleFilter.caption = "Clear &Filter" then
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2014
    Posts
    12
    Thank you Healdem for your patience with me.

    I actually did not get an error message after doing all of this, but I did the following:

    I copied the code you put down here, in an effort to make life a little easier for me. I then made sure that both the command button and the combo box had the values referenced in the code. Then I made sure that there was a table that had the unit informatoin and set the rowsource for the list box to be that table. I'm not sure why, but I got primary key in the drop down list instead of the units though.

    For additional clarity, I did the following on the data tab for the combo box:
    Control Source = Empty
    Row Source = Table with Units
    Row Source Type = Table/Query
    Bound Column = 1 (That's what it was originally)
    Limit to List = No
    Allow Value List Edits = Yes

    I can provide more information regarding the data tab is necessary.

    After doing all of that, I opened the form to try it out, selected a number, and clicked on the command button. At that point, nothing happened. I went back and looked at the code, and the "if cmdToggleFilter.caption = "Clear &Filter"" line was red. That tells me there problem is there, I'm just not sure what the problem is.

    I hope that helps, because I'm at a loss at this point.
    Charles

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    oops mea culpa should read
    if cmdToggleFilter.caption = "Clear &Filter" then
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jan 2014
    Posts
    12
    Okay, I made that change. Thank you for putting it in red. I also had to make a change to the table that had the units listed in it, as I was getting an error message.

    Run-Time error '3464':
    Data type mismatch in criteria expression

    After making the change to the unit table, I no longer got the error message. However, the form is not showing the selected unit when I use the drop down and then click on the command button. I have four units there, and tried it on all the units.

    Out of curiosity, should I have expressions in each of the fields that are displaying? For example, instead of the Control Source being "Unit" on the form, should it be "[Unit]", and the same for the other fields in the form?

    I'm thinking something is wrong, as the form doesn't work, but playing around with different things doesn't seem to be working on my part.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    short of seeing the db doubt I can help you that much


    see here on how to post a db here
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jan 2014
    Posts
    12
    Here is the database I'm working with. There was something else I was wondering about with regarding to only showing the information that has non zero values, but I figure I can ask that after I try to figure that one out on my own.

    Thank you again for you help on this.
    Attached Files Attached Files
    Last edited by cmcgrue; 01-21-14 at 17:16.

  13. #13
    Join Date
    Jan 2014
    Posts
    12
    By the way. The information in there is test information.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to start off with if you are supplying a text value to a query you need to delimit the value

    eg
    select my, column, list from mytable where USER = 'CMCGRUE'
    if it was numeric
    select my, column, list from mytable where USER = 12345678
    if its a date literal then you need to use either ISO (yyyy/mm/dd) or the loathesopme US format (mm/dd/yyyy)
    eg:-
    select my, column, list from userLogons where user = 'CMCGRUE' and Logondate >= #2014/01/01#

    a word of caution whenever I see columns with a numer4ic suffix I tend to get a bit twitchy and wonder if the table is properly designed
    seeing 01..6, E1..E9 makes me wonder...
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Jan 2014
    Posts
    12
    Thank you Healdem for your response. I'll try out what you recommended and see what I come up with. Kind regards... Charles

Posting Permissions

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