Results 1 to 4 of 4
  1. #1
    RLehrbass is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2015
    Posts
    102

    Ok, search code work on one form but not the other form?

    * This code works to search frmMember for criteria found in tblMember:

    Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
    MsgBox "You must select a field to search."

    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
    MsgBox "You must enter a search string."

    Else

    'Generate search criteria
    GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

    'Filter Members based on search criteria
    Form_frmMember.RecordSource = "select * from tblMember where " & GCriteria
    Form_frmMember.Caption = "Members (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

    'Close frmSearch
    DoCmd.Close acForm, "frmSearch"

    MsgBox "Results have been filtered."

    End If



    End Sub


    * But this next code which I have copied and Pasted to frmEventInput, then renamed the frm and tbl, retrieves the same criteria as the above (tblMember) instead of the tblEventInfo...

    Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
    MsgBox "You must select a field to search."

    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
    MsgBox "You must enter a search string."

    Else

    'Generate search criteria
    GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

    'Filter Events based on search criteria
    Form_frmEventInput.RecordSource = "select * from tblEventInfo where " & GCriteria
    Form_frmEventInput.Caption = "Events (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

    'Close frmSearch
    DoCmd.Close acForm, "frmSearchEvent"

    MsgBox "Results have been filtered."

    End If

    End Sub

    * What am I missing??? I'm so confused.
    Attached is a copy of my DB.
    Membership-database-constructionzipreopened-7-18-15.zip

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The short answer is:
    In the form "frmSearchEvent" there is a combo box named "cboSearchField". The row source is a value list. The value list is for the fields in "tblMember" NOT "tblEventInfo".

    In other words, the combo boxes on forms "frmSearchEvent" and "frmSearch" have the same value list.

  3. #3
    RLehrbass is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2015
    Posts
    102
    Thank You. I was sure I was overlooking something simple. Haha, My very very Bad.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Great.

    BTW, referencing a form using
    Code:
    Form_frmEventInput.RecordSource = "select * from tblEventInfo where " & GCriteria
    is the wrong method. You should use
    Code:
    Forms!frmEventInput.RecordSource = "select * from tblEventInfo where " & GCriteria
    And instead of changing the form record source, you might think about using the Filter property. The code would look like
    Code:
            Forms!frmEventInput.Filter = GCriteria
            Forms!frmEventInput.FilterOn = True
            Forms!frmEventInput.Caption = "Events (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
    To remove the filter, the code would be (depending on where the button is):
    Code:
         Me.Filter = ""
         Me.FilterOn = False
    This is just my preference.....


    Other things I noticed:
    ** I would remove ALL look up FIELDS (different than look up TABLES) from your tables.
    See The Evils of Look up FIELDS
    http://access.mvps.org/access/lookupfields.htm
    No experienced programmer (that I know of) uses look up FIELDS.
    Also see
    The Ten Commandments of Access
    http://access.mvps.org/access/tencommandments.htm



    ** "Name" is a reserved word in Access and shouldn't be used for object names.


    ** "Option Explicit" should be at the top of EVERY code module. There is an option to require variable declarations.


    ** You have "ID" as the name of the PK field. This is a very poor name for a field, let alone 10 tables.
    Which is easier to understand "ID" or "TimeID" (or "Time_ID")??
    Take time to use a naming convention. It will help you in the future.

    You might also read these
    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers

    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    Use Autonumbers properly
    http://access.mvps.org/access/general/gen0025.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 07-03-2015, 05:03 PM
  2. Replies: 5
    Last Post: 12-09-2014, 02:38 PM
  3. Replies: 6
    Last Post: 09-02-2012, 04:30 PM
  4. !!!!Urgent!!!! Search code doesn't work!
    By Laetilae in forum Programming
    Replies: 4
    Last Post: 12-13-2010, 10:34 PM
  5. my search form won't work?
    By sams in forum Forms
    Replies: 5
    Last Post: 05-06-2010, 02:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums