Results 1 to 13 of 13
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    Form filter and add/edit


    GM. I'm using access 2016..i have a form to add/edit records based on a table..because there are so many records in the table, users want a combo box to be able to select a record to edit...this works for editing but when they have to add a record, the form saves the record with the filtered record...I have the for set to go to add new record on form open but once its filtered using the combo box if the user wants to add a record it uses the record already filtered. I am also using the audit trail code to capture additions and edits...what do I need to do to make the form smooth for the users pls..

  2. #2
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Have you tried to place a New Record Command button on the form?

    Dave

  3. #3
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Yes...I have this cmd button on the form..

  4. #4
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Doesn't that button open up to a new record?

    Can you zip and attach a copy of the database?
    Last edited by Dave14867; 08-06-2019 at 11:03 AM. Reason: Added question.

  5. #5
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    The button just take the form to a blank record..can zip at work...will attach sample 2nite...its a simple form based on 1 table with all fields from the table... the unbound combo box is based on the primary key

  6. #6
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Can I not have a cbo filter on the same form to add a record??

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Pretty slim on details...

    Sooooooo,

    What is the code for the button?

    The combo box is unbound?
    What is the code for the combo box?

  8. #8
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Sorry...the combo box is unbound and the button for the New Record button was wizard created..

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by slimjen View Post
    i have a form to add/edit records based on a table..because there are so many records in the table, users want a combo box to be able to select a record to edit...this works for editing
    So there must be code in an event of the combo box???


    And the button has code???

  10. #10
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    For Unbound cbo in afterupdate:
    Code:
    docmd.searchforrecord, "", acfirst, "[clientid] = " &  str(nz(screen.activecontrol, 0))
    Add new record on click:
    Code:
    docmd.gotorecord, "", acnewrec

  11. #11
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, you might need to *clear* the filtered record before going to new.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've never used "SearchForRecord", nor have I ever used "screen.activecontrol" (except in Excel).

    If the combo box is named "cboSearch", using "Docmd.SearchForRecord", I would have
    Code:
        DoCmd.SearchForRecord ,,, "ClientID = " & Me.cboSearch
    or
    Code:
        DoCmd.SearchForRecord acDataForm, Me.Name, , "ClientID = " & Me.cboSearch
    What I do is filter the form:
    Code:
    Private Sub cboSearch_Click()
        Me.Filter = "ID = " & Me.cboSearch
        Me.FilterOn = True
    End Sub
    The button code looks OK. The 2 quotes are optional. I use
    Code:
    Private Sub newrec_Click()
        DoCmd.GoToRecord , , acNewRec
    End Sub


    I've re-read your first post several times and I'm not sure I understand the problem. Would have to see your dB.......

  13. #13
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Got it solved by a work around.....thanks

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

Similar Threads

  1. Replies: 2
    Last Post: 02-20-2017, 11:28 PM
  2. Replies: 9
    Last Post: 02-24-2015, 11:19 AM
  3. Replies: 1
    Last Post: 05-31-2013, 08:53 AM
  4. Replies: 1
    Last Post: 08-16-2012, 01:51 PM
  5. Replies: 28
    Last Post: 03-08-2012, 06:47 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