Hi all, I have a database that I have been using for years that is entirely based on macros and core functionality. I am now trying to improve its functionality and efficiency with VBA . The database records all transactions such as expenditure and deposits, loans across cash, direct debits, accounts payable, assets etc. It then queries and reports this data, process bank reconciliations, P&L etc.
I have a main form and table that records the financial transaction with:
Transactions!
ID
Date
Supplier
HowPaid
Amount
Reconciled
etc
As each transaction may be broken into several different accounts and job numbers, I use a secondary table and subform that is linked to the main transactions table by ID field in Main Table linked to TransRef in secondary table
TransDetail!
ID
TransRef
Account
Amount
JobNumber
The subform is connected to the masterform with ID:TransRef
When I populate the main form and then the child form, all works perfectly well, the record and sub form records save and I can move to the next record and repeat.
As many fields remain constant record after record, I wanted to populate the main form and the child form with the same data as was used the last time I used a particular supplier. ie, after I update Supplier field, all the other controls/ fields such as amount, Account, JobRef are auto filled based on the values the last time the same supplier was used.
Here is the code I am using on the Supplier control after update:
Private Sub Supplier_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM Expense_Form_Base WHERE [Supplier] = '" & Me![Supplier] & "' ORDER BY [Date] DESC"
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
Me![F_Amount] = rs![F_Amount]
Me![Business] = rs![Business]
Me![ExpenseSubform]![Account] = rs![Account]
Me![ExpenseSubform]![JobRef] = rs![JobRef]
MsgBox "Last similar record copied successfully."
Else
MsgBox "No similar record found."
End If
rs.Close
Set rs = Nothing
Set db = Nothing
The controls are correctly populated but when I try to move to the next record, I get the error Cant go to the specified record - The Microsoft Access Database engine cannot find a record in the table 'Transactions' with key matching fields 'TransRef"
I understand the one to many issue here but I don't understand why I can save and more to next record when I manually enter the data, but cannot when the data is populated with the VBA code.
Can someone help me please.
I have a main form and table that records the financial transaction with:
Transactions!
ID
Date
Supplier
HowPaid
Amount
Reconciled
etc
As each transaction may be broken into several different accounts and job numbers, I use a secondary table and subform that is linked to the main transactions table by ID field in Main Table linked to TransRef in secondary table
TransDetail!
ID
TransRef
Account
Amount
JobNumber
The subform is connected to the masterform with ID:TransRef
When I populate the main form and then the child form, all works perfectly well, the record and sub form records save and I can move to the next record and repeat.
As many fields remain constant record after record, I wanted to populate the main form and the child form with the same data as was used the last time I used a particular supplier. ie, after I update Supplier field, all the other controls/ fields such as amount, Account, JobRef are auto filled based on the values the last time the same supplier was used.
Here is the code I am using on the Supplier control after update:
Private Sub Supplier_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM Expense_Form_Base WHERE [Supplier] = '" & Me![Supplier] & "' ORDER BY [Date] DESC"
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
Me![F_Amount] = rs![F_Amount]
Me![Business] = rs![Business]
Me![ExpenseSubform]![Account] = rs![Account]
Me![ExpenseSubform]![JobRef] = rs![JobRef]
MsgBox "Last similar record copied successfully."
Else
MsgBox "No similar record found."
End If
rs.Close
Set rs = Nothing
Set db = Nothing
The controls are correctly populated but when I try to move to the next record, I get the error Cant go to the specified record - The Microsoft Access Database engine cannot find a record in the table 'Transactions' with key matching fields 'TransRef"
I understand the one to many issue here but I don't understand why I can save and more to next record when I manually enter the data, but cannot when the data is populated with the VBA code.
Can someone help me please.