Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts
    I copied the logic from the Access 2007 MSDN article but I seem to have the syntax wrong - the second combo box does not show any values when I select a value in the first box.

    Event Code:
    Private Sub BCT_Selection_AfterUpdate()

    Me.Major_Process_Selection.RowSource = "SELECT [Process Description] FROM" & _
    " [Business Processes] WHERE EBC Code = " & _
    Me.BCT_Selection & _
    " ORDER BY [EBC Process ID Number]"

    Me.Major_Process_Selection = Me.Major_Process_Selection.ItemData(0)

    End Sub

    First Combo Box Row Source:
    SELECT [Business Cycles].[EBC Code], [Business Cycles].[EBC Title] FROM [Business Cycles] ORDER BY [EBC Title];

    Second Combo Box Row Source:
    SELECT [Business Processes].[EBC Code], [Business Processes].[Process Description], [Business Processes].[EBC Process ID Number] FROM [Business Processes] ORDER BY [EBC Process ID Number];

    EBC Code is the common linking field

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I'm not sure what you are doing with

    Me.Major_Process_Selection = Me.Major_Process_Selection.ItemData(0)

    BUT you need to add

    Me.Major_Process_Selection.Requery

    After you set the Record Source of the Second Combo
    Andrew

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by bvitter View Post
    Me.Major_Process_Selection.RowSource = "SELECT [Process Description] FROM" & _
    " [Business Processes] WHERE EBC Code = " & _
    Me.BCT_Selection & _
    " ORDER BY [EBC Process ID Number]"
    Any field names containing spaces must be surrounded by square brackets.
    So EBC Code should be [EBC Code]

    What datatype is EBC Code? If it is text then you need

    "Where [EBC Code]=" & chr(34) & me.BCT_Selection & chr(34)

    chr(34) is code for a double quote.
    Regards
    John



  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Andrew W View Post
    Me.Major_Process_Selection.Requery
    I am not sure, but my recollection is that a requery is automatic when you change the Row Source.

    A requery is needed if the row source is a query that uses the first combo as a parameter.
    Regards
    John



  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Absolutely true John.
    Not sure what question I was actually answering there.
    It was 2.30 am and I had been up for 18 hours so although I was theoretically awake, I am not sure my brain was.

    All refreshed now.
    Andrew

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thanks for replying so quickly and please forgive my lack on understanding of Access VB syntax. I modified the function and tried several different combinations of quotes and spacing but it still does not work. The second Combo Box finds all entries until I make a selection in the first Combo Box then it does not find any entries.

    Private Sub BCT_Selection_AfterUpdate()

    Me.Major_Process_Selection.RowSource = "SELECT [Process Description] FROM" & _
    "[Business Processes] WHERE [EBC Code]=" & _
    Chr(34) & Me.BCT_Selection & Chr(34) & _
    " ORDER BY [EBC Process ID Number]"

    Me.Major_Process_Selection = Me.Major_Process_Selection.ItemData(0)

    End Sub

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I can't see anything wrong with what you have written..I think I would need to see some sample data to work out why it does not return any rows.

    Changing the row source via VBA is not the simplest way to do this, and can be difficult to debug. (Particularly if you are not very comnfortable wth VBA.)

    The 'standard' solution is to base the second combo on a saved query that uses the first combo as a parameter. If you do this, then all you need in the After Update event of the first combo is:

    Me.Major_Process_Selection.requery

    So with the form open, and a value in the first combo, start creating a new query. Add the tables and fields you need for the second combo.
    On the criteria line, under the {EBC Code] field, right click and choose Build.

    In the Expression Builder choose (i.e. double click) Forms, thenh Loaded Forms, then the specific form you are using. Then in the list of controls on the form, find the first combo and double click that . You will see something like this, but with different words of course. click OK.
    [attachment=89845:Expressionbuilder.gif]

    Then view the results of the query. Does it look right? Go back to the form, change the value in the first combo. Back at the query switch back to Design View, then Datasheet view to refresh the list. Is it still OK?

    Once you are happy that the query works properly, save the query and set the row source of the second combo to the saved query.
    Then add the requery line of code to the after update event of the first combo. ( and remove the other code.)
    Attached Images Attached Images
    Regards
    John



  8. #8
    2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts
    John,

    Thank you, that works great and I learned something that will really need for future design efforts.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Good...I am glad that all worked.
    Regards
    John



Posting Permissions

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