Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: How to manage a form that has no data

    Hi All - thanks for reviewing my post

    I have a main form with a query record source. The query has a parameter to ask user which ID# they want, and a where clause that will only show records that a checkbox is false. User should not call on the records where the checkbox is true while in this form.

    When I test this, the ID#'s that the checkbox is false open the form fine. PERFECT. The ID#'s that the checkbox is true gives me a runtime error 2105, in where VBA says you can't go to the specified record (Form_Load to set focus to the subform........it's debugging here at the setfocus to the subform). That's also perfect, since we can't make entries for that record here anyway.

    What I need to do is bypass the 2105 by writing syntax on the main form where when it runs the query to see if I'm getting that ID# returned, a message box warning that there is no data for the ID# they requested pops up, and then closes the form.

    Can someone tell me the event and correct syntax to pull that off?

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Assuming that this is the only time you're likely to get this error message, in this form, you can squash it, pop up your own and close the form using the Form_Error event:

    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2105 Then 'Cannot go to record
       MsgBox "There are no records for the ID Number you requested! Form will be closed!"
       DoCmd.Close
    End If
    End Sub
    If you should find the form closing when you don't expect it to, just remember that you have this routine in there.

    Linq ;0)>
    Last edited by Missinglinq; 04-15-11 at 17:31.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can either use the Error trapping mechanism (On Error Goto...) to intercept the error and take appropriate action when it occurs, or you can test the environment conditions (Query not returning any record, if I correctly understand) before trying to perform an action that will cause an error to occur.

    Without knowing precisely which code is run and where, when and how, it's difficult to be more specific.
    Have a nice day!

  4. #4
    Join Date
    Jun 2010
    Posts
    186
    thank you for your response -

    Linq, can you tell me what the message in green is after the ' in your code? I've never used that in syntax......and not sure exactly what it means (after the ')

    I pasted your example in and tried to open an ID that the query will not return.....I got the error 2105 again opening VBA to debug the Form_Load set focus to the subform

  5. #5
    Join Date
    Jun 2010
    Posts
    186
    Sinndo - the beauty of this form is very little code is there
    I use Form_Load to setfocus to the subform

    Because I handled the data very easily with the query........I was able to test both scenarios - return or not

    Is the query running during Form_Load that might be my problem......have to add to Form_Load event?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you set the focus on the subform in the Form_Load event on the main form, you could try something like:
    Code:
    Private Sub Form_Load()
        
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset("<Query1>", dbOpenSnapshot)
        If rst.BOF = True And rst.EOF = True Then
            rst.Close
            MsgBox "There is not record to display.", vbInformation
            DoCmd.Close acForm, Me.Name
        Else
            rst.Close
            Me.<MySubForm>.SetFocus
        End If   
        
    End Sub
    See also: http://www.mvps.org/access/forms/frm0022.htm
    Have a nice day!

  7. #7
    Join Date
    Jun 2010
    Posts
    186
    OK - little unfamiliar territory but up for the challenge.

    can you help to troubleshoot with me?

    entered Form_Load as follows:
    Private Sub Form_Load()

    Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset("qryfrmRequestResp2611", dbOpenSnapshot)
    If rst.BOF = True And rst.EOF = True Then
    rst.Close
    MsgBox "That request has been approved, or does not exist! Cancelling action", vbInformation
    DoCmd.Close acForm, Me.Name
    Else
    rst.Close
    Me.RQTsfApp2611.SetFocus
    End If

    End Sub

    Run-Time Error '3061'
    Too few parameters. Expected 1.

    VBA debugging on: Set rst = CurrentDb.OpenRecordset("qryfrmRequestResp2611", dbOpenSnapshot)
    When I test parameter with an ID number that is there (in query) I can click end the debug and my form opens the record I need
    When I test parameter with an ID number that is not there, and click end opens a blank form

    Any ideas?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the SQL statement for the query qryfrmRequestResp2611?
    Have a nice day!

  9. #9
    Join Date
    Jun 2010
    Posts
    186
    SELECT [RQTTOREGqry Without Matching tblRQTRESPONSE].REGSTAT32511.ID, [RQTTOREGqry Without Matching tblRQTRESPONSE].ACCOUNT AS RQTTOREGqry_ACCOUNT, [RQTTOREGqry Without Matching tblRQTRESPONSE].Date, [RQTTOREGqry Without Matching tblRQTRESPONSE].STATLU, [RQTTOREGqry Without Matching tblRQTRESPONSE].SLU, [RQTTOREGqry Without Matching tblRQTRESPONSE].SLU2, [RQTTOREGqry Without Matching tblRQTRESPONSE].PTLU, [RQTTOREGqry Without Matching tblRQTRESPONSE].RNOTES, [RQTTOREGqry Without Matching tblRQTRESPONSE].RRIIDD, [RQTTOREGqry Without Matching tblRQTRESPONSE].tblRQT.ID, [RQTTOREGqry Without Matching tblRQTRESPONSE].RSLMNRQT, [RQTTOREGqry Without Matching tblRQTRESPONSE].RSLMNRQT2, [RQTTOREGqry Without Matching tblRQTRESPONSE].RQTDATE, [RQTTOREGqry Without Matching tblRQTRESPONSE].RQTCOMMENTS, ACCTTABLE21109.STREETADDRESS, ACCTTABLE21109.STREETADDRESS2, ACCTTABLE21109.CITY, ACCTTABLE21109.STATE, ACCTTABLE21109.ZIP, ACCTTABLE21109.INTLZIP, ACCTTABLE21109.COUNTRY, ACCTTABLE21109.CONTACT, ACCTTABLE21109.TITLE, ACCTTABLE21109.CONTACT2, ACCTTABLE21109.TITLE2, ACCTTABLE21109.CONTACT3, ACCTTABLE21109.TITLE3, ACCTTABLE21109.TELEPHONE
    FROM [RQTTOREGqry Without Matching tblRQTRESPONSE] INNER JOIN ACCTTABLE21109 ON [RQTTOREGqry Without Matching tblRQTRESPONSE].ACCOUNT = ACCTTABLE21109.ACCOUNT
    WHERE ((([RQTTOREGqry Without Matching tblRQTRESPONSE].RRIIDD)=[Enter Request #]));

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I guess that the problem could be linked to the one you mention in your other post concerning the "ACCOUNT" field. Moreover you should supply the parameter before trying to open the Recordset:
    Code:
    RequestNumber = InputBox("Enter Request #")
    strSQL = "SELECT [RQTTOREGqry Without Matching tblRQTRESPONSE].REGSTAT32511.ID, [RQTTOREGqry Without Matching tblRQTRESPONSE].ACCOUNT AS RQTTOREGqry_ACCOUNT, [RQTTOREGqry Without Matching tblRQTRESPONSE].Date, [RQTTOREGqry Without Matching tblRQTRESPONSE].STATLU, [RQTTOREGqry Without Matching tblRQTRESPONSE].SLU, [RQTTOREGqry Without Matching tblRQTRESPONSE].SLU2, [RQTTOREGqry Without Matching tblRQTRESPONSE].PTLU, [RQTTOREGqry Without Matching tblRQTRESPONSE].RNOTES, [RQTTOREGqry Without Matching tblRQTRESPONSE].RRIIDD, [RQTTOREGqry Without Matching tblRQTRESPONSE].tblRQT.ID, [RQTTOREGqry Without Matching tblRQTRESPONSE].RSLMNRQT, [RQTTOREGqry Without Matching tblRQTRESPONSE].RSLMNRQT2, [RQTTOREGqry Without Matching tblRQTRESPONSE].RQTDATE, [RQTTOREGqry Without Matching tblRQTRESPONSE].RQTCOMMENTS, ACCTTABLE21109.STREETADDRESS, ACCTTABLE21109.STREETADDRESS2, ACCTTABLE21109.CITY, ACCTTABLE21109.STATE, ACCTTABLE21109.ZIP, ACCTTABLE21109.INTLZIP, ACCTTABLE21109.COUNTRY, ACCTTABLE21109.CONTACT, ACCTTABLE21109.TITLE, ACCTTABLE21109.CONTACT2, ACCTTABLE21109.TITLE2, ACCTTABLE21109.CONTACT3, ACCTTABLE21109.TITLE3, ACCTTABLE21109.TELEPHONE
    FROM [RQTTOREGqry Without Matching tblRQTRESPONSE] INNER JOIN ACCTTABLE21109 ON [RQTTOREGqry Without Matching tblRQTRESPONSE].ACCOUNT = ACCTTABLE21109.ACCOUNT
    WHERE ((([RQTTOREGqry Without Matching tblRQTRESPONSE].RRIIDD)=" & RequestNumber & "));"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    An alternative would be to declare the parameter in the QueryDef (Query definition of qryfrmRequestResp2611) then to supply a value for it before trying to open the Query.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •