Page 1 of 5 123 ... LastLast
Results 1 to 15 of 66
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select from a combo box or drop down list (2000)

    I might as well "fess up" that I know no Visual Basic, and almost no SQL. Until I was given directions to this forum, my only source of help was the help function in Access. Thanks to you kind folks, I succeeded yesterday in completing a query to choose a range of accounts and a department number - or no department number. I also managed to write a macro that resulted in the final query being exported to Excel.
    Now my supervisor wants to be able to fill the "Dept number" by selecting a Department by name from a list.

    So far I have a Table listing Departments with Department number as the primary field.
    A form called Select Departments that has a combo box based on the above table. This box shows the names, not the numbers, but the field Department number is included - just hidden.

    Now -- how do I even start to build an Event Procedure that will result in the number for the selected Department being inserted into the Query that selects the parameters for the accounts and department? Or is that even what I want to do?

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Select from a combo box or drop down list (2000)

    I don't think you have to resort to event procedures and code just yet. The usual solution for this kind of thing is to create a criteria in your query that references the combo box control on your form. It typically looks something like:
    <font color=blue>Forms![MyFormName]!ComboBoxName</font color=blue>
    Note that the bound column for the form needs to be the same kind of data you are trying to restrict, or you will get a type conversion error. Hope this makes sense and helps - if not post back.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select from a combo box or drop down list (2000)

    Thanks Wendell, I will give this a try right away.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select from a combo box or drop down list (2000)

    Typing this into criteria doesn't result in a dialog box of any kind. It just skips that field on the Table the the Query creates.
    Maybe I need to elaborate. This combo box doesn't appear to do anything at this point. The form comes up, click the arrow and you get a list of names. Choosing a name -- does nothing.
    Sorry to be such a know-nothing.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Select from a combo box or drop down list (2000)

    Guess I made an assumption about what you were trying to do that isn't true. When you select a value in the combo box, what do you want to have happen? If you are wanting to display a record on the form based on the choice you make in the combo box, the simplest way is to use the combo box wizard. The third option it gives you the option of creating a combo box that will "Find a record on my form based on the value I selected in my combo box." It creates a fairly simple VBA procedure that works on the AfterUpdate event for the combo box. If you are after a different effect, let us know.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select from a combo box or drop down list (2000)

    Okay, let me start from scratch here. What the original request from my Supervisor was. Where I've gotten with it, and what he wants now. I may be off on a totally irrelevant track.
    Original request:
    "Build a Query that will compute the MTDC for an account." ( MTDCis made up of specific expense codes)
    Did That.
    "Can I request a range of accounts?"
    Changed to a Paremeter Query by putting Between [Type the beginning Account] and [Type the ending account] in the criteria for the account number
    " We need to be able to request a specific Department as well"
    Did this with help from this site. At this point the Department number has the crieria [Type Department number] or [Type Department number] IsNull.
    Thought I was through. This does what is needed quite efficiently, and I got it to work in a Macro that exports the results into Excel.

    Now ---
    " I would rather not have to look up the account number each time. Can we have a list of Departments to choose from?"

    Based on the way Access help described a combo box, I thought that was what I needed. I have created a table of Department names and associated Department numbers. The Department number is the primary field. Then I created a Form with a combo box with two fields. Department number is the bound field, Department name is all that shows in the drop down list.

    What I need this to do is insert the department number into the Query criteria upon selection of the Department name. As long as I'm asking, it also needs to select all departments within the range if there is no Department selected.

    If this is not the way to accomplish my goal, any guidance would be appreciated.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Select from a combo box or drop down list (2000)

    <<" I would rather not have to look up the account number each time. Can we have a list of Departments to choose from?">>
    Does that mean you want to be able to select ALL accounts by not typing in the Account Number range?

    <<What I need this to do is insert the department number into the Query criteria upon selection of the Department name. As long as I'm asking, it also needs to select all departments within the range if there is no Department selected. >>
    I thought you had solved this by entering [Type Department number] or [Type Department number] IsNull in the Department criteria, No?

    It's not clear (to me at least) of what you want to do.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select from a combo box or drop down list (2000)

    <<Does that mean you want to be able to select ALL accounts by not typing in the Account Number range?>>

    No. It will always be either a single account. (Which can be accomplished by typing the same beginning and ending account), or a range of accounts.
    Sometimes we will require the entire range, regardless of what Department the account is assigned to. Sometimes we will require only the accounts, within the range, that are assigned to a specific Department.

    <<I thought you had solved this by entering [Type Department number] or [Type Department number] IsNull in the Department criteria, No?>>

    Actually, yes, and quite beautifully. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> The thing is, we have over a hundred Departments. Rather than having to drag out the list and look up the number before typing it in, my Supervisor wants to be able to select from a list -by name- of Departments and have the appropriate number inserted into the Query. The reason I cannot use Department Name directly is that the Read Only Data Base I link to for Account numbers has them assigned by Department Number only.
    Thanks for your interest.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Select from a combo box or drop down list (2000)

    OK, it does look like you need some VBA, but the wizard can do it for you. If you are simply opening a query, then you could do that manually, once you have the form with the combo box open. To automate it a bit more, you could put a command button on your form that says display the results, and the wizard will let you choose to run a query. If you are actually creating a report, it will also open a report for you - in that case the report would need to have the query with the reference to the combo box as its data source. If you really want it to be quite seamless, you could actually move the code for the command button to the event AfterUpdate for the combo box, and then each time a new value was selected in the combo box, it would open the query or report with the appropriate data.

    Things get a fair bit more complicated when you want to do a range of accounts - combo boxes don't work well with ranges unless you use two. And then someone will say they want two different ranges. Multi-select List Boxes may be a reasonable choice, but they still don't deal with ranges. You may need to resort to some sort of Like criteria, but that gets to be fairly complicated, as you are creating a query on the fly. You might also want to look at filters and how they are used - they are fairly powerful in their own right even when working with tables of queries. You have an interesting challenge.
    Wendell

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Select from a combo box or drop down list (2000)

    Am I right in assuming that you want to select a department by name and then use the corredsponding department number to do the lookup?
    If so, then you need to change the query so that you are looking at the form control (ComboBox) for the criteria, this can be done as:
    Forms![your form name]!cmbDepartment.Column(1)
    If the .Column(1) doesn't work then you will need to setup an invisible control on your form that is the Department number and reference that in the query.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select from a combo box or drop down list (2000)

    This gets me closer. Much closer. In fact, if push comes to shove, this will work. I am frustrated though. I really want the Department number to be automatically entered into the criteria for the Query. I was expecting something like the old TAKE, PUT commands from Basic. Essentially TAKE value from Here-PUT value There. Doesn't exist in Visual Basic?

  12. #12
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select from a combo box or drop down list (2000)

    This looks very similar to Wendell's original reply, which just resulted in the column being skipped. Curious, because that solution is exactly what Access help says will work. Maybe I'm just typing it wrong. Yours is a bit different, I will try it.
    <Forms![your form name]!cmbDepartment.Column(1)>
    Dummy didn't name her Combo Box, so it is called Combo0. The column that the Department number is in is dept-nbr.
    So I'm writing this as Forms![Find Departments]!Combo0.dept-nbr.
    We shall see.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select from a combo box or drop down list (2000)

    <hr>Forms![Find Departments]!Combo0.dept-nbr.<hr>
    What exactly is this supposed to represent? You can't refer to a column of a combobox as if it were a named property of that combobox. If you want a value, you have to refer to the column number for that value, and columns are a zero-based collection. So the second column in your combobox woudl be Combo0.Column(1). If you want to make your code readable, create a constant for dept_nbr (no hyphen, please, Access doesn't like them) and then substitute the constant for the number in the column index.
    Charlotte

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Select from a combo box or drop down list (2000)

    The department number should be taken from the appropriate column in the combo box - my original post presumed it was the bound value. If the combo is unbound, or if you are using other than the bound column, then you need to refer to the column number as Pat suggests. Also note that columns start with zero (0), so if the department number is hidden as the second column in your rowsource for the combo, then it is refered to as
    <font color=blue>Forms!MyFormName!MyComboBox.Column(1)</font color=blue>

    FYI, it is possible to create SQL strings in VBA and make them the source of a report or query, which is usually a bit more work and involves more code. But the concept of TAKE and PUT dealt with reading and writing files as I recall, and not retrieving data from a database structure. The equivalent of those commands still exist if you are doing sequential file I/O in VBA. And don't let your frustration get the best of you - many of us have been at this for 10 years or more, and we often assume others know most of what we do. In addition, Access is (IMHO and in that of most serious Access developers) the most powerful and complex Microsoft Office application. (I can just see all those Word and Excel gurus going ballistic about now <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>
    Wendell

  15. #15
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select from a combo box or drop down list (2000)

    Forms![Find Departments]!Combo0.dept-nbr.
    <What exactly is this supposed to represent?>
    I guess this represents complete confusion. I don't know VB. I thought I was copying Pat's solution. Except substituting the name of the column. The one I want would be column 0 per your explanation.
    < If you want to make your code readable, create a constant for dept_nbr (no hyphen, please,
    Access doesn't like them) and then substitute the constant for the number in the column index.>

    I have no idea how to do this. -- I have changed the name of the column to Department number.

    I do not know Visual Basic. I mean I REALLY REALLY do not know it. Please be patient with me.

    Thank you for your interest

Page 1 of 5 123 ... LastLast

Posting Permissions

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