• How to carry over data from previous record

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » How to carry over data from previous record

    Author
    Topic
    WSbvitter
    AskWoody Lounger
    September 14, 2010 at 1:13 pm #471668

    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
    Viewing 8 reply threads
    Author
    Replies
    • WSAndrewKKWalker
      AskWoody Lounger
      September 14, 2010 at 3:20 pm #1244413

      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
    • WSkentg
      AskWoody Lounger
      September 14, 2010 at 9:41 pm #1244474

      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
    • WSbvitter
      AskWoody Lounger
      September 19, 2010 at 7:47 pm #1245608

      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
    • WSkentg
      AskWoody Lounger
      September 20, 2010 at 9:30 am #1245688

      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
    • WSkentg
      AskWoody Lounger
      September 20, 2010 at 10:00 pm #1245762

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

      Reply | Quote
    • WSbvitter
      AskWoody Lounger
      September 21, 2010 at 9:29 pm #1245906

      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
    • WSkentg
      AskWoody Lounger
      September 22, 2010 at 3:25 am #1245921

      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
    • WSbvitter
      AskWoody Lounger
      September 22, 2010 at 8:07 pm #1246264

      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
    • WSkentg
      AskWoody Lounger
      September 22, 2010 at 10:22 pm #1246276

      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
    Viewing 8 reply threads
    Reply To: How to carry over data from previous record

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.