Results 1 to 14 of 14
  1. #1
    sydcomebak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    8

    Beginner - Fill a listbox based on dropdown selection (Filter)

    I am trying to create a form that filters data based on a dropdown.




    The Main table has an inventory of equipment: Name, Type, Location, etc


    If the dropdown is set to a location, I want there to be a list of equipment displayed at that location that is of a certain type depending on the list box. (one list box of printers, one of monitors, etc)


    Can someone help me to get things started?

    Click image for larger version. 

Name:	Capture_Form.PNG 
Views:	21 
Size:	12.7 KB 
ID:	26687

    Click image for larger version. 

Name:	Capture_Table.PNG 
Views:	22 
Size:	23.5 KB 
ID:	26688
    Thank you!

    -Dave

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the query of the listbox uses the combo box a filter BUT you must refresh the listbox when the combo changes.
    the listbox query is : qsList1Device
    select * from table where [device] = forms!myForm!cboBox

    then in the cbo box AFTERUPDATE event, refresh the listbox
    Code:
    sub cboBox_afterupdate()
      listbox.requery
    end sub
    BUT if you want to see ALL items in the list box with a null cbo box, you need 2 queries:
    Code:
    sub cboBox_afterupdate()
    if isnull(cboBox) then
      listbox.rowsource = "qsAll"
    else
      listbox.rowsource = "qsList1Device"
    endif 
    end sub

  3. #3
    sydcomebak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    8
    Thanks! I'll see if I can work this in and get back to you!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    sydcomebak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    8

    Worked on this a bunch this weekend, no luck.

    Please take a look at the attached DB. I'll be on YouTube watching Access tutorials for the next hour.

    Thanks!
    Attached Files Attached Files

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell us about the database --plain English, no jargon.
    What is the business involved? Who does what with what?
    My first reaction is that you have hidden entities. You refer to Zones, Managers...
    You have a 1-1 relationship between Inventory and Facilities??? We need to understand your business to set up tables and relationships.

  7. #7
    sydcomebak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    8
    Orange:

    Hey. I work IT and we have equipment all over the country. We are kinda tired of copying and pasting IP#s from our wiki that has our inventory in it.

    We get a call that something can't connect to the WiFi in 1 of the warehouses, so we need to start pinging WAPs and see if they are online.

    There can be 25 WAPs at a facility.

    Based on the facility the caller calls from, I want to list all the WAPs in the listbox. The button to PING all the WAPS is step2:

    Take all the WAP IPs and export them to a csv, open PingInfoView importing that CSV and continuous ping them.

    I have this working in Excel, but having a massive Tab with all the Inventory seemed dumb when that's what DBs are for.

    Please ask any questions you need to. Thanks!

  8. #8
    sydcomebak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    8

    Picture of Working Excel with VBA code

    I hope this gives you a better visual.

    Click image for larger version. 

Name:	PingInfoView.PNG 
Views:	13 
Size:	170.9 KB 
ID:	26708

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Are you moving to Access or some combination of Access and Excel?

    You have Facilities in different Locations.
    Each Facility has various Equipment.
    Some Equipment is associated with 0,1 or many IP address(es).

    Please fill this out a little more with Zones, Managers etc.
    Each Facility has 1 Manager, or different Managers for different Business lines?
    One Facility per Zone or many?

    Host IP---does that represent a network/server/AccessPoint???

    You have to get the tables and relationships set up to match your business requirement before you jump into Forms etc.

  10. #10
    sydcomebak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    8
    We are going purely ACCESS. The Excel was just to test out using VBA to open an outside program and input the CLI info.

    Each Facility is assigned a unique Zone.

    I have changed the Inventory:Location to Inventory:Facility

    The Inventory is messed up. Multiple IPs on different gear, sometimes the same 1 for 3 diff items. We know. Part of the plan is to update everything after this piece works.

    All I need is to make it so when the dropdown is selected, goto Table:Inventory and list all WAPs where Inventory:Facility matches the Facilities:Zone.

    So GAN should show 21 WAPs from IP range 10.4.204.31 thru 10.4.204.50.

    I was specifically instructed NOT to waste time filling in and updating the DB until I had a working model with the data I have already manually entered.

    I'm really sorry to hand you such an unfinished DB, but it's what I have.

  11. #11
    sydcomebak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    8
    I had nothing to do with building the original Wiki that houses all the IPs. I will probably be on the team that goes around and verifies all the hostnames and IPs once we have certain functions on the DB.

    It's a long process, but in order to be approved to spend the time on that, I need to show that it'll be worth it to move over to ACCESS.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I totally agree with this comment
    I was specifically instructed NOT to waste time filling in and updating the DB until I had a working model with the data I have already manually entered.
    Here a start, but it needs to be vetted with some test data.
    Click image for larger version. 

Name:	FacilityIPAddresses.jpg 
Views:	13 
Size:	51.2 KB 
ID:	26709

  13. #13
    sydcomebak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    8
    So with this, it looks like you are trying to avoid duplicate IPs, which is great, but I have to import the records I have exactly as they appear on the wiki. If I have 3 printers at 10.10.40.44, I could only assign that IP to 1 of the 3 printers as I enter the data. This means I have to stop, verify the info and then jump back into data entry.

    Believe me, I HATE that I am entering data that I know is wrong, but sometimes when it's week 2 of your employment, you just do what you are told to. =(

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    No, I'm not trying to prevent anything. I'm trying to get you to describe what you need in business terms.
    There is an error in the diagram--An Employee doesn't work at many Facilities (they can only be in 1 spot at a time).

    The model is showing 1 Piece of Equip can have Many IPAddresses.

    Bottom line, as per the person who gave you the instructions/quote from before, you build a model (pencil and paper),
    you take some test data and some test scenarios and confirm/find problem with the model. You adjust as necessary unitl your evolving model supports your requirement. Then you have a blueprint for your database design.

    Suggest you avoid the term Inventory for the moment. Let's stick with Equipment or Server or whatever makes sense to you and you business.

    So Location is gone now?

    With respect to the 3 printers--are they accessible by the same IP? Is there some specific reason you only want 1 Printer with a specific IP? I'm not in that business so you're going to have to identify the requirement.

    If you want only 1 Printer to be on that IP, that can be resolved during load (when the time comes).

    Don't confuse How to do something with WHAT to do/accomplish.

    You have 37 distinct zones and 26 distinct Zone_Numbers.
    I would separate your C_S_Z into City, State and Zip fields. You can always combine them later if necessary.
    Access, and database generally, works well 1 fact 1 field.

    DO NOT USE LOOKUPs AT THE TABLE FIELD LEVEL.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-20-2017, 10:26 AM
  2. Replies: 2
    Last Post: 06-25-2013, 08:35 AM
  3. Replies: 10
    Last Post: 06-18-2013, 02:00 PM
  4. Replies: 6
    Last Post: 11-15-2012, 02:38 PM
  5. Replies: 2
    Last Post: 08-03-2010, 10:16 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums