Populate Sub Form with similar to last main form record data (1 Viewer)

[MHART08]

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.

 

[Gasman]

No idea TBH. Suspect it is something to do with relationships though.
However I would ask why are you bringing in a whole recordset when you only use the first record?
Look at using TOP 1

 

[arnelgp]

you need to save the Main record first, then get the New ID and save it to the subform:

Private Sub Supplier_AfterUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    'arnelgp
    Dim ID As Long
    
    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]
        
        
        'arnelgp
        'save the ID of this record so we can fetch the
        'Transfer detail for this ID
        Me.Dirty = False
        ID = Me.ID
        
        Me![ExpenseSubform]![Transfer] = ID
        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
    
End Sub

 

[MHART08]

No idea TBH. Suspect it is something to do with relationships though.
However I would ask why are you bringing in a whole recordset when you only use the first record?
Look at using TOP 1

Thank you Gasman, I understood I was calling up the record set to see the last record that was used by that p, then copy all the data from that set. So I need to do a call on the whole record set.

I'm not sure what TOP1 is but will do the research on that now.

Many thanks!!

you need to save the Main record first, then get the New ID and save it to the subform:

Private Sub Supplier_AfterUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
   
    'arnelgp
    Dim ID As Long
   
    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]
       
       
        'arnelgp
        'save the ID of this record so we can fetch the
        'Transfer detail for this ID
        Me.Dirty = False
        ID = Me.ID
       
        Me![ExpenseSubform]![Transfer] = ID
        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
   
End Sub

you need to save the Main record first, then get the New ID and save it to the subform:

Private Sub Supplier_AfterUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
   
    'arnelgp
    Dim ID As Long
   
    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]
       
       
        'arnelgp
        'save the ID of this record so we can fetch the
        'Transfer detail for this ID
        Me.Dirty = False
        ID = Me.ID
       
        Me![ExpenseSubform]![Transfer] = ID
        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
   
End Sub

Many thanks Arnelgp, I was thinking along a similar track. If I key the data into the subform manually, it creates a new record as soon as I enter on that row. What I don't understand is when I prepopulate the subform fields, the master/child relationship is not understood. I get the error even when I move from one control to another in the subform. I was thinking to push the record save with "Ondirty" or similar but I cannot.

I tried your code addition and get a run time error "you can't assign a value to that object" and the debug starts at the code line
ID = Me.ID

 

[arnelgp]

do you have ID field on the Form, add it (and set it's Visible propety to No to hide it).

 

[MHART08]

Thank you Gasman, I understood I was calling up the record set to see the last record that was used by that p, then copy all the data from that set. So I need to do a call on the whole record set.

I'm not sure what TOP1 is but will do the research on that now.

Many thanks!!


Many thanks Arnelgp, I was thinking along a similar track. If I key the data into the subform manually, it creates a new record as soon as I enter on that row. What I don't understand is when I prepopulate the subform fields, the master/child relationship is not understood. I get the error even when I move from one control to another in the subform. I was thinking to push the record save with "Ondirty" or similar but I cannot.

I tried your code addition and get a run time error "you can't assign a value to that object" and the debug starts at the code line
ID = Me.ID

Thank you Arnelgp, it work now. My mistake, I missed your first line of edited code where you declared Dim ID As Long. After including that, it runs perfectly, thank you again!!

 

[MHART08]

Thank you Gasman, I understood I was calling up the record set to see the last record that was used by that p, then copy all the data from that set. So I need to do a call on the whole record set.

I'm not sure what TOP1 is but will do the research on that now.

Many thanks!!


Many thanks Arnelgp, I was thinking along a similar track. If I key the data into the subform manually, it creates a new record as soon as I enter on that row. What I don't understand is when I prepopulate the subform fields, the master/child relationship is not understood. I get the error even when I move from one control to another in the subform. I was thinking to push the record save with "Ondirty" or similar but I cannot.

I tried your code addition and get a run time error "you can't assign a value to that object" and the debug starts at the code line
ID = Me.ID

do you have ID field on the Form, add it (and set it's Visible propety to No to hide it).

Thank you Arnelgp, it work now. My mistake, I missed your first line of edited code where you declared Dim ID As Long. After including that, it runs perfectly, thank you again!!