Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    6

    Unanswered: Subform filter and Combo Box.

    Hello All.

    I'm new to the whole world of MC Access. I have a database with a subform.
    I need to set up the suborm to only allow access to supplies which correspond with the correct supplier (chosen by the user via a Combo box). I have 4 suppliers.

    If I use the code below, it works for 1 supplier, but how do I write the code for multiple suppliers. If I add another "WHERE s.Code = "BROWN" " I get both results listed under the supplier. I'm not to sure where to go from here...

    SELECT p.PID, p.Code
    FROM Supplier AS s INNER JOIN Product AS p ON s.SID = p.SID
    WHERE s.Code="RED"
    ORDER BY p.Code;

    Or should I be using a If Then...?

    I hope this makes sense.

    Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use:
    Code:
    WHERE s.CODE = 'RED' OR s.CODE = 'BROWN'
    Or
    Code:
    WHERE s.CODE IN ( 'RED', 'BROWN' )
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    6
    Thank you Sinndho.

    I think I need to set this up as an Event Process.
    I've created the VBA with the following code... but I is erroring.

    Private Sub cboSID_AfterUpdate()
    ' allow only supplier products to be shown
    Dim frmSub As Form_frmPurchaseOrderSubForm

    If Me.cboSID = 1 Then
    frmSub.cboProduct = Application.DLookup("Code", "Product", "SID = 1")
    ElseIf Me.cboSID = 2 Then
    frmSub.cboProduct = Application.DLookup("Code", "Product", "SID = 2")
    ElseIf Me.cboSID = 3 Then
    frmSub.cboProduct = Application.DLookup("Code", "Product", "SID = 3")
    Else
    frmSub.cboProduct = Application.DLookup("Code", "Product", "SID = 4")
    End If
    End Sub

    It is telling me "Run-time erro''91' Object variable or With block variable not set"

    highlights this "frmSub.cboProduct = Application.DLookup("Code", "Product", "SID = 1")"

    I want someone to select a supplier from the supplier combo box = cboSID
    and then from that selection, the cboProduct Combo box will only display the products relating to the suppliers code.

    I'm starting to go crazy ...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Emoon View Post
    I'm starting to go crazy ...
    Don't !

    Try:
    Code:
    Private Sub cboSID_AfterUpdate()
    
        ' allow only supplier products to be shown
        '
        Dim strCriteria As String
        
        If Eval(Me.cboSID.Value & " IN ( 1, 2, 3 )") = True Then
            strCriteria = "SID = " & Me.cboSID.Value
        Else
            strCriteria = "SID = 4"
        End If
        
        ' Note: Child_PurchaseOrder is the name of the SubForm control
        '       the SourceObject property of which is the form frmPurchaseOrderSubForm.
        '
        Me.Child_PurchaseOrder.Form.Controls("cboProduct").Value = DLookup("Code", "Product", strCriteria)
        
    End Sub
    Have a nice day!

Posting Permissions

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