Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    4

    Unanswered: One Form, Multiple Data Entries

    I have two tables, Customers and Transactions.

    Customers
    CustomerID CustomerName

    Transactions
    TransactionID CustomerID TransactionType Amount

    There is a one to many relationship from Customers.CustomerID to Transactions.CustomerID.

    I need to create a data entry form that allows me to create two simultaneous entries in the transaction table, one for a buyer and one for a seller. For example: John purchases something from Mary for $10. This would have to create two Transactions, one that is -$10 from John and one that is +$10 for Mary.

    I have created a form that has a dropdown for CustomerID, a dropdown for TransactionType, and a text entry box for Amount. Is it possible to add a 2nd set of dropdown boxes that identify a different CustomerID and TransactionType, and create a 2nd Transaction record?

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Is the form bound to the table Transactions?
    Have a nice day!

  3. #3
    Join Date
    May 2013
    Posts
    4
    Yes, the form is bound to the Transactions table.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Create 2 combos based on the table Customers (as explained in my answer in your former thread) but do not bound (i.e. the ControlSource property remains empty) the second combo (ComboSeller).

    2. Either using a command button or in the AfterUpdate event handler of the second combo, call this:
    Code:
    Private Sub InsertTransaction()
    
        Const c_SQL As String = "INSERT INTO Transactions ( CustomerID, TransactionType, Amount ) VALUES ( @C, '@T', @A );"
        
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim lngRowID As Long
        
        strSQL = Replace(Replace(Replace(c_SQL, "@C", Me.ComboSeller.Column(0)), "@T", "<TransactionType>"), "@A", Me.Amount.Value * -1)
        CurrentDb.Execute strSQL, dbFailOnError
        lngRowID = Me.TransactionID
        Me.Requery
        Set rst = Me.RecordsetClone
        rst.FindFirst "TransactionID = " & lngRowID
        If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
        rst.Close
        Set rst = Nothing
        
    End Sub
    I don't know how the transaction types are defined (still wondering if and why they are necessary), so replace "<TransactionType>" with the appropriate value.

    The segment of code after Me.Requery moves the current record of the form to the position it has before the requery.

    This code asserts that:
    - TransactionID is numeric,
    - CustomerID is numeric,
    - TransactionType is text,
    - Amount is numeric.
    Add (for a text value) or remove (for a numeric value) single quote (') pairs in the constant c_SQL if necessary.
    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
  •