Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / March 2007

Tip: Looking for answers? Try searching our database.

subform - Dcount and filter

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
miss031 - 18 Mar 2007 00:48 GMT
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.
strive4peace - 19 Mar 2007 06:06 GMT
count records in form you are behind:

me.recordset.recordcount

count records in a subform:

me.subform_controlname.form.recordset.recordcount

Warm Regards,
Crystal
 *
     (:  have an awesome day  :)
  *
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
  *

> I have a single form for entering and editing contacts, in which I have a
> search subform.  
[quoted text clipped - 88 lines]
> Hopefully you understand what I am seeking. Any help would be greatly
> appreciated.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.