Need to add to filtered form

Topic

New Reply

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.:cheers:

Thanks,

P.S. Is there an escape code that can be used to have a square bracket ignored?

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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[/Code]

Works like a charm.:bananas:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You’re welcome :).

Reply | Quote