Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    I actually put the code in cmdNew_Click not in the Form_BeforeUpdate event, see if this makes a difference.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    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

  9. #9
    2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •