Results 1 to 12 of 12
  1. #1
    Khermann is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2016
    Posts
    15

    Dependent Combo Box and Form Generation


    Hello everyone, bear with me, I think I have a question with a simple solution I just need someone to beat me over the head with it!

    So I am trying to construct a training database for a manufacturing plant that does a boat-load of cross training. As a result they need to be able to generate queries/forms/reports for single employees who may hold more than one job title which requires different certifications. Now that the description is out of the way here is the actual problem:

    I have a simple form with two dependent combo boxes: cboEEID and cboPositionID which are link together based on the Employees ID which is a primary key. The first looks up employees by their employee ID from my EEtbl and the second draws up the positions associated with that employee from the EEHoldsPositiontbl. This works flawlessly, no issues there. But what I want to do is be able to select the employee and one of their positions to generate a new form based on these criteria that will eventually show general information about the employee but mostly the requirements of that specific job and eventually what the employee has or has not completed. What I am struggling with at this point is filtering the query (which will feed into the form) based on the second combo box (cboPositionID). The query pulls up the records beautifully if it's only by the EEID selected in the first combo box, but will not show any records if I try to filter by both or even by the second combo box by itself. Granted I am only filtering by using the criteria spot in the query. So I'm assuming I'm missing something concerning the nature of dependent combo boxes, but I really am at a loss.

    I have gotten acquainted with some basic coding with creating the dependent combo boxes, but I'm pretty green when it comes to this so please let me know exactly what information you would like to see to try and diagnose my problem. Unfortunately I cannot upload the database itself, I work on a secure system and do not have the ability to compress the file, but I can provide the code or screen shots if you like.

    Thanks in advance for any help!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You need some vb to determine which combos to use in the filter....

    Code:
    sub btnFilter_click()
    Dim sWhere as string 
    
    if not isNull(cbo1) then sWhere = " and [field]='" & me.cbo1 & "'"
    if not isNull(cbo2) then sWhere = sWhere & " and [field]='" & me.cbo2 & "'"
    SWhere = mid(sWhere,5)
    
    me.filter= sWhere
    me.filterOn =true
    End Sub

  3. #3
    Khermann is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2016
    Posts
    15
    Okay...so where exactly do I put this code? You might have to break it down Barney style for me!

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You see I put it in a button click. The button is named btnFilter and on the click event it filters all the records shown.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    If you need your combos to be dependent on one and another, you will need to adjust the combo's RowSource property. In the RowSource property of a given dependent combo, you will include the key value of the other combo, the value selected by the User. You might find some examples if you search for Cascading Dependent Comboxes

  6. #6
    Khermann is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2016
    Posts
    15
    Quote Originally Posted by ItsMe View Post
    If you need your combos to be dependent on one and another, you will need to adjust the combo's RowSource property. In the RowSource property of a given dependent combo, you will include the key value of the other combo, the value selected by the User. You might find some examples if you search for Cascading Dependent Comboxes
    Thank you, but my dependent combo boxes are functional, it's the form generation that depends on the selection of the combo boxes that I'm struggling with.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    What is the name of your second combo? As illustrated in post #2 you will need the value of the selected record from both of the combo controls.

    In a query, you would use a WHERE clause that includes both controls. Post #2 is a method where you use the Filter property of a Form object.

    Beyond the names of your combo controls, you will want to understand which column has the Key Value. Typically, you will hide the Key and display more descriptive columns for the User. Do you know the Bound Column and column width properties? Maybe you can post the Rowsource for the second combo here.

  8. #8
    Khermann is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2016
    Posts
    15
    Quote Originally Posted by ItsMe View Post
    What is the name of your second combo? As illustrated in post #2 you will need the value of the selected record from both of the combo controls.

    In a query, you would use a WHERE clause that includes both controls. Post #2 is a method where you use the Filter property of a Form object.

    Beyond the names of your combo controls, you will want to understand which column has the Key Value. Typically, you will hide the Key and display more descriptive columns for the User. Do you know the Bound Column and column width properties? Maybe you can post the Rowsource for the second combo here.

    First Combo Box: cboEEID
    Column 1: EmployeeID
    Column 2: LastName
    Colume 3: FirstName

    Row Source:
    SELECT [EEtbl].[EmployeeID], [EEtbl].[LastName], [EEtbl].[FirstName] FROM EEtbl;
    Note: Column 1 is the Primary Key in the EEtbl

    Second Combo Box: cboPositionID
    Column 1: ID
    Column 2: EmployeeIDFK
    Column 3: PositionID
    Row Source:
    SELECT EEHoldsPositionTbl.ID, EEHoldsPositionTbl.EmployeeIDFK, EEHoldsPositionTbl.PositionID FROM EEHoldsPositionTbl WHERE (((EEHoldsPositionTbl.EmployeeIDFK)=[Forms]![Copy Of EEMainForm]![cboEEID]));

    Note: Column 1 is the Primary Key for EEHoldsPositionTbl. The combo boxes are linked by the Employee ID and state "Bound to Column 1". All my columns are visible at this time so no worries there.

    Let me know if I missed anything!

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Sorry for the late reply. I am a little busy right now. You have the correct idea to use the combo in your where clause. When you reference a control in your SQL, the SQL will consider the .Value property of the control.

    Comboboxes have a property called Bound Column. Your combos have multiple columns. You need to assign the correct column to the Bound Column property in order to get the correct .Value property. In your case, you want the .Value of your combo to recognize the 1st column.

    Bound Column = 1

    Also you may want to adjust the column widths, to hide the Key column.

    Column Widths = 0,1.5,1.5

    More info on Column Widths
    https://www.accessforums.net/showthr...579#post225579

    If you can check on these properties, I will revisit this thread in a little bit...

  10. #10
    Khermann is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2016
    Posts
    15
    Quote Originally Posted by ItsMe View Post
    Sorry for the late reply. I am a little busy right now. You have the correct idea to use the combo in your where clause. When you reference a control in your SQL, the SQL will consider the .Value property of the control.

    Comboboxes have a property called Bound Column. Your combos have multiple columns. You need to assign the correct column to the Bound Column property in order to get the correct .Value property. In your case, you want the .Value of your combo to recognize the 1st column.

    Bound Column = 1

    Also you may want to adjust the column widths, to hide the Key column.

    Column Widths = 0,1.5,1.5

    More info on Column Widths
    https://www.accessforums.net/showthr...579#post225579

    If you can check on these properties, I will revisit this thread in a little bit...
    No worries, take your time. If I am understanding you correctly, both my combo boxes should say Bound Column = 1? If this is the case, than both my combo boxes are already showing this in their respective property sheets. At this time I do want to be able to view all my columns, I'll hide the unneeded columns when I've gotten this puzzle solved, but I am aware of how to edit them.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    There are hundreds of ways to go about this. If you look at post #2 you will see the controls being used on the current form, the current form's Filter property.

    If you want to open another form and use the values selected by the user (via the combos), I would recommend going after the other form's RecordSource. This is different than adjusting a form's Filter property.

    You mentioned a query. You mentioned a form. It seems all your query needs is a WHERE clause. The following code is untested. Check to see if I have the field names correct, i.e. ID and EmployeeIDFK

    What this code will do is grab the values of the combos and add those values to a WHERE clause. The WHERE clause is added to the query object. The result is assigned to the other form's recordsource.

    Post your questions here. The following code is untested

    Code:
    Dim lngEEID As Long
    Dim lngIDFK As Long
    lngEEID = 0
    lngIDFK = 0
    
    If Me.cboEEID.Column(0) > 1 Then
    lngEEID = Me.cboEEID.Column(0)
    Else
    MsgBox "Invalid EEID Selection"
    Exit Sub
    End If
    
    If Me.cboPositionID.Column(0) > 1 Then
    lngIDFK = Me.cboPositionID.Column(0)
    Else
    MsgBox "Invalid IDFK Selection"
    Exit Sub
    End If
    
    'Open another form and apply the values of the combos
    DoCmd.OpenForm "MyOtherForm"
    Forms![MyOtherForm].RecordSource = "SELECT * MyQueryName WHERE (MyQueryName.EmployeeIDFK=" & lngIDFK & ") AND MyQueryName.ID=" & lngEEID
    
    'You can add some code here to close the current form
    'DoCmd.Close acForm, Me.Name
    Last edited by ItsMe; 08-19-2016 at 12:54 PM. Reason: Added some red fonts and adjusted combo name in If Then statement

  12. #12
    Khermann is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2016
    Posts
    15
    Quote Originally Posted by ItsMe View Post
    There are hundreds of ways to go about this. If you look at post #2 you will see the controls being used on the current form, the current form's Filter property.

    If you want to open another form and use the values selected by the user (via the combos), I would recommend going after the other form's RecordSource. This is different than adjusting a form's Filter property.

    You mentioned a query. You mentioned a form. It seems all your query needs is a WHERE clause. The following code is untested. Check to see if I have the field names correct, i.e. ID and EmployeeIDFK

    What this code will do is grab the values of the combos and add those values to a WHERE clause. The WHERE clause is added to the query object. The result is assigned to the other form's recordsource.

    Post your questions here. The following code is untested

    Code:
    Dim lngEEID As Long
    Dim lngIDFK As Long
    lngEEID = 0
    lngIDFK = 0
    
    If Me.cboEEID.Column(0) > 1 Then
    lngEEID = Me.cboEEID.Column(0)
    Else
    MsgBox "Invalid EEID Selection"
    Exit Sub
    End If
    
    If Me.cboPositionID.Column(0) > 1 Then
    lngIDFK = Me.cboPositionID.Column(0)
    Else
    MsgBox "Invalid IDFK Selection"
    Exit Sub
    End If
    
    'Open another form and apply the values of the combos
    DoCmd.OpenForm "MyOtherForm"
    Forms![MyOtherForm].RecordSource = "SELECT * MyQueryName WHERE (MyQueryName.EmployeeIDFK=" & lngIDFK & ") AND MyQueryName.ID=" & lngEEID
    
    'You can add some code here to close the current form
    'DoCmd.Close acForm, Me.Name
    Thank you for taking the time to put all this together for me. As of right now I'm just a little confused about where to put the WHERE clause. Is that something I do in SQL view? This is my hang up, I have little to no experience with actual code and where it goes beyond the most basic of things in form events. When I did make my corrections and slapped the code into SQL in my query it gave me a "Syntax error (missing operator) in query expression 'EEHoldsPositionTlb.[EmployeeIDFK] = EEtbl.[EmployeeID]

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

Similar Threads

  1. Dependent combo box
    By kiranair in forum Access
    Replies: 2
    Last Post: 06-14-2016, 02:27 AM
  2. Replies: 1
    Last Post: 04-05-2016, 09:25 AM
  3. Replies: 2
    Last Post: 05-29-2015, 09:21 AM
  4. Dependent Combo Box
    By tigers in forum Forms
    Replies: 1
    Last Post: 06-16-2009, 12:46 PM
  5. Replies: 3
    Last Post: 02-26-2009, 10:17 AM

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