Hello,
I have a record with 9 fields and one of these is an amount.
I want to split this record in two so that everything remains the same but the amount on both records is divided by 2.
Ex.
ID | DATE | AMOUNT
------------------------
1 |10/10/2006 | 100 €

>> Click on SPLIT BUTTON <<<

1 |10/10/2006 | 50 €
2 |10/10/2006 | 50 €

I know it is tough but it would really be useful to me.
Sven

Hi,

There are many ways to do this. You can use an update query and an append query and run them in sequence, or you can use VBA. Since this is in the VBA forum, how about:

Dim iID As Integer
Dim dtDate As Date
Dim cAmount As Currency
'Since I do mostly ADO, here is some code, but others can show you DAO
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String

iID = Me.ID
dtDate = Me.Date 'bad field name
cAmount = Me.Amount/2

sSQL = "SELECT * FROM MyTable WHERE [ID] = " & iID

'Open Connection to database
Set cn = New ADODB.Connection
With cn
    .ConnectionString = CurrentProject.Connection
    .CursorLocation = adUseClient
End With
cn.Open "Provider=..." 'or Access provider statement - use DAO

sSQL = "SELECT * FROM Employee WHERE [EmployeeID] = " & pEmpID
sSQLU = "SELECT * FROM Username WHERE [Username] = '" & pUserName & "'"

'Open Recordset on MtTable
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open sSQL, cn

rs("Amount") = cAmount
rs.Update

rs.AddNew
rs("Date") = dtDate
rs("Amount") = cAmount
rs.Update

rs.Close
cn.Close

Okay, that is how you would do it on a SQL Server, just use DAO for Access. And note, that in MyTable, I am sure there are other fields that relate this record to some other master record, so add that data in the rs.AddNew using the proper variables.

I hope that helps.

mmcdonal

Sorry, you can tell I copied and modified the code. Remove these two lines:

sSQL = "SELECT * FROM Employee WHERE [EmployeeID] = " & pEmpID
sSQLU = "SELECT * FROM Username WHERE [Username] = '" & pUserName & "'"



mmcdonal

Thanks alot!
I adapted the code slightly and it now works perfectly!
Sven