Results 1 to 7 of 7
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Not in list event and double click events to not fitler forms!

    Crochet Along.zip



    Good Evening all,
    I have a couple of DblClk and NotInList issues i am unable to solve.
    1. On form "MemberProfileFrm" I have a a DblClk and NotInList function on combo "CboEvent"
    They work however, when I DblClick, it opens form, but filters the form to that recrod. I dont want to filter
    this? Also on the not in list event, it opens form and criteria yet it also filters the form to that record. I
    Dont want to filter that either, just call CmdAddEvent and allow all the recrods to be available. I hope this
    makes sense.
    2. On the EventFrm i have a Combo at the bottom called "CboEventSearch" and if i type in a name that is not in the list
    or that i mispelled, it give me a msgbox, OK and forces me to pick from list or i have to hit escape twice to get out of
    it. I really would like to know how to put a not in list on that that would, If Yes, then call AddNewEvent and go to new
    record as its on the same form.
    and if NO, then Undo, and clear the combo box or set to null?
    3. EventFrm, i have a list box "MembersLst" and it to has a double click event, that opens MembersFrm and i have the same
    issue with filters the form to that record. I dont want to filter the form, just go to that recrod and not filter it.
    Thank you and would apriciate some assistance as i have spent countless hours on these three issues.
    Thanks
    Dave

  2. #2
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi all (UPDATE)
    I was able to figure out the double click not to filter forms? Thank heavens and a big thanks to a previous post from June7!

    I am still seeking solution to the not on list event for the combo's if anyone could assist with that.
    Thanks
    Dave

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First, the top two lines in EVERY code module should be
    Code:
    Option Compare Database
    Option Explicit
    ================================================== =============================

    Next, your questions..
    1. On form "MemberProfileFrm" I have a a DblClk and NotInList function on combo "CboEvent"
    They work however, when I DblClick, it opens form, but filters the form to that recrod. I dont want to filter
    this? Also on the not in list event, it opens form and criteria yet it also filters the form to that record. I
    Dont want to filter that either, just call CmdAddEvent and allow all the recrods to be available.
    You are causing the form to be filtered!

    OK the "CboEvent" DblClk event. You have:
    Code:
    Private Sub CboEvent_DblClick(Cancel As Integer)
        Dim LinkCriteria As String
        LinkCriteria = "[EventName] = '" & Me!CboEvent.Text & "'"
        DoCmd.OpenForm "EventFrm", , , LinkCriteria
    End Sub
    Here is the syntax of the "OpenForm" command:
    expression.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

    The WHERE parameter is after the third comma.
    Look at the code above in purple. What is after the third comma? Correct, it is the variable "LinkCriteria". And the variable "LinkCriteria" contains something like "[EventName] = 'Super Mario'".
    How many records do you think will be returned? ONLY THE RECORDS WHERE EVENTNAME = Super Mario!!! There will be no other records in the record set for the form!

    The Same thing is happening in the "Not In List Event" code!


    -------------------------------------------------------------------------------------------------------
    2. On the EventFrmOn the EventFrm i have a Combo at the bottom called "CboEventSearch" and if i type in a name that is not in the list
    or that i mispelled, it give me a msgbox, OK and forces me to pick from list or i have to hit escape twice to get out of
    it.
    Build it like the "Not In List" event in form "MemberProfileFrm".
    Code:
    If x = vbYes Then
      'Append the new value
    Else
       'do something else
    End If

    ----------------------------------------------------------------------------------------------
    3. EventFrm, i have a list box "MembersLst" and it to has a double click event, that opens MembersFrm and i have the same
    issue with filters the form to that record. I dont want to filter the form, just go to that recrod and not filter it.
    When I dblclk the list box, I get an error. (maybe because I added the Option Explicit command)

    From the form "EventFrm", you open the form "MemberFrm", (filtered!), then you have code
    Code:
        With Me.RecordsetClone
            .FindFirst "[MemberID]=" & tmp
            If Not .NoMatch Then Me.Bookmark = .Bookmark
        End With
    The problem is "FindFirst" is trying to search the field "MemberID". BUT you are still in form "EventFrm", which does not have a field "MemberID" in the form record set.
    I would try moving the close form command to just after the open form command.
    Code:
    Private Sub MemberLst_DblClick(Cancel As Integer)
        Dim tmp As Long
    
        tmp = Me![MemberLst].Column(2)
        DoCmd.OpenForm "MemberFrm", , , "[MemberID]=" & tmp
        DoCmd.Close acForm, "EventFrm"     '<<-- moved to here
    '    Debug.Print Me.Name
    
        With Me.RecordsetClone
            .FindFirst "[MemberID]=" & tmp
            If Not .NoMatch Then Me.Bookmark = .Bookmark
        End With
    
    End Sub

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Crochet Along-davegri-v01.zip
    See attached. I think I addressed all the issues in post#1, plus removing calculated fields IsActive and Complete from the EventTbl and incorporating them into a query for the EventFrm recordsource.
    I fixed the filter problem by using openargs in the docmd.openform statements. Then the openargs is used in the form_open event to findfirst, which leaves the form unfiltered.
    Fixed the notinlist problems.
    If I missed something, let me know. It was a lot to debug and test.

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi davegri,
    I just wanted to thank you for all your assistance and work. I have been looking it over and much nicer way to deal with the check boxes. It did make some of my member form and profile form go whacky but I believe you have given me enough to work with here and get that straightened out with little effort. Not sure what is going on yet with them but will take a look tomorrow. But I do want to thank you so much, that gave me some knowledge I have not had and I understand it. Thanks

    I wish I could get the following code to open the form and go to that record added without filtering form but I don't think It is possible but would be nice.
    Thanks again!!

    Dave


    Code:
    Private Sub CboEvent_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String, x As Integer
        Dim LinkCriteria As String
        x = MsgBox("Event is not in Current List, Would you Like to Add?", vbYesNo)
            If x = vbNo Then
            Me.Undo
            Response = acDataErrContinue
            Exit Sub
        End If
        If x = vbYes Then
        strSQL = "Insert Into EventTbl ([EventName]) values ('" & NewData & "')"
        'MsgBox strsql
        CurrentDb.Execute strSQL, dbFailOnError
        'LinkCriteria = "[EventName] = '" & Me!CboEvent.Text & "'"
        'DoCmd.OpenForm "EventFrm", , , LinkCriteria
        DoCmd.OpenForm "EventFrm"
            
        
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
    End Sub

  6. #6
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Crochet Along-davegri-v02.zip
    This includes the request.
    However, when EventFrm is opened, some fields are locked, and you can't enter StartDate, for example.
    This allows EventTbl to have invalid (null) fields in the added records. This should not be allowed, as it screws up the IsActive computation in qEventTbl and ensuing logic.
    Suggest you remove all the locked field code in the DB until the very last coding effort.
    Make sure any added records to any table have logically required fields.

  7. #7
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you so much! Just love it and removed the locks!
    I really appreciate this!
    Dave

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

Similar Threads

  1. Replies: 12
    Last Post: 03-22-2019, 09:42 AM
  2. Replies: 7
    Last Post: 03-11-2017, 11:00 AM
  3. Replies: 6
    Last Post: 02-28-2017, 09:33 AM
  4. Opening records via Double-Click event on a search form.
    By IncidentalProgrammer in forum Programming
    Replies: 4
    Last Post: 11-21-2014, 03:47 PM
  5. Replies: 3
    Last Post: 08-21-2014, 07:42 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