Results 1 to 6 of 6
  1. #1
    jenkag875 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    25

    Open to a specific record

    Hello. I have the following code on a form and all is working but one feature. When I make the detail visible if it finds the application ID entered, it displays all the table results and I want it to just display the application ID entered. Is this a simple fix?

    Private Sub txtApplicationID_AfterUpdate()


    If DCount("[ApplID]", "tblDQPerson", "[ApplID]= '" & Me.txtApplicationID & "'") > 0 Then
    If DCount("[ApplID]", "tblDQPersonGrowthAttainHS", "[ApplID]= '" & Me.txtApplicationID & "'") <> 0 Then
    MsgBox "Application ID found, proceed to update the High School Growth Attainment."
    Me.Detail.Visible = True
    Else
    MsgBox "The Application ID is in the database but there is not a High School Growth Attainment tied to it to update. Enter a different Application ID or go to the ADD menu to create an entry for the Application ID entered."
    End If
    Else
    MsgBox "That Application ID entered is not in the database, enter a different Application ID."
    Me.txtApplicationID = " "
    DoCmd.RefreshRecord
    End If
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    filter on your id

    me.filter = "[ApplID]= '" & Me.txtApplicationID & "'"
    me.filteron = true

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

  4. #4
    jenkag875 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    25
    I have my code updated to the following with new error message Click image for larger version. 

Name:	error.png 
Views:	8 
Size:	53.8 KB 
ID:	26143 (in the code, the line it's stopping on is the "Me.FilterOn = True"). The results are displaying all.

    Thanks for the assistance thus far.

    Private Sub txtApplicationID_AfterUpdate()
    If DCount("[ApplID]", "tblDQPerson", "[ApplID]= '" & Me.txtApplicationID & "'") > 0 Then
    If DCount("[ApplID]", "tblDQPersonGrowthAttainHS", "[ApplID]= '" & Me.txtApplicationID & "'") <> 0 Then
    MsgBox "Application ID found, proceed to update the High School Growth Attainment."
    Me.Detail.Visible = True
    Me.Filter = "[ApplID]= '" & Me.txtApplicationID & "'"
    Me.FilterOn = True
    Me.Requery
    Else
    MsgBox "The Application ID is in the database but there is not a High School Growth Attainment tied to it to update. Go to the ADD tab to create an entry for the Application ID entered."
    DoCmd.Close
    End If
    Else
    MsgBox "That Application ID entered is not in the database, enter a different Application ID."
    Me.txtApplicationID = " "
    DoCmd.RefreshRecord
    End If
    End Sub

  5. #5
    jenkag875 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    25
    I fixed it, I had record locks set to "all records". When I changed it to "no locks", the filter worked.

    Thanks

  6. #6
    jenkag875 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    25
    I re-opened this as I have 4 different forms to update with this feature. The one I got it working on contained unique records whereas the other 3 can have multiple values per each application ID. It's working all the same except it's still is displaying all the records rather than just the one I filtered on. Is there another update I need to make since it's not distinct values?

    Thanks again

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

Similar Threads

  1. Open form with specific record
    By Lukael in forum Programming
    Replies: 14
    Last Post: 11-16-2015, 06:31 AM
  2. Open form to specific record
    By zoro.1983 in forum Access
    Replies: 3
    Last Post: 05-31-2015, 11:04 AM
  3. Open form on specific record
    By iky123 in forum Forms
    Replies: 1
    Last Post: 04-11-2012, 09:56 AM
  4. Open Specific Record
    By halfaguava in forum Forms
    Replies: 1
    Last Post: 06-09-2011, 04:08 PM
  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