Page 1 of 3 123 LastLast
Results 1 to 15 of 40

Combo box option depending on previous combo box value & having to draw data from diff data table

  1. #1
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22

    Combo box option depending on previous combo box value & having to draw data from diff data table

    Combo box option depending on previous combo box value & having to draw data from diff data table



    Hi guys,

    I am having this problem,

    I managed to get the 2nd Combobox (Combo16) to filer it's options based on the value selected on the 1st combobox (Combo 14) However for this two data table relationship, it is a one to one relationship. Whereby I have a table of (List of Countries) and a table of (Customers List) in various countries.

    Now the issue is with the third combo box. (Combo18). After selecting the Countries and the Customers, I want to be able to select the different sites that the customer have. The way I have structured the data table is that one customer would have one data table of sites. Because lets say I sell computers to Carrefour in Germany, Carrefour will have different shopping outlets at Berlin or other cities.

    I want to be able to only show the sites that the client that I have chosen in Combo 16.

    The code that I have used for Combo14 to Combo16 is the requery code

    =========================
    Private Sub Combo14_AfterUpdate()
    Me.Combo16.Requery


    End Sub
    ==================

    Click image for larger version. 

Name:	Database structure.jpg 
Views:	41 
Size:	68.1 KB 
ID:	27012Click image for larger version. 

Name:	Form issue.jpg 
Views:	42 
Size:	62.2 KB 
ID:	27013

    The second issue I would have is that after selecting the Sites, I would want to print the equipment list that the outlet has, like their POS system, their computers, their logistics provider. Correct me if I am wrong, this should involve a button to print the report in pdf form? But how do I only print the site record that I have selected in Combo18 ?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,571
    The report uses a query that looks at the combo.
    use the builder in the criteria to get the path correct.
    Usu,
    select * from table where country = forms!myForm!combo14

    then you can print to PDF or printer.
    Also,
    the query for the second combo also looks at the combo1

  3. #3
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    I did this code in the Row Source Query but the data still doesn't appear on Combo 18. I need the Combo16 value to filter the sites that I can select at Combo18.

    Correct me if I am wrong, but my Form is called Addition of Clients so my naming should be [Addition of Clients] right?

    [Forms]![Addition of clients]![Combo16] Or [Forms]![Addition of Clients]![Combo14]

    Click image for larger version. 

