Add new record if nothing found with filter

[Tal]

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

 

[Jeanette Cunningham]

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]

Fantastic Jeanette. You totally answered my question.

Cheers,
Tal

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

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