Results 1 to 14 of 14
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202

    Post Using command button to open form, message if record does not exist and return to original form


    Good afternoon,
    I have a form (frmDashboardLogs) that has a command button (cmdfrmLogTPAID). This command button is to open another form (frmLogTPAID) using the ID of that record. frmLogTPAID is based on the query qryLogIscOID. If the record does not exist, I want a message "This record does not exist." and to cancel the command. This should return to the original form. Can anyone help?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Try opening the form for a record that doesn't exist
    If an error occurs, note the error number and quote it in your reply.
    I'll supply error handling code to handle it.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    I get a couple of items. 1) "You can't go to the specified record. You may be at theend of a recordset. 2) Error Number 2105 and 3) Error Number 3018. This only happens when I enter the ID that does not exist. I want the form to open with a specific ID. This is the criteria in the qry. Otherwise, works great. Thanks in advance for the help

  4. #4
    Kudos is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    Or don't leave your original form until you've confirmed that the record exists. You could use a DLookup to check.

    Dim RecordExists As Variant

    RecordExists = DLookup()

    If IsNull(RecordExists) Then
    MsgBox "This record does not Exist"
    Else
    DoCmd.OpenForm "frm"
    End If

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Error 2105 - can't go to specified record - makes sense
    Error 3018 - could not find field - this may indicate another issue also needs resolving

    I'm going to offer 3 solutions, one of which is similar to the reply by kudos

    1. Use error handling:

    Code:
    Private Sub cmdfrmLogTPAID_Click()
    
    On Error GoTo Err_Handler
    
        'Your open form code goes here
        DoCmd.OpenForm "frmLogTPAID",,,"your filter criteria here"
    
    Exit_Handler:
        Exit Sub
    
    Err_Handler: 
        If err=2105 or err=3018 Then
            MsgBox "This record does not exist."
        Else
            MsgBox "Error " & err & " in cmdfrmLogTPAID_Click procedure"
        End If
    
        GoTo Exit_Handler
    
    
    End Sub
    2. Check record exists

    Code:
    Private Sub cmdfrmLogTPAID_Click()
    
    On Error GoTo Err_Handler
    
        If DCount("*","qryLogIscOID","ID = " & your filter criteria)>0 Then
            'Your open form code goes here
            DoCmd.OpenForm "frmLogTPAID",,,"ID = " & your filter criteria
        Else
            MsgBox "This record does not exist."
        End If
    
    Exit_Handler:
        Exit Sub
    
    Err_Handler: 
        MsgBox "Error " & err & " in cmdfrmLogTPAID_Click procedure"
        GoTo Exit_Handler
    
    End Sub
    3. Add a combo box to the form listing all records - use your qryLogIscOID as the row source
    Then use the combo selection as the record source for the form

    All should work. In my opinion, method 1 is OK; 2 is better; 3 is best
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Ridders52, Thank you for your response. When I enter the information, I get the code 2105 (which refers to the form I am to open and GoToControl "txtDateStroke" on open. I think I understand that it will not go to this field as the ID does not exist. After closing the error box, I then get the message "This record does not exist" as expected. Just opening the form (without command button on a form) with a non-existent ID, I get the same issue. Entering an ID that is existent, there are no problems. These lead me to believe that it has to be with the fact that it is related to the non-existent ID while trying to open the form. Two questions, 1. How can I avoid opening the form if the ID does not exist, thus avoiding this issue, and just have a message? and 2. Why wouldn't it just open to a blank form? I do have it set not to add a record from this form (this has to do with needing primary information before the form can be generated) At looking at your third suggestion, my concern is that I have over 5,000 IDs. This would create an enormous cbobox. Here is what I adjusted to accommodate. Any further input would be appreciated.

    Private Sub cmdfrmLogTPAID_Click()
    On Error GoTo Err_Handler
    'Your open form code goes here
    DoCmd.OpenForm "frmLogTPAID", , , StrokeID
    Exit_Handler:
    Exit Sub
    Err_Handler:
    If Err = 2105 Or Err = 3018 Then
    MsgBox "This record does not exist."
    Else
    MsgBox "Error " & Err & " in cmdfrmLogTPAID_Click procedure"
    End If
    GoTo Exit_Handler

    End Sub

  7. #7
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Kudos
    ,

    I attempted your code, I am fairly new to programming and I am probably doing something wrong. I put your code in under the on click and it is telling me there is an error in the code. I am under the assumption that the DLookup is automatic from my qry criteria as I have not made any changes to the code you provided. The only variation to your code is in
    blue.
    This is what I have:


    Private Sub
    cmdfrmLogTPAID
    _Click()
    Dim RecordExists As Variant

    RecordExists = DLookup()

    If IsNull(RecordExists) Then
    MsgBox "This record does not Exist"
    Else
    DoCmd.OpenForm "frm
    LogTPAID
    "
    End If

    End Sub


    Thank you in advance for your help.


  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK - so a combobox of 5000 records may be too unwieldy

    If you want to use solution 1 or 2, you need to set your filter criteria correctly

    Code:
    Private Sub cmdfrmLogTPAID_Click()
    On Error GoTo Err_Handler
    
        'Your open form code goes here
        'this assumes you have a number field called StrokeID and a textbox for user entry called txtStrokeID
        DoCmd.OpenForm "frmLogTPAID",,,"StrokeID = " & Me.txtStrokeID
    
    Exit_Handler:
        Exit Sub
    
    Err_Handler: 
        If err=2105 or err=3018 Then
            MsgBox "This record does not exist."
        Else
            MsgBox "Error " & err & " in cmdfrmLogTPAID_Click procedure"
        End If
    
        GoTo Exit_Handler
    
    End Sub
    Or I would use:

    Code:
    Private Sub cmdfrmLogTPAID_Click()
    
    On Error GoTo Err_Handler
    
        If DCount("*","qryLogIscOID","ID = " & your filter criteria)>0 Then
            'Your open form code goes here
             'this assumes you have a number field called StrokeID and a textbox for user entry called txtStrokeID
                     DoCmd.OpenForm "frmLogTPAID",,,"StrokeID = " & Me.txtStrokeID
        Else
            MsgBox "This record does not exist."
        End If
    
    Exit_Handler:
        Exit Sub
    
    Err_Handler: 
        MsgBox "Error " & err & " in cmdfrmLogTPAID_Click procedure"
        GoTo Exit_Handler
    
    End Sub
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    I applied both of your codes into vba for on click. The form to be opened is based on a query with the criteria to enter the ID when opening. I cannot get past this point. One small change in blue as I was getting an error. I believe I corrected this. I cannot get past the DoCmd line in red. it shows me getting a compile error: Expected: end of statement (highlight on the last "). This applies to both Option 1 and Option 2. Your assumption is correct concerning the Stroke ID field and textbox txtStrokeID. Small question as I am new to programming, in your code, you have green font, it that just information or part of the code?

    Private Sub cmdfrmLogTPAID_Click()
    On Error GoTo Err_Handler
    If DCount("*", "qryLogIscOID", "ID = " & StrokeID) > 0 Then
    'Your open form code goes here
    'this assumes you have a number field called StrokeID and a textbox for user entry called txtStrokeID--This is true for the form

    DoCmd.OpenForm "frmLogTPAID",,,"StrokeID = " & Me.txtStrokeID"
    Else
    MsgBox "This record does not exist."
    End If
    Exit_Handler:
    Exit Sub
    Err_Handler:
    MsgBox "Error " & Err & " in cmdfrmLogTPAID_Click procedure"
    GoTo Exit_Handler
    End Sub


    Thank you

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The code in green was comment text for info

    First of all, my apologies - for the code in red there should be no final " - copy/paste error on my part which I've just corrected
    It should read: DoCmd.OpenForm "frmLogTPAID",,,"StrokeID = " & Me.txtStrokeID

    You are getting the filters tangled up
    Change the If DCount line to have the same filter:
    If DCount("*", "qryLogIscOID", "StrokeID = " & Me.txtStrokeID ) > 0 Then
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Getting closer, Made the changes you suggested, now getting Compile error: Method or data member not found referring to Me.txtStrokeID. I double checked the forms, and the field is txtStrokeID. After making the changes, it moves past the first few lines and shows "Error 3075 in cmdfrmLogTPAID_Click procedure." Which is the last message in the vba. Funny thing is, when I click on the command button, it doesn't even ask for the ID. Almost like it isn't looking at the query.


    Private Sub cmdfrmLogTPAID_Click()
    On Error GoTo Err_Handler
    If DCount("*", "qryLogIscOID", "StrokeID = " & StrokeID) > 0 Then
    DoCmd.OpenForm "frmLogTPAID", , , "StrokeID = " & StrokeID
    Else
    MsgBox "This record does not exist."
    End If
    Exit_Handler:
    Exit Sub
    Err_Handler:
    MsgBox "Error " & Err & " in cmdfrmLogTPAID_Click procedure"
    GoTo Exit_Handler
    End Sub

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    We're going around the houses here and I'm beginning to get confused about your responses.
    Suggest you post a stripped down version of your database containing both forms /the query / tables used in the query / anything else needed to see this in practice
    Just logging off as its late here but I'll have a look at it tomorrow if you upload it.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If I may jump in, maybe there is another option.

    In the button click, use Colin's code
    Code:
    Private Sub cmdfrmLogTPAID_Click()
    
        On Error GoTo Err_Handler
    
        'Your open form code goes here
        'this assumes you have a number field called StrokeID and a textbox for user entry called txtStrokeID
        DoCmd.OpenForm "frmLogTPAID", , , "StrokeID = " & Me.txtStrokeID
    
    Exit_Handler:
        Exit Sub
    
    Err_Handler:
        MsgBox "Error " & Err & " in cmdfrmLogTPAID_Click procedure"
        GoTo Exit_Handler
    
    End Sub
    Then, in the form "frmLogTPAID" open event, have code to check if there is at least 1 record:
    Code:
    Private Sub Form_Open(Cancel As Integer)
        Dim r As DAO.Recordset
        Dim RC As Long   'RC = record count
    
        Set r = Me.RecordsetClone  'get a copy of the form records
    
        RC = 0  'default value
       
        'check if recordset has records
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            RC = r.RecordCount
        End If
        r.Close
        Set r = Nothing
    
        If RC = 0 Then
            'no records so cancel form opening
            MsgBox "No records exist. Canceling form opening!"
            Cancel = True
        End If
    
    End Sub

    Just a thought........

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Steve
    Whilst that would also work, I prefer to check the record exists before opening the form (& cancel if not) rather than open the form & check afterwards.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 5
    Last Post: 10-18-2016, 06:00 AM
  2. Replies: 1
    Last Post: 08-19-2016, 01:40 PM
  3. Replies: 13
    Last Post: 07-24-2014, 04:30 PM
  4. Replies: 2
    Last Post: 05-11-2012, 11:52 AM
  5. Replies: 11
    Last Post: 01-26-2012, 01:22 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