Fantastic Jeanette. You totally answered my question.
Cheers,
Tal
"Jeanette Cunningham" wrote:
[Quoted Text] > Hi Tal,
> If there are no matches for the search terms, you could open a form (not a
> subform) to add a new entry.
> I like to keep my search forms for searching and have separate forms for
> adding/editing records.
> I find it is easier to manage the filters on the search form if I do it this
> way.
>
> To find if there are matching records, you could use the recordsetclone to
> count the records.
> If record count is <=0, you know there are no matches and then you can open
> the form to add new records.
>
> Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>
>
> "Tal" <Tal[ at ]discussions.microsoft.com> wrote in message
> news:C7ACDA35-5B36-45A8-8A6B-484B111A74A8[ at ]microsoft.com...
> > Hi all,
> >
> > Is there any way I can have a subform open up to a new record if nothing
> > is
> > found by the search criteria that is filtering the main form?
> > Here is the search code
> > Also, I realize that I have the BeforeInsert event cancelled, but that is
> > because the address component of the search form is a concatenated
> > read-only
> > expression, so it doesn't make sense to just let the user input a new
> > client
> > or group at the bottom of the datasheet.
> >
> > Many thanks,
> > Tal
> >
> >
> > Private Sub btnRefine_Click()
> > If Not IsNull(Me.txtSearch) Then
> > varWhere = varWhere & "([txtClientMainName] LIKE ""*" & Me.txtSearch &
> > "*"" OR " & _
> > "[txtClientFirstName] LIKE ""*" & Me.txtSearch & "*"" OR " & _
> > "[txtGroupName] LIKE ""*" & Me.txtSearch & "*"") AND"
> > End If
> > If Not IsNull(Me.txtRefine) Then
> > varWhere = varWhere & "([txtClientMainName] LIKE ""*" & Me.txtRefine &
> > "*"" OR " & _
> > "[txtClientFirstName] LIKE ""*" & Me.txtRefine & "*"" OR " & _
> > "[txtGroupName] LIKE ""*" & Me.txtRefine & "*"" OR " & _
> > "[compAddress] LIKE ""*" & Me.txtRefine & "*"")"
> > End If
> > lngLen = Len(varWhere)
> > If lngLen <= 0 Then
> > MsgBox "No Search Criteria Entered", vbInformation, "Nothing to
> > Search."
> > Else
> > varWhere = Left$(varWhere, lngLen)
> > End If
> > Me.Filter = varWhere
> > Me.FilterOn = True
> > End Sub
> >
> >
> > Private Sub Form_BeforeInsert(Cancel As Integer)
> > Cancel = True
> > MsgBox "You can't add new clients to the Search form", vbInformation,
> > "Permission Denied"
> > End Sub
> >
> >
>
>
>
|