Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Mouse51180 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    15

    Combo box selection fills in list box

    This is my first access project and I am still learning the ins and outs. I am a network administrator and not a programmer so I am using a lot of the wizards and design tools to create my project and have not entered into the VBA side of this beast yet...so please keep this in mind when offering advice.



    Setup:
    - I have two tables (tblManufacturer and tblModel). tblManufacturer has a single column with Manufacturer names. tblModel has two columns. One column is models and the second column is a foreign key column that has a relationship back to tblManufacturer.
    - I have a form called frmNewModel. This form has with a combo box and a list box.
    - The combo box pulls its data from frmManufacturer.

    What im trying to do:
    I would like for my users to select a manufacturer from the list box and only model associated with said manufacturer to be listed in the drop down box.

    What I have tried:
    I have tried to use the list box wizard and adjust the the row source property, but have not made any traction. I am unsure if I need to associate this task to a query instead, or if there is a better way of doing this lookup that I am unaware of.

    I am sure this is a simple and common task to do in Access...I just cant seem to find the right tutorial.

    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The listbox query looks at the cboManuf box to filter the data,qsModels1Manif.
    that query would be: select manuf from table where [manufacturer]=forms!myForm!cboManuf

    in the cboManuf AFTERUPDATE event ,refresh the list
    Code:
    sub cboManuf_afterupdate()
    LstModels.requery
    end sub

  3. #3
    Mouse51180 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    15
    Quote Originally Posted by ranman256 View Post
    The listbox query looks at the cboManuf box to filter the data,qsModels1Manif.
    that query would be: select manuf from table where [manufacturer]=forms!myForm!cboManuf

    in the cboManuf AFTERUPDATE event ,refresh the list
    Code:
    sub cboManuf_afterupdate()
    LstModels.requery
    end sub
    Im afraid I am not following the code here.
    This is what I have in my cboManufacturer Row Source.... but is not working. I am sure my issue is in the "forms!myForm!cboManufacturer" section, but I dont understand the code enough to adjust it correctly.
    Code:
    SELECT [tblManufacturer].[Manufacturer] FROM tblManufacturer WHERE [Manufacturer]=forms!myForm!cboManufacturer ORDER BY [Manufacturer];

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The manufacturer combobox will list ALL manufacturers, without any criteria. The criteria that you have in your SQL above goes with the model listbox - list all models for the selected manufacturer.

  5. #5
    Mouse51180 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    15
    Ok...I think im getting closer.

    This is what is currently in the lsbModels source row section in the frmModels form.
    ***(I had to recreate the form and I realize my original for was lstModels and this one is lsbModels...side questions...what is the correct normalization for this?)
    Code:
    SELECT [tblModel].[ModelID], [tblModel].[Model] FROM tblModel WHERE [Model]=Forms!frmModel!cboManufacturer ORDER BY [Model];
    the lsbModels.Requery has been added to the "After Update" event section for the cboManufacturer.

    When running, I get no errors. The manufacturer drop down looks to fill in correctly. The list box stays blank.


    The Manufacturer combo box is pulling the data correctly.
    I am not getting any errors, but I am not getting anything to return in the List Box.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Do you really want to show only models that match the manufacturer? Sadly, computers have an IQ of zero and only do what they are told!

  7. #7
    Mouse51180 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    15
    I would like to..sadly my user base has an equally impressive IQ score and I am not really sure I can put the amount of confidence in them that they can match the square peg to the square hole.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    OK, let's see what happens next: you want to list MODELS where the MANUFACTURER matches. I don't know how else to say it.

  9. #9
    Mouse51180 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    15
    Quote Originally Posted by aytee111 View Post
    OK, let's see what happens next: you want to list MODELS where the MANUFACTURER matches. I don't know how else to say it.
    Hope this might help. Here is a picture of my setup.
    My combo box currently shows Dell, but also shows Lenovo and Synology. When I select between the three manufacturers, I would like the models of the devices made by those manufacturers to show up in the list box. Right now the list box shows nothing.

    Click image for larger version. 

