Results 1 to 8 of 8
  1. #1
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53

    Input pop up appears until I open up design view and make a random change

    When I click a button that generates a report, two input prompts appear. One is asking for the OrderID (PK), and the second is a drawing # (regular field). I can type in the correct values or gibberish and the report won't open. Two things will make the report generate the second time I try.



    1) If I go to the Event Procedure of the button, add a breakpoint, click the button, step through the code, then the report generates fine. I then remove the breakpoint and click the button. Report generates fine.

    2) If I go into the design of the report and make any random change, save it, click the button, then the report runs fine with no input prompts.

    After closing the database and re-opening, the problem returns.

    The problem started after I added three new calculated expressions to the query. If I run the query in Datasheet view with a specific order# in the criteria, the query runs with no errors. I don't get prompted twice.

    Anyone know what is going on??

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this could happen if:
    you have a parameter in the query
    if you have a misspelled field in the query
    and (most likely)
    if you have a GROUP sort that no longer exists and shows as EXPRESSION , thus giving the popup

  3. #3
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Quote Originally Posted by ranman256 View Post
    this could happen if:
    you have a parameter in the query
    The Criteria spaces are blank across the whole query

    Quote Originally Posted by ranman256 View Post
    if you have a misspelled field in the query
    When I view the results of the query in Datasheet view, all the fields are filled in correctly.

    Quote Originally Posted by ranman256 View Post
    and (most likely)
    if you have a GROUP sort that no longer exists and shows as EXPRESSION , thus giving the popup
    The Sort spaces are all blank across the query. I'm not sorting the results.

    I will add that this query is used for a handful of reports that are related but have slight differences in the data displayed. Those other reports are working as intended. Only one report is prompting.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do you have these 2 lines as the first two lines of EVERY module?
    Code:
    Option Compare Database
    Option Explicit
    Would you post the code for the button click?
    Could you post the dB - change any sensitive data - only need a few records.......



    I will add that this query is used for a handful of reports that are related but have slight differences in the data displayed. Those other reports are working as intended. Only one report is prompting.
    So there is something happening with this one report or the VBA code.
    Have you tried re-creating the report?

  5. #5
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Quote Originally Posted by ssanfu View Post
    Do you have these 2 lines as the first two lines of EVERY module?
    Code:
    Option Compare Database
    Option Explicit
    Yes, to the Option Compare Database
    Most of them for the Option Explicit

    Below is the code for the button.

    Code:
    Private Sub Command321_Click()
    Dim smlt As String
    Dim strDocument
    Dim dbs As Database
    Dim rst As Recordset
    Dim strSelect As String
    Dim records As String
    Dim qdf As QueryDef
    Set dbs = CurrentDb
    
    strSelect = ("SELECT* FROM qryDoorCalc3 WHERE [ordid] = " & Me.OrdId & "")
    
      'view recordset in query
    Set rst = dbs.OpenRecordset(strSelect, dbOpenSnapshot)
     
        If rst.EOF Then
            Exit Sub
        Else
        
          rst.MoveFirst
            
            Do While Not rst.EOF
            
                If rst!OrdId = Me.OrdId And rst!GlOenL > (rst!RawL / 2) And rst!GlOpenW > (rst!RawW / 2) And rst!ProdId = 4101 Then
                    strDocument = "Door Listing Full Lite"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ") And ( Not [CORE]= """ & "LBR" & """) And (Not[smltdoor]is not null)"
                Else
                
                If rst!SmLtDoor = "Small Light Door" And rst!OrdId = Me.OrdId And rst!GlOenL < (rst!RawL / 2) And rst!GlOpenW > (rst!RawW / 2) And rst!ProdId = 4101 Then
                    strDocument = "Door Listing Half Lite"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ") And ([smltdoor]= """ & rst!SmLtDoor & """)"
                Else
                
                If rst!OrdId = Me.OrdId And rst!GlOenL > (rst!RawL / 2) And rst!GlOpenW < (rst!RawW / 2) And rst!ProdId = 4102 Then
                    strDocument = "Door Listing R Side Lite Long"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ")"
                Else
                
                If rst!OrdId = Me.OrdId And rst!SmLtDoor = "Small Light Door" And rst!GlOpenW < (rst!RawW / 2) And rst!ProdId = 4102 Then
                    strDocument = "Door Listing R Side Lite"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ") And ([smltdoor]= """ & rst!SmLtDoor & """)"
                Else
    
                If rst!Core = "LBR" And rst!OrdId = Me.OrdId Then
                    strDocument = "door listing solid wood"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ")And ([CORE]= """ & rst!Core & """)"
                Else
                
                If rst!OrdId = Me.OrdId And rst!ProdId = 4100 Then 'rst!SmLtDoor <> "Small Light Door" And rst!OrdId = Me.OrdId Then 'Or rst!Core <> "LBR" Then
                    strDocument = "Door Listing Flush"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ") And ( Not [CORE]= """ & "LBR" & """) And (Not[smltdoor]is not null)"
                   
                    
            End If
            End If
            End If
            End If
            End If
            End If
            rst.MoveNext
    
            Loop
        End If
    
        rst.Close
        Set dbs = Nothing
    End Sub

    Quote Originally Posted by ssanfu View Post
    So there is something happening with this one report or the VBA code.
    Have you tried re-creating the report?
    I believe the issue is with this one report. I haven't tried re-creating it. Do you mean from scratch??

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Will the report open manually?

    When you open the query "qryDoorCalc3" with a specific ordid, is more than 1 record returned? (Why the DO Loop in code if you are opening a report?)


    Quote Originally Posted by breezett93 View Post
    I believe the issue is with this one report. I haven't tried re-creating it. Do you mean from scratch??
    Could be some corruption. Have you done a "Compact and Repair"?
    If report still won't open, re-create the report from scratch.

    I modified your code:
    Code:
    Private Sub Command321_Click()
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strDocument As String
        Dim strSelect As String
    
        '   Dim smlt As String   ' <<--variable not used
        '   Dim records As String   '<<-- variable not used
        '   Dim qdf As QueryDef   '<<--variable not used
    
        Set dbs = CurrentDb
    
        strSelect = "SELECT * FROM qryDoorCalc3 WHERE [ordid] = " & Me.OrdId
    
        'view recordset in query
        Set rst = dbs.OpenRecordset(strSelect, dbOpenSnapshot)
    
        If rst.EOF Then
            'do nothing - no records returned so Exit Sub
        Else
            rst.MoveFirst
    
            Do While Not rst.EOF
    
                If rst!GlOenL > (rst!RawL / 2) And rst!GlOpenW > (rst!RawW / 2) And rst!ProdId = 4101 Then
                    strDocument = "Door Listing Full Lite"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ") And ( Not [CORE]= 'LBR' And (Not[smltdoor]is not null)"
                ElseIf rst!SmLtDoor = "Small Light Door" And rst!GlOenL < (rst!RawL / 2) And rst!GlOpenW > (rst!RawW / 2) And rst!ProdId = 4101 Then
                    strDocument = "Door Listing Half Lite"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ") And ([smltdoor]= '" & rst!SmLtDoor & "')"
                ElseIf rst!GlOenL > (rst!RawL / 2) And rst!GlOpenW < (rst!RawW / 2) And rst!ProdId = 4102 Then
                    strDocument = "Door Listing R Side Lite Long"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ")"
                ElseIf rst!SmLtDoor = "Small Light Door" And rst!GlOpenW < (rst!RawW / 2) And rst!ProdId = 4102 Then
                    strDocument = "Door Listing R Side Lite"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ") And ([smltdoor]= """ & rst!SmLtDoor & """)"
                ElseIf rst!Core = "LBR" Then
                    strDocument = "door listing solid wood"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ") And ([CORE]= '" & rst!Core & "')"
                ElseIf rst!ProdId = 4100 Then     'rst!SmLtDoor <> "Small Light Door" And rst!OrdId = Me.OrdId Then 'Or rst!Core <> "LBR" Then
                    strDocument = "Door Listing Flush"
                    DoCmd.OpenReport strDocument, acViewPreview, , "([ordid]=" & rst!OrdId & ") And ( Not [CORE]= 'LBR') And (Not[smltdoor]is not null)"
                Else
                    '??? -oops - how did we get here??
                End If
                rst.MoveNext
    
            Loop
        End If
    
        rst.Close
        Set dbs = Nothing
    End Sub
    In a couple of places, you have "Not[smltdoor]is not null". This does not make sense. It appears that "smltdoor" is text.
    "NOT" is used mainly for Boolean types - it reverses TRUE to FALSE or FALSE to TRUE. Can a string be true or false??
    Then there is a missing space between the closing bracket and "Is".

  7. #7
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Quote Originally Posted by ssanfu View Post
    Will the report open manually?

    When you open the query "qryDoorCalc3" with a specific ordid, is more than 1 record returned? (Why the DO Loop in code if you are opening a report?)

    Could be some corruption. Have you done a "Compact and Repair"?
    If report still won't open, re-create the report from scratch.
    The report does open manually. The same two prompts (OrdId and DwgNo) pop up, but it auto picks the oldest order in the system. A new message also opened saying On Load produced an error; so I'll go through that section. Not sure why I never got that message until opening the report manually. *Edit: It's only a simple if statement that checks out...

    Every new line item in the order will create a new record in the query (regardless of quantity of each item). So sometimes, yes, only one record; sometimes there will be multiple records pulled. Every line item gets its own page in the report. That's why it loops.

    Every time the database closes, it runs a Compact and Repair.

    I will add that I did not design this database. It's about 20 years old; so over the past couple years, I've come across lots of issues.

    Quote Originally Posted by ssanfu View Post
    In a couple of places, you have "Not[smltdoor]is not null". This does not make sense. It appears that "smltdoor" is text.
    "NOT" is used mainly for Boolean types - it reverses TRUE to FALSE or FALSE to TRUE. Can a string be true or false??
    Then there is a missing space between the closing bracket and "Is".
    [smltdoor] is a field in the query. Text gets entered in that field. So to my understanding, if the required text is present in that field, then it's True.

  8. #8
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Short closing update:

    I decided to import the report from the previous version released. This would undo the changes in-between, but I was running out of options. After importing, the report is now working, but my edits are still there. So, I'm not sure if this is over.

    Thanks to all who responded.

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

Similar Threads

  1. Vertical Scrollbar Appears At Random
    By lccrews in forum Access
    Replies: 3
    Last Post: 10-31-2017, 06:54 PM
  2. Replies: 5
    Last Post: 09-12-2014, 06:41 AM
  3. Replies: 2
    Last Post: 04-16-2013, 01:34 PM
  4. Change right mouse menu in Design View ?
    By byterbit in forum Access
    Replies: 1
    Last Post: 02-03-2011, 11:29 PM
  5. Replies: 1
    Last Post: 12-20-2010, 09:09 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