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

    Open form, specific record, not filters form?


    Crochet2.zip

    I have two questions! I have attached db, and on the CALFrm I have a subform, where I am trying to get it not to duplicate the value. If I select a screen name from the combo, if it all ready exist in the corresponding table along with the CALID in the same row, then I don't want it to add, and if not, I want it to add. I believe I know whats wrong with my code but don't know how to fix it. Its CboPerson_BeforeUpdate(Cancel As Integer). I don't think its that complicated, I just am not seeing the fix to it?
    Secondly, I have aCboPerson_DblClick(Cancel As Integer) sub in my code and if I dblclk, then it opens form to that record, but the form is under filter, I am trying to get it so it doesn't filter. Maybe a bookmark ????

    Thanks so much
    Dave

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What I do is use the OpenArgs argument of OpenForm to pass value to another form.

    DoCmd.OpenForm "formname", , , , , , Me.LabNum

    Then very simple example of code behind second form:

    Me.RecordsetClone.FindFirst "LabNum='" & Me.OpenArgs & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark

    That example assumes LabNum does exist since users are only allowed to select existing LabNumber. Your code will likely be more elaborate, like:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    If Not IsNull(Me.OpenArgs) Then
        Me.RecordsetClone.FindFirst "LabNum='" & Me.OpenArgs & "'"
        If Not Me.RecordsetClone.NoMatch Then
            Me.Bookmark = Me.RecordsetClone.Bookmark
        Else
            'do something else with OpenArgs value like maybe
            DoCmd.GoToRecord , , acNewRec
            Me!LabNum = Me.OpenArgs
        End If
    End If
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Code:
    Private Sub CboCAL_DblClick(Cancel As Integer)
    If Not IsNull(Me.OpenArgs) Then
        Me.RecordsetClone.FindFirst "CALID='" & Me.OpenArgs & "'"
        If Not Me.RecordsetClone.NoMatch Then
            Me.Bookmark = Me.RecordsetClone.Bookmark
        Else
            'do something else with OpenArgs value like maybe
            DoCmd.GoToRecord , , acNewRec
            Me!CALName = Me.OpenArgs
        End If
    End If
     DoCmd.OpenForm "CALFrm", , , , , , Me.CALID
    End Sub
    Have tried this code above and it does open form but not to correct record. What am I doing wrong here?
    Thanks
    Dave

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Dave,

    Please provide a sample of what exactly you want to happen when you dbl click -using the Names in your database.
    It might also be helpful if you described to readers the logic involved in simple terms with examples. You can prevent duplicates by means of unique composite index, or you could remove items from the list once they have been selected(depends on your needs).

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    DblClick in Subform and Duplicate Record Before UoDate Questions?

    Crochet3.zip

    I have two questions I need help with please! Database Attached

    1. I have form (CALFrm) and a Sub Form (CAL2PersonFrm) where I am trying to get the double click on the subform to open PersonFrm to that specific record and not filter the open form (PersonFrm). I cannot for the life of me figure this out?

    2. Same forms and Sub forms, I am trying to when I select from combo on the sub form to do a DLookUp in the Person2CALTbl to see if it all ready exist in that record to prevent it in the before update event from being duplicated. If I select a Person (ScreenNaame) and it is all ready in the group, then I want to msgbox it, exit. If not, then add record! I have tried multiple ways of doing this and I just am out of solutions?

    Can I get someone to take a peak at this and help me with it?
    Thanks
    Dave

  6. #6
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Orange,
    I just posted to try and give a better clear picture of this, LOL... and then I seen your post... thank you, I hope it explains this better.
    Basically if I pick form the drop down in the subform (CaltoPersonFrm) and I have my "Person or ScreenName" all ready in that group, then I don't want to duplicate that but I believe my last post will clarify it.
    Thanks again
    Dave

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ?? Not clarifying for me.

    What does group represent?

    Readers don't know your requirement nor subject matter. You have forms and subforms which are HOW you are doing something. I don't follow WHAT you are trying to accomplish.

    I am not following but offer this for consideration:
    Since PersonID and CALID represent the composite PK of the Person2CAL table, Access will give you an error when trying to add a duplicate record.
    When offering names for selection on the CAL Name by Screen Name form, you could constrain the available names by not offering those that are already selected (by changing the Recordsource of the Person2CalSubform).

    It would be helpful if you briefly described CAL, ScreenName and Block and Banner.

    Good luck.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    1. Posted code is not used how I suggested. The bookmark code goes in Open event of form being opened. The posted code is not what is in the posted db.

    2. No primary key(s) nor Index defined in Person2CALTbl. Do this and Access will prevent duplicates and annoy users with messages. If you want to control this then the DLookup criteria must include both PersonID and CALID and maybe even BlockID to look for 'duplicates'. As is, CALID is associated with multiple PersonID so of course the DLookup will find a PersonID - the first one it runs into that matches the CALID.

    I tested cboCAL combobox on Person2CalSubFrm and am at a loss on this. Code looks correct. No idea why not allowing the new value in combobox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you June7 and orange,
    I didn't understand what you were telling me June7, sorry!
    Think of CAL as a group, and ScreenNames as people that are in that group, and Block is what level they are at, just for clarification purposes!
    The sub forms are in each form, one for person (Screen Names) and one for CAL (Groups)
    A person can belong to many groups, and many groups can belong on each person (Many to Many relationship)

    orange is correct in access not allowing dups in that case however, I am trying to get ahead of that miserable access error msg that the end user doesn't understand and check before entered with a custom message.
    That's why I was wanting to get a before update event to check, and prevent that access message.
    The other issue is the dbl click event. I hate that having to have it open form in filtered mode. It confuses most people.
    Basically I am using the sub forms to add my data and switch between forms.
    Thanks
    Dave

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What exactly do you not understand?

    Use Cancel = True if there is a match. https://docs.microsoft.com/en-us/off...reUpdate-event

    If you are 'switching' between forms, does that mean both forms remain open? In which case, Open event will not trigger because form is already open.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks for clarifying June7,
    I did get that to work with the double click event and on open event. Thank you so much for that.
    Now If I can figure out the other part!
    Dave

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

Similar Threads

  1. Open form to specific record
    By zoro.1983 in forum Access
    Replies: 3
    Last Post: 05-31-2015, 11:04 AM
  2. Replies: 7
    Last Post: 05-05-2014, 02:59 PM
  3. Open form on specific record
    By iky123 in forum Forms
    Replies: 1
    Last Post: 04-11-2012, 09:56 AM
  4. Open form to specific record
    By Two Gun in forum Forms
    Replies: 7
    Last Post: 11-09-2011, 10:00 AM
  5. Open Form to Specific Record
    By batowl in forum Forms
    Replies: 1
    Last Post: 04-08-2011, 10:10 AM

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