Results 1 to 7 of 7
  1. #1
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87

    Want to go to specific record in form without having a filter

    New problem I can't solve!



    I have two forms. One creates a new record, populating certain fields. The other is the main form. These are not parent/sub forms.

    I want to go from the create record form to the main form ending up on the newly created record AND NOT have the filter on.

    I have other databases where I click a button and go to a specific record on another form, but it ends up just displaying the matching record and I have to turn off the filter to see or find any other records.

    The people using this new database are not "database people" and won't know that the filter has to be turned off nor will they remember to click a button to remove the filter.

    The Main form is not sorted by the primary key, but I'm using the primary key to find the last record created.

    I have run in circles for two hours!

    Here's what I have:

    Code:
    If MsgBox("New Widget Created!" & vbCrLf & vbCrLf & "Would you like to open the new record?", vbYesNo + vbQuestion, "New Widget Created!") = vbYes Then
         
            LastOne = DMax("WidgetAutoID", "tbl_MasterWidgetData")
            Debug.Print LastOne
            lastRec = "[WidgetAutoID] = " & LastOne
            Debug.Print lastRec
            'DoCmd.OpenForm "frm_MasterwidgetData", "WidgetAutoID =" & LastOne    <<--throws a "type mismatch" error
    
            'DoCmd.FindRecord "WidgetAutoID =" & LastOne     <<--- Does show the last record created, but applies a filter and ONLY shows this record
            'DoCmd.GoToRecord acDataForm, "frm_MasterWidgetData", "[WidgetAutoID] = " & LastOne & ""   <<--- throws a "type mismatch" error
            'DoCmd.GoToRecord acDataForm, "frm_MasterWidgetData", LastOne <<--throws an error "expression in argument three invaild
            'DoCmd.GoToRecord acDataForm, "frm_MasterWidgetData", lastRec  <<--throws a "type mismatch" error
         
        Else
    
    
        End If
    The "LastOne" variable is defined as an integer and the "WidgetAutoID" field is an autonumber ... is that the issue? if so, what should the LastOne variable be defined as?
    I also defined "lastRec" as a string, but had the same issues ... type mismatch.

    This seems so simple!


    Susie
    Kansas

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Could you not have button with a Caption: "Click to Show All Records" or similar.
    When clicked, it alters the recordsource accordingly and then requeries the form?
    Even non-database people should understand that.

    Auto numbers are unique, not necessarily positive, nor sequential.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    filters are fast and easy.
    1 click and you can turn it off.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your first & LastOne attempt is concatenating "" at the end, which ought to convert the value to a string, which will give a type mismatch error. Just dump the "" ?
    You didn't use it in the line that worked - why change that?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Opening a form with criteria automatically sets a filter. If you don't want that, open the form with openargs = LastOne
    Then in the target form's open event, use the openargs as a key to findfirst the desired record. The form will show the desired record and be scrollable to any other record.
    Last edited by davegri; 08-14-2020 at 03:55 PM. Reason: fix

  6. #6
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    Thanks davegri! Openargs did it!

    Susie

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Happy to help. Little things mean a lot.

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

Similar Threads

  1. Replies: 2
    Last Post: 10-02-2018, 07:13 AM
  2. Replies: 3
    Last Post: 11-28-2016, 03:17 PM
  3. Replies: 9
    Last Post: 02-24-2015, 11:19 AM
  4. login form and filter to specific record
    By mikael in forum Programming
    Replies: 1
    Last Post: 04-10-2013, 12:02 PM
  5. Filter specific records on sub form
    By foxtet in forum Forms
    Replies: 5
    Last Post: 06-05-2011, 12:06 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