Name:	Query info.jpg 
Views:	37 
Size:	55.7 KB 
ID:	27015

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    Things that concern me:
    1) It looks like you are using a text field as PK/FK fields. (not how I would design the tables.) See this site for Primary Key Tips and here for Autonumbers.
    2) You have spaces in object names.
    3) You have special characters in object names (example: "Engine S/N"). Object names should only be letters, numbers or the underscore.


    I believe you are having problems because of your table structures. The tables "GSP1", "Thailand A Site" and "GSP3" all appear to have the same structure (fields). They should be one table, with an additional field to differentiate the data. The additional field data would be "GSP1", "Thailand A Site" or "GSP3".

    Same with the tables "Malaysia EMEPMI" and "Thailand PTT Sites". The structure is the same so combine the tables and add an additional field to differentiate the data. ("Malaysia EMEPMI" and "Thailand PTT Sites" is actually data - shouldn't be object names)


    My $0.02 ..........
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Correct me if I am wrong. The intent of the primary key is to identify the table so as to allow it to link with another table with the same criteria. Because my tables are linked via client name that was why I used text. I dont assign a number to each client as there might be potential to add clients and it would mess up the numbering system

    Mine tables "GSP1", "Thailand A Site" and "GSP3" have the same structure (fields) but different data values in the table. Like the engine model is different and the engine S/N is different with different sites. Thats my rationale for different data tables for different sites, otherwise it would be one table with many client sites and different model numbers etc.

    An example would be lets say I have Dell, HP and IBM as my vendor and I sell computer case, motherboard and keyboard. But different clients at different countries buy different type of my products.

    So for Dell USA, maybe for the state New York, I sell A model computer case, B model motherboard and C model keyboard.
    But for Dell USA, for the state of
    Los Angeles, I sell B model computer case, C model motherboard and A model keyboard.

    My issue i suspect is the combo box cannot draw data from different tables based on the previous combo box criteria of the "country" I have selected and the "client" I have selected.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    The intent of the primary key is to identify the table so as to allow it to link with another table with the same criteria.
    A PK field (one field or a composite PK) is used to have a unique record identifier. This "unique record identifier" can then be used in a field in a different table (the FK field(s)) to link the child records back to the parent record.

    Records in a relational database can be sorted and physically stored in any order, but the key field (or fields) define uniqueness for that record. This makes it easy to link this table to data in other tables.


    Microsoft Access Tables: Primary Key Tips and Techniques http://www.fmsinc.com/free/newtips/primarykey.asp
    Autonumbers--What they are NOT and What They Are http://www.utteraccess.com/wiki/index.php/Autonumbers


    Mine tables "GSP1", "Thailand A Site" and "GSP3" have the same structure (fields) but different data values in the table. Like the engine model is different and the engine S/N is different with different sites. Thats my rationale for different data tables for different sites, otherwise it would be one table with many client sites and different model numbers etc.
    The engine model and the engine SN is data. Add a field for site and you have 1 table to manage instead of 3. Then it is easy to use a combo box to filter/select specific records.


    Attached is a text file with links to normalization. I would recommend you take the time to read the links (I still re-read these links )
    I would also suggest you work through these tutorials http://www.rogersaccesslibrary.com/forum/forum46.html
    With your current design, I fear you are going to have a lot of problems....


    Good luck with your project......
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Managed to resolve the issue with combing the 3 tables into 1. But i am trying to print the report based on the comobox selection.

    I tried using the macro code method and VBA method but the macro code prints the whole report out without the combo box selection filter. My VBA code cannot work, no report is being generated.

    My VBA code


    Private Sub Form_Click()
    Dim strWhere As String




    strWhere = "[Sites] = " & Me.[Combo36].Value & " AND [Clients] = " & Me.[Combo16].Value
    Debug.Print strWhere


    DoCmd.OpenReport "SiteDetails", View:=acViewNormal, WhereCondition:=strWhere
    End Sub

    Whereas for the Macro tool, whenever i press the print button, the whole report is being generated.

    [Forms]![Addition of Clients]![Combo36]

    Anything wrong with my code? I tried googling and tried both methods but cannot work

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    If you change your VBA code to:
    Code:
    Private Sub Form_Click()
      '  Dim strWhere As String
    
      '  strWhere = "[Sites] = " & Me.[Combo36].Value & " AND [Clients] = " & Me.[Combo16].Value
      '  Debug.Print strWhere
    
      '  DoCmd.OpenReport "SiteDetails", View:=acViewNormal, WhereCondition:=strWhere 
      DoCmd.OpenReport "SiteDetails", View:=acViewNormal
    End Sub
    what happens?


    What is the record source for the report "SiteDetails"? (the SQL)
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Quote Originally Posted by ssanfu View Post
    If you change your VBA code to:
    Code:
    Private Sub Form_Click()
     '  Dim strWhere As String
    
     '  strWhere = "[Sites] = " & Me.[Combo36].Value & " AND [Clients] = " & Me.[Combo16].Value
     '  Debug.Print strWhere
    
     '  DoCmd.OpenReport "SiteDetails", View:=acViewNormal, WhereCondition:=strWhere 
      DoCmd.OpenReport "SiteDetails", View:=acViewNormal
    End Sub
    what happens?


    What is the record source for the report "SiteDetails"? (the SQL)
    The Report "SiteDetails" record source is from Table "SiteDetails" . That is the table that I have combine all the sites for the clients into one table.

    Nope, the VBA code prints the whole report still. I want it to print those site details that I have selected in the combo box.

    Eg: Combo 1: Indonesia (Country)

    Combo 2: Exxon (Client)

    Combo 3: Iraqi Platform A (Site)

    I only want to print details on Iraqi Platform A, like the engine that they have, the compressor, the power plant etc. My current table has the details of all the client sites and the related information

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    The Report "SiteDetails" record source is from Table "SiteDetails" . That is the table that I have combine all the sites for the clients into one table.
    And in that table, there are fields "Sites" and "Clients"?
    (I would be using a query, not a table.... but I don't know your table structures.)


    Nope, the VBA code prints the whole report still.
    But the report prints.


    I would advise taking the time to rename controls. In 6 months, will you know Me.[Combo36] refers to "Sites" without lots of research?
    I would have Me.cboSites instead of Me.[Combo36] and Me.cboClients instead of Me.[Combo16]


    Now for some troubleshooting:
    Q: What is the bound field for Me.[Combo36] (Me.cboSites)? What is the data type of the bound field? What is the data type in the table for "Sites"?
    Q: What is the bound field for Me.[Combo16] (Me.cboClients)? What is the data type of the bound field? What is the data type in the table for "Sites"?


    Any chance you would post your dB instead of having to play 20 questions??
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  11. #11
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Click image for larger version. 

Name:	DB Structure 1.jpg 
Views:	39 
Size:	46.8 KB 
ID:	27060

    Here is my DB relationship.


    I need the report to print only the option i choose in the combo box dropdown.

    Click image for larger version. 

Name:	Site Details Table.jpg 
Views:	40 
Size:	120.8 KB 
ID:	27061Click image for larger version. 

Name:	Additon of Clients.jpg 
Views:	40 
Size:	100.8 KB 
ID:	27062

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    So is "SiteDetails.Client" a Long or Text data type?

    is "SiteDetails.Sites" a Long or Text data type?


    And the combo boxes data types?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #13
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    https://drive.google.com/file/d/0B4ki-s9qqSLdc0k2cE8xOTk5cWM/view?usp=sharing

    I have uploaded the database. I think that is an easier way than screenshoting and beating around the bush.

    I believe the "SiteDetails.Client" & "SiteDetails.Sites" is both text

    Click image for larger version. 

Name:	site details text type.jpg 
Views:	40 
Size:	238.4 KB 
ID:	27071

  14. #14
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Click image for larger version. 

Name:	Combo box refernce issue.jpg 
Views:	33 
Size:	40.8 KB 
ID:	27230

    Hi guys,

    I have an issue with the combo box filter. What I want to do is to filter the 2nd combo box results based on the 1st combo box choice selection. This is a different issue than the earlier post. But it is the same project and I am trying to create another form based off the same data

    1st Combo box : (ComboMainParts) Main Parts List (Aka Engine, Windscreen)
    1st Data table: MainParts
    2nd Combo box: (ComboMainPartsD) Models of the Main parts List (Aka Engine Model, windscreen model)
    2nd Data table: MainPartsDetails


    I tried using the Query method to filter it based on the Combobox selection, but the 2nd combo box does not show any results.

    1) I did a after update code to change combo box 2 values when combo box 1 value changes



    Private Sub ComboMainPartsD_AfterUpdate()
    ComboMainParts.Requery
    End Sub


    2) Added a Row Source code to reference the MainPartsDetails Table and set the criteria filter

    Row Source: MainPartsDetailsQuery

    Criteria: [Forms]![MainPartsDetails]![ComboMainParts]

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    I've played around with your example dB...... I really don't like the table structures, but I have the combo boxes working.
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 5
    Last Post: 12-01-2015, 02:22 PM
  2. Replies: 1
    Last Post: 09-30-2015, 12:58 PM
  3. Replies: 7
    Last Post: 03-30-2015, 10:04 AM
  4. Replies: 2
    Last Post: 01-06-2014, 04:22 PM
  5. Display table data depending on selected data
    By swavemeisterg in forum Forms
    Replies: 7
    Last Post: 07-30-2013, 03:43 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
  •  
Tech Forums: Microsoft Office Forums