How to carry over data from previous record

Topic

New Reply

How do I set the default value for a Combo Box based on the value selected in the previous record? e.g. when using a form to add new records in a table I want the value for some fields including Combo Boxes to default to the value entered for the same field in the previous record. The user should, of course, be able to over ride the default values.

Reply | Quote

Replies

Not sure you can do this without using a chunk of VB and referencing the RecordsetClone and finding the previous record.
I realise that you want to autopopulate with the data from the previous record, but could you not just get them to use CTRL ‘

Also, is this just for NEW Records or for ALL records?
If just for NEW Records you could possibly write code on the OnCurrent Event to test for a NEW Record ( If Me.NewRecord Then …..)
then Set Focus to each field that needs a default value
And then use Sendkeys “^'” to retrieve the data from the last record.

But you have to be careful with Sendkeys

[Actually I just tried this approach without much success so I think an alternative is needed]

Just some ideas

Reply | Quote

My Mother taught me that if I ever used SendKeys, I would surely go straight to hell.

I’ve recently had to do something similar and here’s what I did.

Create module level variables for the fields you want to carry over.

Set them in the after update event of the form

in the on insert event, populate the controls from the module level variables.

Reply | Quote

I think I understand the basic concept but I am missing something in the syntax. In the attached “strEBC_Code” is a Global variable, “Select_BCT” is the first name of the control on the form – a Combo Box.

With these additions to the events already defined to requery the second Combo Box all controls are locked and I can not enter or change variables.

Private Sub Form_AfterUpdate()

strEBC_Code = Me!Select_BCT

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

If Not IsNull(strEBC_Code) Then
Me.Select_BCT.Value = strEBC_Code
End If

End Sub

Private Sub Form_Current()

Me.Select_PCMs.Requery

End Sub

Private Sub Select_BCT_AfterUpdate()

Me.Select_PCMs.Requery

End Sub

Reply | Quote

strEBC_Code should be a module level variable and you should name it mstrEBC_Code to make this obvious in your code.

a better test for Not isnull(strEBC_Code) would be to concatenate the variable with an empty string This will tell you that it’s neither null nor an empty string.

I’m assuming that Select_PCMs uses Select_BCT as a filter

also, the current event won’t fire on a new record so you need

Code:

if strEBC_Code & ""  "" then
  Me.Select_BCT.Value = mstrEBC_Code
  call Select_BCT_AfterUpdate

now to “With these additions to the events already defined to requery the second Combo Box all controls are locked and I can not enter or change variables.”

do you mean you can’t enter values into the controls? If so I can’t see why, can you post a stripped out database with the form and just enough data for me to look at.

Reply | Quote

I actually put the code in cmdNew_Click not in the Form_BeforeUpdate event, see if this makes a difference.

Reply | Quote

Thank you for all the help. I stripped out everything but the essential tables and queries and deleted most records. The form works except that it does no carry forward the BCT from the previous record

Reply | Quote

the variables should be module level not global as they are only used by this form.

Don’t try and set the default property at runtime, it screws up in later versions of Access.

the code behind your form should look like this –

Code:

Option Compare Database
Option Explicit

Private mstrEBC_Code As String, mlngPCM_Number As Long

Private Sub Form_AfterUpdate()

    mstrEBC_Code = Me.Select_BCT ' EBC Code
    mlngPCM_Number = Me.Select_PCMs ' PCM ID

End Sub


Private Sub Form_BeforeInsert(Cancel As Integer)

    Me.Select_BCT = mstrEBC_Code
    Call Select_BCT_AfterUpdate
    Me.Select_PCMs = mlngPCM_Number
    
End Sub

Private Sub Select_BCT_AfterUpdate()

    Me.Select_PCMs.Requery
    
End Sub

Reply | Quote

I must really be missing something – I think the code below is the same as your guidance (except for the mlngPCM_Number part which is less important) but the EBC Code does not carry over from the prior record. I am using Access 2007 if that makes a difference.

Option Compare Database
Option Explicit

Private mstrEBC_Code As String, mlngPCM_Number As Long

Private Sub Form_AfterUpdate()

mstrEBC_Code = Me!Select_BCT

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.Select_BCT = mstrEBC_Code
Call Select_BCT_AfterUpdate

End Sub

Private Sub Form_Current()

Me.Select_PCMs.Requery

End Sub

Private Sub Select_BCT_AfterUpdate()

Me.Select_PCMs.Requery

End Sub

Reply | Quote

Does it populate the BCT when you select a system?

The before insert won’t fire until you enter your first bit of data on the form.

if you need it to populate when you move to a new record then you need to use a command button to goto the new record and run the code

Reply | Quote