Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Aug 2007
    Posts
    102

    Unanswered: Print Preview issues

    Hello all,

    My form provides a list of orders (filtered by dates). I have placed a command button on the form to preview an
    order. I'm having problems getting the right results. I want a person to select from the list and preview an Order (by OrderID)in a Report called "Invoice"; an error msg should pop up if the person doesn't select from the list. Here's what I've tried, that isn't quite working. It doesn't seem to connect with the list, which is displayed in a 'Listbox' control.

    Also is there a way to prevent this Form from closing everytime you click the cmd Preview button?

    Code:
    Private Sub Preview_Click()Dim strDocName As String
    Dim strLinkCriteria As String
    
    On Error GoTo ErrorHandler
    
    If IsNull(Me.OrderID) Then
    MsgBox "Please click an order in the list.", vbInformation
    Else
    Me.Visible = False
    strDocName = "Invoice"
    strLinkCriteria = "OrderID = " & Me.OrderID
    DoCmd.OpenReport strDocName, acViewPreview, strLinkCriteria
    End If
    Exit Sub
    
    ErrorHandler:
    MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
    End Sub
    The results I get are the "Invoice" report with no data, just error in the fields. I know the Report design is fine, because if I go outside of the Form and simply open the "Invoice" Report, it displays all the information from the first Order in the Order table.

    I don't get a message when I simply click the Preview cmd button. I'm not warned to "select" from the list. Is it because the form opens with a list of 'Orders' (containing OrderID's)...so the IsNull(will never be true)???

    thanks so much.
    Last edited by imrosie; 08-31-07 at 03:31.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Me.Visible = False
    Is the form closing or dissapearing?

    I think you need to go through some good old fashioned debugging to watch the value of OrderID throughout your sub.

    Perhaps you need a .Value somewhere?
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2007
    Posts
    102

    No closing or disappearing....

    Hi,

    This is the same PrintPreviewInvoices form. The filter is working, so I can view a list of Orders (in listbox) based on Value list "today", "this week", "last week", "this month", etc.......

    However, the Preview cmd button seems disconnected. I did put in the code:
    Code:
    Debug.Print strDocName & strLinkCriteria
    I opened an 'Immediate' window and saw this:
    Code:
    SELECT Customer Name, OrderID, OrderDate FROM OrderList;
    I don't know what that tells me? I thought about a '.value' addition earlier, but since the OrderID, is the value....I couldn't figure where I would add it.
    thanks

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I must agree with George: the "Me.Visible = False" line is what's making your form invisible every time you click the command button. What are you trying to do with that line?

    Sam

  5. #5
    Join Date
    Aug 2007
    Posts
    102

    I took it out....it's still not working...

    Hi Sam, GeorgeV,

    No the form isn't disappearing or closing...the filter are working now, great. I just can get the 'OrderID' info to pass over to the Invoices Report.

    I agree with Georgev too....I copied this sub out of another db, and changed a few lines, but not that one. I should have paid more attention to it. However, with it out, I still don't get the results I'm looking for.

    thanks

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't use filter magic (unnecessary), link criteria (ugly), report-preview (destroys my hide-Access strategy), or me.visible = false (i am not a fan of stroboscopic screen displays) ...so i can only offer some general comments:

    isnull(anyListOrCombo) should be true if nothing is selected.

    .Value is a lifestyle choice.
    for lists .Value is the default property so presence/absence is largely irrelevant.

    I did put in the code:
    Debug.Print strDocName & strLinkCriteria
    I opened an 'Immediate' window and saw this:
    SELECT Customer Name, OrderID, OrderDate FROM OrderList;

    seems to be extremely unlikely!
    you should have seen (exactly, complete with missing space):
    InvoiceOrderID = xxxxx
    where xxxxx is the currently selected OrderID

    tragically, me.visible = false works!
    compare and contrast with clicking myButton with the code
    private sub myButton_Click()
    me.myButton.Visible = False

    which sensibly throws a 2165 error.
    ...but it is not CLOSING your form.
    this may cause confusion down the line if you open the me-form again --- you will start to accumulate multiple invisible instances of the me-form. when your code talks to the me-form, which instance of the me-form is being spoken to?

    last thought: let's assume you are sending strLinkCriteria correctly. what is happening at the other end? what are you doing to receive the criteria in the report?

    this filter/linkCriteria stuff is alien for me - so i might be talking total crap!

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Aug 2007
    Posts
    102

    No, it's not crap

    Hi IzyRider,

    thanks for your detailed comments. You're obviously well experienced in this area. I am still very new at this.

    I want to be clear on goal of the form. It is to provide a list of past orders (including 'today' order)on the basis of a filtered timeframe (today, this weeks, last week, etc). Then, select an order (based on OrderID) from the list and preview or print the order in an 'Invoice' report format.

    More clear information on the form:
    1.) 'PrintPreviewInvoices' - has only 2 controls and 3 cmd buttons (for print, preview, cancel). It is based on a query called 'Orderlist' (has 3 fields, Customer Name, OrderID, OrderDate only). The Form itself is filtered on OrderID.

    2. Control 1 - called 'thelistbox' is an unbound listbox control, (Row/Source) also based on same 'OrderList' query...provide a 3 column box in the middle of the form, displaying the 3 fields.

    3. Control 2 - called 'PayFilter' is an unbound combo box, using a value list("Today", "This Week", "Last Week", "Last Month", etc) and has an After Update event which switches the filters:
    Code:
    Private Sub PayFilter_AfterUpdate()
    Dim SQL As String
    Dim PayFilter As String
    SQL = "SELECT Customer Name, OrderID, OrderDate FROM OrderList;"
    Select Case Forms![PrintPreviewInvoices]!PayFilter.Value
        Case "Today"
            PayFilter = " Where (Year([OrderDate])=Year(Date()) And Month([OrderDate])=Month(Date()) And Day([OrderDate])=Day(Date()))"
        Case "This Week"
           PayFilter = " Where DatePart(""ww"",[OrderDate])=DatePart(""ww"",Date()) and Year([OrderDate]) = Year(Date())"
        Case "Last Week"
            PayFilter = " Where Year([OrderDate])* 53 + DatePart(""ww"", [OrderDate]) = Year(Date())* 53 + DatePart(""ww"", Date()) - 1"
        Case "This Month"
            PayFilter = " Where Year([OrderDate]) = Year(Now()) And Month([OrderDate]) = Month(Now())"
        Case "Last Month"
            PayFilter = " Where Year([OrderDate])*12+ DatePart(""m"",[OrderDate])=Year(Date())*12 + DatePart(""m"", Date())-1"
         Case "All"
          PayFilter = ""
        End Select
    Debug.Print SQL & PayFilter
    Me.thelistbox.RowSource = "Select [Customer Name], OrderID, OrderDate FROM OrderList" & PayFilter
    Me.thelistbox.Requery
    End Sub
    As for the Preview (on click) event, that giving me the trouble....

    "isnull(anyListOrCombo) should be true if nothing is selected." ???? I'm not sure if you're stating that 'thelistbox' should go there in the code??

    The 'PayFilter' does filter the dates for the listbox, just fine.
    Its the 'Preview Click' event that doesn't work.....
    I put in the debugging line, because I thought would show me some failure in the Immediate window, but I did not see "InvoiceOrderID = xxxxx
    where xxxxx is the currently selected OrderID" as you stated....Which means there's definitely something missing with the code.

    Are you suggesting I use "me.visible = false works!
    compare and contrast with clicking myButton with the code
    private sub myButton_Click()
    me.myButton.Visible = False"........with my PayFilter button??? I'm confused there.

    Again, the Invoice Report (is based on a separate query called Invoices) provides a much more detailed report. It works just fine (by default when you open it, it has the navigation buttons for all 2000 past orders, which display fine).

    I just need to figure out how to get the OrderID from the listbox (on selection)on PrintPreviewInvoices form to the Invoices report or query (called Invoices).

    Hopefully I've cleared up the goal of the form..... I hope you can help point me in the right directions.

    thanks IzyRider...
    Last edited by imrosie; 09-02-07 at 17:17.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    isnull(anyListOrCombo) should be true if nothing is selected." ???? I'm not sure if you're stating that 'thelistbox' should go there in the code
    i am saying that your code is correct and does work.

    Its the 'Preview Click' event that doesn't work...
    add a line:
    Private Sub Preview_Click()Dim strDocName As String
    Dim strLinkCriteria As String
    On Error GoTo ErrorHandler
    msgbox "IT IS RUNNING"

    do you see the msg ...guessing not, so copy your code to notepad (not the private sub ..... click() line and not the end sub line - just the meat in the middle).
    delete the sub code
    delete the button
    close the form (save)
    compact the database
    add a new button & give it a different name e.g. ShowPreview
    open it's _click() event in the code window
    paste your code back in from notepad.
    ??? how are we doing now - do you see the msgbox?

    Are you suggesting I use "me.visible = false works!
    i am suggesting that it is blindingly stupid for this code to work but, unfortunately, it does. access throws an error when you .visible = false a control that has the focus, so why on earth does it allow you to .visible = false the entire me-form (including the control that has the focus). totally insane! ridiculously inconsistent. meanwhile - why you have that line in there is a mystery to me - just for fun, please comment it out for the moment.

    ...so maybe you now get the RUNNING msgbox and your code sends off the strLinkCriteria or your Please click... message. that's already something! next step: please clarify what you are doing in the report to handle the strLinkCriteria as they/it arrive(s).

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Aug 2007
    Posts
    102

    you're definitely a guru...

    Hi Izy,

    I'm sure you get that a lot....anyway, I followed what you said and I now get the msg "It is running"...so I'm almost there...

    The report "Invoice" opens, but the reports fields all say "#Error,,,,as though there's no correlation (passing of the 'OrderID' to the query Invoices query, which provides the information to the Invoice Report.....

    Invoices query does work...I can open the Invoice Report and go through each record of Orders (it's filter is OrderID) and get the proper information for each.

    Additionally, when you click Preview, the Invoice Report opens, whether you've selected an OrderId from the listbox or not...this isn't good...I don't know how to fix that. It just opens willy nilly....

    It's as though this part of my 'Preview' code is dormant:
    Code:
    If IsNull(Me![OrderID]) Then
    MsgBox "Please click an order in the list.", vbInformation
    Else
    strDocName = "Invoice"
    strLinkCriteria = "[OrderID] = " & Me![OrderID]
    "If IsNull(Me![OrderID]) Then" will always be true....because of the already populated listbox. Also if you select an Order from the list, it does nothing with the OrderID. In fact it goes straight on to open the Invoice report, ignoring:

    Code:
    strLinkCriteria = "[OrderID] = " & Me![OrderID]
    ......hence the "#Error" in all the reports fields.

    Here's the opening code of the Invoice report:
    Code:
    Private Sub Report_Open(Cancel As Integer)
    Dim strRecordSource As String
    If CurrentProject.AllForms("PrintPreviewInvoices").IsLoaded = True Then
     strRecordSource = "SELECT * FROM Invoices WHERE OrderID = " & Forms!PrintPreviewInvoices.OrderID
           Me.RecordSource = strRecordSource
        End If
    End Sub
    I must have problems clearly understanding how to properly format the strLinkCriteria......or maybe "CurrentProject.AllForms"
    If I can get this sorted out...I think I'll be there straight on. thanks for your help....
    imrosie
    Last edited by imrosie; 09-03-07 at 16:49.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    do you know about breakpoints? assuming not (and apologies if you do):
    click in the margin of the codewindow and a red dot appears - your code stops when it hits the line with the dot.
    then you can tap F8 ...steps thru your code one line at a time. so you can see where it branches and check any values in the immediate window (you can also hover your mouse over a variable and usually see the current value).
    you can also drag the yellow arrow back up your code to start executing from an earlier line. all important stuff for debugging problems. it is also worthwhile to check out the Watch tools (Debug AddWatch etc).

    anyway - since we now have the RUNNING msg, add a break point on the line after the msgbox line.
    run.
    your code stops at the breakpoint.
    ctrl-G (open the immediate window) and type
    ? me.orderid
    and hit return.
    type
    ? isnull(me.orderid)
    and hit return
    do you see what you expect to see?
    a/ when nothing is selected in the list (? isnull(me.orderID) should give TRUE)
    b/ when something is selected in the list (? isnull(me.orderID) should give FALSE)

    a populated list is NULL until something is selected in the list. if you are not seeing the TRUE return then somehow you have something selected in the list. try forcing your list to have nothing selected:- when your code hits the breakpoint, in the immediate window type
    forms!PrintPreviewInvoices.OrderID = null
    hit return and F8 thru your code... surely now you get isnull(me.orderid) returning True!!!!

    mess around with the breakpoints/F8/immediate until you know what your code is doing and why it is doing it.


    next item:
    you have made the effort to prepare strLinkCriteria and then, as far as i can see, you completely ignore the criteria. your code attempts to pick up the value directly from the form and does not seem to make any effort to pick up the incoming strLinkCriteria (keep in mind that my ignorance of filters is TOTAL - so if filter-magic somehow intercepts the criteria, you can ignore this comment).

    rather than messing with the report's .recordsource (??? can this even work the way you are trying - i don't think so), here are two other ways to get where you want to go.

    simple way:
    make a saved query ...all fields from Invoices.
    design-time your report to look at the saved query as .recordsource
    sit in the query design-view criteria box of orderid and hit the magic-wand thingie in the menu.
    navigate in the resulting dialog window
    right window- forms/allforms/your printpreview form
    in the middle window double-click orderid.
    close (save)
    your query now takes the criteria direct from the form.
    kill the strLinkCriteria stuff in your form (no longer needed)
    kill the .recordsource attempt in your report (no longer needed)
    still a good idea for the report to check the form isopen (or there are no criteria for the query).


    less simple way (i prefer since i don't like magic-in-queries and i do like DAO):
    make a saved query ...all fields from Invoices. lets call it 'Fred'
    design-time your report to look at the saved query 'Fred' as .recordsource
    in your Preview_Click()
    dim qdef as DAO.querydef
    set qdef = currentdb.openquerydef("Fred")
    qdef.SQL = "SELECT * FROM Invoices WHERE OrderID = " & me.orderid
    qdef.close
    set qdef = nothing
    ...them open the report.


    izy
    Last edited by izyrider; 09-04-07 at 07:17.
    currently using SS 2008R2

  11. #11
    Join Date
    Aug 2007
    Posts
    102

    Wow!...I've got to print this out and step through all

    Izyrider,

    You've really put lots of effort into this one...I did stumble onto breakpoints once or twice, but not real sure how to set them up, or how to use properly...

    I'm going off now to do all that you're told me...I'll be back shortly to report. thanks so much.
    imrosie

  12. #12
    Join Date
    Aug 2007
    Posts
    102

    Well....I learned a lot!

    OK Izyrider,

    I did what you said in the first part....
    1.Put in the breakpoint on the very next line after the 'msg'. It is exactly:
    Code:
    If IsNull(Me![OrderID]) Then
    2. I ran the form and when the msg came up, I did the ctrl-G and in the immediate window typed:
    ? me.orderid
    and hit return.
    type
    ? isnull(me.orderid)
    and hit return

    The response I received was 749 (the very first orderid in the populated list) AND the second response was FALSE, as you stated.

    Apparently, it's defaulting to the order in the list when nothing is selected.

    3. Next I tried forcing the list to have nothing selected. I cleared the immediate window and ran the form again; ctrl-g to open window and typed:
    Code:
    forms!PrintPreviewInvoices.OrderID = Null
    hit F8 and received this message:
    'Field cannot be updated' when I hit the Ok, it gives me another error msg - Run-time' error '3164' reserved error.

    I kept trying F8, but it's stuck on the same error msgs, so it gets no further in the code.

    OrderID in the query (OrderList) which supports the PrintPreviewInvoices is coming straight from the Orders table and OrderID is an autonumber (here we go)....Is this an autonumber problem???? Oh Gosh....

    "Next Item":
    On strLinkCriteria, I'm not sure why you said "your code attempts to pick up value directly from the form",,,,,,,that's not what I wanted. That is where the problem lies....I'm expecting it to pick up from a selection of an OrderId in the list (Me.OrderID).....I don't have any great experience using filters either...so I can also claim some ignorance of filters here.

    Using your "simple way"; It sounds interesting but I'm not following exactly what you're telling me to do here...I'll play with this a bit to get it going....I'm missing some understanding here.

    "Less simple way" ("i prefer")......
    1. I copied Invoices query and called it Fred

    2. I copied Invoice report to a report called testInvoice, in the recordsource I pointed it to Fred.

    3. I made a backup of PrintPreviewInvoices and then to the Preview_Click added:
    dim qdef as DAO.querydef
    set qdef = currentdb.openquerydef("Fred")
    qdef.SQL = "Select * From Fred WHERE OrderID = " & me.orderid
    qdef.close
    set qdef = nothing

    I think you meant 'Select from Fred, not Invoices'......
    I received an error message from this code too -
    "Compile error, User-defined type not defined..

    Oh well, now what? Thanks so much, all your help is greatly appreciated.
    imrosie

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    easy one first:
    3. I made a backup of PrintPreviewInvoices and then to the Preview_Click added:
    dim qdef as DAO.querydef
    set qdef = currentdb.openquerydef("Fred")
    qdef.SQL = "Select * From Fred WHERE OrderID = " & me.orderid
    qdef.close
    set qdef = nothing

    I think you meant 'Select from Fred, not Invoices'......
    I received an error message from this code too -
    "Compile error, User-defined type not defined..


    no, i definitely meant SELECT * FROM Invoices blah blah

    try this:
    Alt-F11
    (you are now in the code window) - menu: Tools/References
    see if Microsoft DAO appears at the top with the check-marked references.
    odds-on: no!
    scroll on down to M-for-Microsoft and set a reference to DAO

    "Compile error, User-defined type not defined.. should now be gone, my code should run, code should redefine the query, query should feed your report the way you want it to be fed.

    get back to you later on the other items.

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i'm an unbound-controls person.
    thinking that your inability to = null the list is somehow a bound-control issue i had a go for the first time in ages: list = null worked admirably with a bound list. this issue remains a mystery for me and i won't get any closer without seeing your db.

    I kept trying F8, but it's stuck on the same error msgs, so it gets no further in the code.
    ooops! i mentioned you can drag the yellow arrow up, but you can also drag it down (beyond the error line). don't exaggerate when dragging down - if you skip a line that does something crucial to vars used in subsequent lines you will confuse both of us!

    that's not what I wanted
    but that is exactly what you have with
    WHERE OrderID = " & Forms!PrintPreviewInvoices.OrderID
    ...is looking at the form directly and ignoring your painfully constructed strLinkCriteria (and in any case, i believe you can only change report .recordsource at designtime (not runtime) - yes you can code up designtime and edit .recordsource but it is a really ugly thing to do and either of my two query suggestions will be faster and more elegant)) CAVEAT: i still know nothing and care less about filters!

    below is the 'simple' magic-query-criteria method in a picture.
    admitted - table/field/query/form/control names are different, but have a go.

    izy
    Attached Thumbnails Attached Thumbnails magicQuery.gif  
    currently using SS 2008R2

  15. #15
    Join Date
    Aug 2007
    Posts
    102

    still.....compiler probs....

    Hi Izy,
    I put "Invoices" back in the select statement as you instructed......

    However, the compiler is still complaining about:
    Code:
    qdef As DAO.querydef
    The error msg is 'User-defined type not defined'
    it's stuck here....
    is there another way to declare the 'DAO'?

Posting Permissions

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