G
Guest
I have a single form for entering and editing contacts, in which I have a
search subform.
The subform is continuous, and has a search box at the top which, when
either the last name, company name, or phone number are entered, it filters
the subform detail, and show any matching records in the continuous form.
On each line of the subform detail, I have a command button, which opens
another form with the details of that contact, for editing.
What I am having problems with is this: I would to use DCount in a text box
on the header of my subform to show the number of records returned, and when
there is 0 records returned, I would like to change focus to the main form,
to enter the contact as new.
Here is the code for my search box:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
Else
End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like ""*" &
strWord & _
"*"") OR ([table_contact_companies.contactcompany]
Like ""*" & strWord & "*"") OR ([contactprimphone] Like ""*" & strWord &
"*"") OR ([bidder_bidnumber] Like ""*" & strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is the code for my command button. Is there any way to apply this to
the main form instead of opening a new one? Or make it another subform and
set it to visible when the button is clicked? I could only find code to open
a new form.
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub cmd_use_Click()
On Error GoTo Err_cmd_use_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "subform_edit_bidder"
stLinkCriteria = "[bidder_contactID]=" & Me![contactID_current]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmd_use_Click:
Exit Sub
Err_cmd_use_Click:
MsgBox Err.Description
Resume Exit_cmd_use_Click
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hopefully you understand what I am seeking. Any help would be greatly
appreciated.
search subform.
The subform is continuous, and has a search box at the top which, when
either the last name, company name, or phone number are entered, it filters
the subform detail, and show any matching records in the continuous form.
On each line of the subform detail, I have a command button, which opens
another form with the details of that contact, for editing.
What I am having problems with is this: I would to use DCount in a text box
on the header of my subform to show the number of records returned, and when
there is 0 records returned, I would like to change focus to the main form,
to enter the contact as new.
Here is the code for my search box:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
Else
End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like ""*" &
strWord & _
"*"") OR ([table_contact_companies.contactcompany]
Like ""*" & strWord & "*"") OR ([contactprimphone] Like ""*" & strWord &
"*"") OR ([bidder_bidnumber] Like ""*" & strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is the code for my command button. Is there any way to apply this to
the main form instead of opening a new one? Or make it another subform and
set it to visible when the button is clicked? I could only find code to open
a new form.
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub cmd_use_Click()
On Error GoTo Err_cmd_use_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "subform_edit_bidder"
stLinkCriteria = "[bidder_contactID]=" & Me![contactID_current]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmd_use_Click:
Exit Sub
Err_cmd_use_Click:
MsgBox Err.Description
Resume Exit_cmd_use_Click
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hopefully you understand what I am seeking. Any help would be greatly
appreciated.
have an awesome day