Results 1 to 3 of 3
  1. #1
    andersonEE is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2011
    Posts
    7

    Set Current Record based on Primary Key Input

    Perhaps not the best title for this issue, but here is my problem:



    I have an Access 2007 Form that is bound to a Linked Table (ODBC). The primary keys in the Table are a Date field and a Number field.

    Using the form controls at the bottom of the screen, I can add new records, and navigate to next / previous record. However, I want to be able to enter values in the "Date" and "Number" and have the form display the correct record. If there is a record with the primary key of the values entered, it will take me to that record...if not, it will just start a new record.

    What is a good way to do this? Do I need separate unbound textboxes for "Date" and "Number" that I can run a query from...this was my best idea but it seems like there should be an easier way.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, the search criteria boxes must be unbound, otherwise you would change the value in the record. You can either use these to filter the form recordset to just the record meeting the criteria or to go to the record matching the criteria. Either will require code, here is an example of go to in VBA procedure.
    Code:
    Private Sub cbxCommunities_AfterUpdate()
    'Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.RecordsetClone
    rs.FindFirst "Community = '" & Me.cbxCommunities & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    Me.cbxCommunities = Null
    End Sub
    I suggest that the form RecordSource be a query that includes a constructed field that concatenates these two key fields. Then the combobox would have a RowSource that does the same.
    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
    andersonEE is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2011
    Posts
    7
    Thanks June7, here's what I did:

    Code:
    Private Sub Goto_Record_Click()
        Dim rs As Object
        Set rs = Me.RecordsetClone
        rs.FindFirst "[timestamp] = #" & Me.Filter_Date & "# And [My_Number] = " & Me.Filter_Number
        If rs.NoMatch Then
            DoCmd.GoToRecord , , acNewRec
        Else
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End Sub
    One of the trickiest things for me was to figure out I needed the ## tags around the date.

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

Similar Threads

  1. Replies: 18
    Last Post: 07-26-2011, 04:38 AM
  2. Replies: 3
    Last Post: 05-11-2011, 02:32 PM
  3. Replies: 2
    Last Post: 03-25-2011, 12:22 PM
  4. Replies: 4
    Last Post: 03-20-2011, 08:45 PM
  5. Query based on current record in form
    By bhsvendbo in forum Queries
    Replies: 1
    Last Post: 06-01-2010, 11:20 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