Name:	AccessIssue.jpg 
Views:	17 
Size:	168.4 KB 
ID:	27988

  10. #10
    Mouse51180 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    15
    Playing around I wanted to see if I could just get the list box to duplicate the combo box.
    In the list box row source...I entered =forms!frmModel!cboManufacturer. This spits out an error that says it does not exist.
    I tried it without the ' = ' sign as well and got the same error.

    Thank you for the support.

  11. #11
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Hi mouse.


    I think the issue here is that your list box says "please list all models and modelid where the manufacturer is e.g. DELL.

    BUT, none of your models is
    Called "DELL" so it's returning nothing.

    This is one possible solution I think. The others may have better.

    Create a query in design view of your two tables showing all the fields. (I'll Call it qryname)

    Then make your list box rowsource
    Select qryname.modelid, qryname.model from qryname where manufacturer = forms!frmmodel!cbomanufacturer order by model;

    Fingers crossed!!


    Sent from my iPhone using Tapatalk

  12. #12
    Mouse51180 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    15
    Quote Originally Posted by andy49 View Post
    Hi mouse.


    I think the issue here is that your list box says "please list all models and modelid where the manufacturer is e.g. DELL.

    BUT, none of your models is
    Called "DELL" so it's returning nothing.

    This is one possible solution I think. The others may have better.

    Create a query in design view of your two tables showing all the fields. (I'll Call it qryname)

    Then make your list box rowsource
    Select qryname.modelid, qryname.model from qryname where manufacturer = forms!frmmodel!cbomanufacturer order by model;

    Fingers crossed!!


    Sent from my iPhone using Tapatalk
    Im afraid that did not seem to work.
    I have attached a screenshot of the query and settings.
    I tried
    Code:
    Select qryname.modelid, qryname.model from qryname where manufacturer = forms!frmmodel!cbomanufacturer order by model;
    as well as
    Code:
    SELECT [qryName].[ModelID], [qryName].[Model] FROM qryName WHERE [Manufacturer]=Forms!frmModel!cboManufacturer ORDER BY [Model];
    to make sure it wasnt a syntax issue.
    Both code allowed me to choose the manufacturer from the drop down menu, but produced no results in the list box.
    Click image for larger version. 

Name:	Untitled.png 
Views:	14 
Size:	48.2 KB 
ID:	28010

  13. #13
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Did you use lsbmodels.requery on the on_update() event of the combobox.



    Sent from my iPhone using Tapatalk

  14. #14
    Mouse51180 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2017
    Posts
    15
    Quote Originally Posted by andy49 View Post
    Did you use lsbmodels.requery on the on_update() event of the combobox.



    Sent from my iPhone using Tapatalk
    I did...
    Click image for larger version. 

Name:	2017_03_27_11_34_09_Microsoft_Visual_Basic_for_Applications_TAMS.png 
Views:	12 
Size:	104.1 KB 
ID:	28012

  15. #15
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    What happens when you run that query without the where clause in sql design view. I presume you are getting some results?


    Sent from my iPhone using Tapatalk

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Selection from Combo Box to populate list
    By Cupragsw in forum Forms
    Replies: 3
    Last Post: 04-13-2015, 10:49 AM
  2. Multiple Selection wth List Box / Combo Box
    By rkalapura in forum Forms
    Replies: 11
    Last Post: 02-09-2013, 11:02 PM
  3. Replies: 1
    Last Post: 03-27-2012, 07:10 AM
  4. Filter List box from combo box selection
    By thart21 in forum Forms
    Replies: 3
    Last Post: 11-09-2011, 12:00 PM
  5. Checking List Box Value with Combo box selection
    By empyrean in forum Programming
    Replies: 1
    Last Post: 10-23-2009, 06:01 PM

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