Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    15

    Unanswered: Loop through a Table and return the one or more values

    Hello,

    In my access database I have one simple table called "Transactions" like this with example data

    http://img96.imageshack.us/img96/268...1220142554.jpg

    and all that data will be pulled on specific way through one simple form called "Test form". It should all work similar to this

    http://img818.imageshack.us/img818/1...1220140354.jpg

    This is detailed explanation..

    When user clicks on combo box CUSTOMER there is After update Event that will contain loop that will search all data in Table "Transactions" and then at the end show that customer "A" has two invoices no. '1' and '3'
    In case user chooses customer "B" then subform will show result invoice no. '4' and etc.

    Is this possible, what code I should use at After update Event of combo box CUSTOMER?

    Any help is appreciated and many thanks in advance for prompt replies!

    Cheers
    Adi

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Without knowing how the form is built (a screen snapshot is not enough), it's hard to provide an ad-hoc answer. Generally speaking, you can create a dynamic query expression, create a query, change an existing query, open a Recordset, create a filter...
    Code:
    Sub Combo_Customer_AfterUpdate()
    
        Const c_SQL = SELECT * FROM Transactions WHERE Customer_Name = '@N';"
    
    '
    ' To Create the Query expression (used as a RecordSource, for instance).
    '
        Dim strSQL As String
        strSQL = Replace(c_SQL, "@N", Me.Combo_Customer.Value)
        Me.RecordSource = strSQL
    
    '
    ' To create a new Query:
    '
        Dim qdf As DAO.QueryDef
        Set qdf = CurrentDb.CreteQueryDef("QueryName") ' Change to a proper name.
        qdf.SQL = Replace(c_SQL, "@N", Me.Combo_Customer.Value)
        qdf.Close
        Set qdf = Nothing ' The new query is now ready.
    
    '
    ' To change an existing Query:
    '
        Dim qdf As DAO.QueryDef
        Set qdf = CurrentDb.QueryDefs("QueryName") ' Change to a proper name.
        qdf.SQL = Replace(c_SQL, "@N", Me.Combo_Customer.Value)
        qdf.Close
        Set qdf = Nothing ' The changed query is now ready.
    
    '
    ' To open a RecordSet
    '
        Dim rst As DAO.RecordSet
        Dim strSQL As String
        strSQL = Replace(c_SQL, "@N", Me.Combo_Customer.Value)
        Set rst = CurrentDb.OpenRecordSet(strSQL, dbOpenSnapShot) ' Read-Only RecordSet.
    
    '
    ' To create a Filter (case of a bound form).
    '
        Dim strFilter As String
        strFilter = Customer_Name = '" & Me.ComboCustomer.Value & "'"
        Me.Filter = strFilter
        Me.FilterOn = True
    
    End SuB
    Have a nice day!

  3. #3
    Join Date
    Mar 2013
    Posts
    15
    Hello Sinndho,

    Thank you very much for your reply!

    In order to understand it better I have created db based on example, please see attachment.

    Where exaactly should I place my code?

    Thank you!
    Adi
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a possible solution:
    Code:
    Private Sub CUSTOMER_NAME_AfterUpdate()
    
        Const c_SQL As String = "SELECT Transactions.CUSTOMER_NAME, Transactions.INVOICE_NUMBER " & _
                                "FROM Transactions " & _
                                "WHERE Transactions.CUSTOMER_NAME = '@N';"
    
        Dim qdf As DAO.QueryDef
        
        Set qdf = CurrentDb.QueryDefs("Query1")
        qdf.SQL = Replace(c_SQL, "@N", Me.CUSTOMER_NAME.Value)
        qdf.Close
        Set qdf = Nothing
        Me.Sub_form.Requery
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Mar 2013
    Posts
    15
    Hello,

    Thank you very much for suggestion!

    However, I have solved situation with this code

    Access Help and How-to - Microsoft Office Loop Through A Table And Return The One Or More Values - UtterAccess Discussion Forums

    Thank you!
    Adi

Posting Permissions

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