Results 1 to 8 of 8
  1. #1
    tlkng1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2014
    Posts
    12

    Form Command Button to Select A New Record to Display Based on User Input

    Hi All

    I've been researching this for a long while now.



    A form contains multiple information fields. I have placed a command button with the intent to allow the user to go to a different record based on input into an Input box. The idea being that Access searches on the user input and displays the correct record. In the case of this particular database, the UserIDs are unique and known or given to us by the user.

    I thought this was a DoCmd.OpenRecord process but I must be missing the arguments somewhere, or just flat out using the wrong command .


    The form front looks like:



    The box under the title User Profile identifies the user's ID and is also the PK. What I want the Profile Search button to do is to fire an input box to get the UserID and have Access autoload the matching user's info into the from the appropriate table. Eventually this same type search will be able to also be done from a Navigation Screen.

    I admit I am very basic in my VBA coding and this is just a snippet as I was trying to do a step by step sequence. This is based as an OnClick activate.

    Private Sub cmdSearchUser_Click()
    Dim ID As String
    Dim LastName As String (intent is to allow for both an ID search or a last name search but not included in the snippet as the intended ElseIF..was just trying to get the first piece functioning)
    ID = InputBox ("Please enter ID")
    If [ID] = "" Then
    MsgBox "You failed to make an entry", vbRetryCancel
    Else
    DoCmd.GoToRecord [tblUser], , "ID"
    End If
    End Sub

    I've had other variations on trying to get the code to identify that the ID return identifies and displays the matching record but I am not so sure the GoToRecord is actually the correct option(?).

    Ideas?

    Thanks

    Terese

  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
    AFAIK, there is no DoCmd.OpenRecord, there is OpenForm.

    GoToRecord is not the correct option. If you want to go to a record as opposed to filtering, use RecordsetClone and Bookmarks. https://docs.microsoft.com/en-us/off...RecordsetClone

    Apply filter criteria to Filter property. Review http://allenbrowne.com/ser-62.html
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think what you should investigate is the Bookmark property but not sure because you didn't say if the form was bound to anything. Basically, you clone the form recordset (Me.RecordsetClone) then use the Find method on that to locate a single record based on your search value. You then set your form .Bookmark property to be the bookmark property of the clone and you end up showing that record. That will only work if the search value will return a single unique record.

    Not sure what's being passed by the Input Box but if you could have that as part of the form record it could be simpler. You mention UserID so if that's what is being used by the input, shouldn't that just be part of the form record and you get it from there?

    FWIW, GoToRecord assumes you know what the record position is in the record count (e.g. 5th record of 50). It doesn't necessarily mean that you can go to a record with the ID of 5.

    EDIT - forgot to say you could just filter the form, but again, that assumes it is bound.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Seeing all those blank textboxes on the form, it appears that either the form is unbound or the form has property Data Entry = Yes.
    If it's the latter, your form cannot lookup anything - change it to No.

  5. #5
    tlkng1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2014
    Posts
    12
    Quote Originally Posted by davegri View Post
    Seeing all those blank textboxes on the form, it appears that either the form is unbound or the form has property Data Entry = Yes.
    If it's the latter, your form cannot lookup anything - change it to No.
    The boxes are empty for visual...data is actually there but not shown just to maintain user security.

  6. #6
    tlkng1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2014
    Posts
    12
    Quote Originally Posted by Micron View Post
    I think what you should investigate is the Bookmark property but not sure because you didn't say if the form was bound to anything. Basically, you clone the form recordset (Me.RecordsetClone) then use the Find method on that to locate a single record based on your search value. You then set your form .Bookmark property to be the bookmark property of the clone and you end up showing that record. That will only work if the search value will return a single unique record.

    Not sure what's being passed by the Input Box but if you could have that as part of the form record it could be simpler. You mention UserID so if that's what is being used by the input, shouldn't that just be part of the form record and you get it from there?

    FWIW, GoToRecord assumes you know what the record position is in the record count (e.g. 5th record of 50). It doesn't necessarily mean that you can go to a record with the ID of 5.

    EDIT - forgot to say you could just filter the form, but again, that assumes it is bound.
    I did see the way to just bring up the usual filter/search function but our users aren't always computer savvy. . Back to researching I go ...TKS.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Did you see post #2?
    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.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I did see the way to just bring up the usual filter/search function but our users aren't always computer savvy.
    My post was also about using the form's recordset clone in your current method - or filtering the form, but June7 beat me to that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-16-2018, 03:38 AM
  2. SELECT Records based on user input
    By Mattrob in forum Queries
    Replies: 1
    Last Post: 10-10-2013, 12:28 AM
  3. Replies: 3
    Last Post: 03-11-2013, 05:11 PM
  4. How do you select a field based on user input?
    By technesurge in forum Queries
    Replies: 5
    Last Post: 06-20-2012, 02:04 PM
  5. display a list of values based on user input
    By karl1971 in forum Access
    Replies: 3
    Last Post: 12-06-2011, 09:19 AM

Tags for this Thread

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