Results 1 to 5 of 5
  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,192
    Thanks
    201
    Thanked 784 Times in 718 Posts

    Need to add to filtered form

    I have a filtered form {see below for Form & Design windows} for my coin collection.

    The form works fine with the exception of adding new records. When I click on the * to add a new record it does this and adds the record, however it does not pickup the value for the country which is currently displayed in the form header it just leaves it blank. Both of the fields in the header are unbound so I can execute code to set the filter as appropriate.

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Open(Cancel As Integer)
    
      Me.ddTypes = ""
      Me.ddCountry.SetFocus
      Me.ddCountry.Text = "United States"  '*** Set Default Country ***
      
    End Sub
    
    Private Sub cmdCloseForm_Click()
       DoCmd.Close
    End Sub
    
    Private Sub ddCountry_GotFocus()
       Me.ddTypes = ""
    End Sub
    
    Private Sub ddCountry_AfterUpdate()
       Me.Filter = "{Code} = '" & Me![ddCountry] & "'"   ---Edited to get rid of sq brackets at Code--
       Me.FilterOn = True
    End Sub
    
    Private Sub ddTypes_AfterUpdate()
    
         Me.Filter = "{Code} = '" & Me![ddCountry] & "'" & _
                     "and [Type] = '" & Me![ddTypes] & "'"
         Me.FilterOn = True
         
    End Sub
    I looked for an event, e.g. OnNewRecord but no luck. How would I go about getting this value assigned to the new record? I realize I could add it to the detail section and just size the window so I could scroll over to it when adding a new record but I'd like to find a more elegant solution.

    Thanks,

    P.S. Is there an escape code that can be used to have a square bracket ignored?
    Attached Images Attached Images
    Last edited by RetiredGeek; 2011-05-06 at 15:42.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I presume country is defined in the record source behind the form.
    Have you tried populating this country field from the combo box country field in the form's BeforeUpdate event?

    Or don't i understand your problem?

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,285
    Thanks
    130
    Thanked 1,153 Times in 1,062 Posts
    Do you have a control for the country for each of the records in the detail section? Even if hidden? I think that could be your solution, if then you set the value of the hidden control to be the value of the unbound country dropdown, in a BeforeInsert event.

  4. The Following User Says Thank You to ruirib For This Useful Post:

    RetiredGeek (2011-05-06)

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,192
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Ruirib,

    Thanks much!

    I placed a hidden field in the detail section then added the code:
    Code:
    Private Sub Form_BeforeInsert(Cancel As Integer)
       Me.Code = Me.ddCountry.Value
    End Sub
    Works like a charm.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,285
    Thanks
    130
    Thanked 1,153 Times in 1,062 Posts
    You're welcome .

Posting Permissions

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