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?
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:
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
Set rst = Me.RecordsetClone
rst.FindFirst "TransactionID = " & lngRowID
If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
Set rst = Nothing
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.