Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Functionality issue with 2003 (Access 2003)

    I am testing a database on access 2003 that runs fine on Access xp. Two problems have popped up with '03: when I link to a form that contains a subform the subform does not display. Clicking on the subform produces a dialog box with "ODBC - Call Failed!" error. If I open the form manually (rather than linking to it from another form) the subform and underlying data are displayed appropriately. This behavior occurs on all forms within the database that have subforms when they are linked to. The other problem is I have on click event for running a report. A message box prompts for Yes or No (to either run the report from the current date (date) or run all dates). Normally if yes is selected the event uses the current date to run the report. With access 2003 this has stopped working, prompting the user to enter a date regardless of selection. I have attached the event below. I searched online for a description of these problems but haven't been able to find anything useful. Has anyone experienced something similar? Any help would be appreciate. Thank you! -Scott

    [ Dim stDocName As String
    Dim Msg, Style, Title, Help, Ctxt, Response

    If (CheckRegionSystem(1)) Then
    Msg = "Run report from current date (No shows all dates)?"
    Style = vbYesNo + vbDefaultButton2
    Title = "Move Dates Report"

    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then
    RptBeginDate = Date
    Else
    RptBeginDate = CDate("01/01/2000")
    End If
    stDocName = "rptMoveDates"
    DoCmd.OpenReport stDocName, acPreview
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Functionality issue with 2003 (Access 2003)

    See if setting macro security (Tools | Macro) to Low helps. Or see the thread starting at <post#=364370>post 364370</post#>.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functionality issue with 2003 (Access 2003)

    Hans, Sorry for the delay in responding to your post. Date issue was resolved by lowering security. Still working on the form/subform problem and adjusting Jet SP8/sandbox values. Thank you for your help!

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functionality issue with 2003 (Access 2003)

    In case you are interested, here are some things I discovered about Access 2003.

    Going from Access 2000 to 2003 is far from a seamless proposition. There are a number of issues.

    1. If you apply a Digital Certificate using Access 2003 (in order to get around the annoying security messages) and then attempt to use the database on a computer running Access 2000 you cannot edit any VBA code. This happens even if using the default file format of Access 2000.

    2. In Access 2000 there is a HasData property, to check to see whether or not a subreport has data. If you don't do that check and there is no data in the subreport you get a #Error. However, Access 2003 doesn't seem to have the HasData property, so any controls with that expression show up empty.
    So if you have two computers, one running Access 2003, the other running Access 2000, even though the file format is Access 2000 (the default), controls with the HasData property show properly on the computer running 2000 but show blank on the computer running 2003.
    I have searched the Microsoft Knowledge Base but cannot find a reference to HasData in Access 2003, or any work-around for that property. Perhaps 2003 doesn't require this data check, even in the 2000 format, but omitting it creates a disaster with computers running 2000.

    3. Using Help is irritating. Anything beyond a rudimentary question requires connection to the Internet in order for Help to check Microsoft Office Online.

    4. There are frequent crashes when closing a database. For no logical reason that I can see.

    Tom

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Functionality issue with 2003 (Access 2003)

    I have just looked up "HasData" in Help in Access 2003 and what I found is almost identical to what is in Access 2000.

    What makes you think it is no longer there?

    Do you realise that if you go to Help from a code window, you get taken to VBA help. If you go to Help from a form or report etc you don't get offered VBA help.
    Regards
    John



  6. #6
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functionality issue with 2003 (Access 2003)

    John
    Thanks for replying. Yes, I realize that there is a difference between the Help that you get in a form or report and the Help that you get in a VBA code window.

    Here's the interesting thing...at least this is what I find here. In Access 2003, there is information on the HasData property in VBA Help but not in the form or report Help. In Access 2000, there is information on HasData in both VBA and the form/report Help.

    So, I stand corrected about there being nothing in Access 2003 about the HasData property. It is in VBA Help.
    On the other hand, I am more interested in what needs to be used in report controls with respect to HasData. For example, here is an expression in a report control that references a subreport control...
    =IIf([rptInvoiceDetails].[Report].[HasData] = -1, [rptInvoiceDetails]![txtTotal],0)

    This works in Access 2000. It does not work when using Access 2003, even when the file format is Access 2000. So therein lies the problem to which I was referring, and I apologize for not making that as clear as I should have.

    When you search the Microsoft Knowledge Base for Access 2000, and plug in "HasData property" what comes up is Knowledge Base article #208718, which indicates the necessity to use the HasData property when referencing subreport controls; otherwise you get the #Error message.

    When you search the Microsoft Knowledge Base for Access 2003, and plug in "HasData property" nothing at all comes up.

    What I need to be able to do is prepare reports, in Access 2000 format, so that they can be used on machines running either Access 2000 or 2003. And it's frustrating to find $0.00 invoices because the expression in the control, while working fine in Access 2000, won't reference the subreports if using Access 2003.

    If you have tried this and can suggest a work-around I would be most appreciative.

    Tom

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Functionality issue with 2003 (Access 2003)

    I can open Access 2000 db's that use this syntax without problem in 2003.

    =IIf([rptInvoiceDetails].Report.[HasData] = -1,

    Try removing the [ ] brackets from around Report. It is the only difference I can see.

    ************
    Later.

    I have tested this and I find that neither 2000 nor 2003 will let me put [ ] around Report. When I put them in it just takes them out again.
    Regards
    John



  8. #8
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functionality issue with 2003 (Access 2003)

    John
    What you say is most interesting! Your testing shows that neither 2000 or 2003 accepts the [ ] around Report. The exact opposite is the case at my end. With both 2000 and 2003, if I don't include the [ ] Access automatically inserts them. I am wondering why yours and mine are different.

    I have traced back through the subreports themselves to see whether or not there is something that Access 2003 doesn't like, but I can't find anything. Everything works fine until it comes to the one expression that tests for HasData, which works on the computer running Access 2000 but not on the computer running Access 2003.

    I could understand it if I was using Access 2003 format on one of the machines, but not when both are using Access 2000 format.

    Stumped I am.

    Tom

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Functionality issue with 2003 (Access 2003)

    The question is *where* are you using the reference? The Jet engine and the VBE handle it differently than the query engine does.
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functionality issue with 2003 (Access 2003)

    Charlotte
    I am using the reference in a report.

    I have a report. The report has 2 subreports. On the report there are controls that read totals from the respective subreports. Each of the controls contains the HasData expression, because if that expression isn't there and there is no data in the subreport the #Error occurs and the report does not work properly.

    Tom

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Functionality issue with 2003 (Access 2003)

    Tom

    This is a mystery to me too!

    Here is a workaround for this specific issue, that I used before I knew about the HasData property.

    Replace =IIf([rptInvoiceDetails].[Report].[HasData] = -1, [rptInvoiceDetails]![txtTotal],0)

    with =IIf([isNumeric([rptInvoiceDetails]![txtTotal]), [rptInvoiceDetails]![txtTotal],0)
    Regards
    John



  12. #12
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functionality issue with 2003 (Access 2003)

    John
    With a little tweaking, what you suggested works in both Access 2000 and 2003!
    =IIf(IsNumeric([rptInvoiceDetails].[Report]![txtTotal]),[rptInvoiceDetails].[Report]![txtTotal],0)

    Now for the crunch question...
    It would appear you have used Access 2003 quite a bit. Do you think the advantages to 2003 merit switching from 2000? I'm still trying to decide whether or not to put the program on my desktop computer.

    With this fix, I'm one step closer to keeping 2003. A couple of other things still bug me, but I may get used to it.

    Thanks again for sticking with me on this.
    Tom

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Functionality issue with 2003 (Access 2003)

    Tom

    I am glad you have something that works.

    It is interesting that you needed to tweak it. The version I gave you works for me in both versions of Access ( and so does your tweaked version).

    In answer to the big question , I am in the same boat as you. I have had 2003 on my laptop for about a week and a half!

    I intend to leave 97 and 2000 on my desktop machine for the time being, as I have a lot of projects done in them.
    If I work on a 2000 project using 2003, the danger is that I will use new features that don't work in 2000.

    John
    Regards
    John



  14. #14
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functionality issue with 2003 (Access 2003)

    John
    Yep, that's the danger - using something that works in Access 2003 but not in 2000. So far (and my experience is about 9 days) the HasData is the only thing I found that doesn't work the same on both machines. I guess I could understand better it weren't for the case that all the files are in Access 2000 format.

    I'm also intrigued by the fact that structures that work on your machines don't work on mine, and vice versa. The expession you suggested gave a #Name? error here, until I tweaked it. Are there different versions of Access 2003? Or maybe different versions of Access 2000?

    Honestly, I'm really uncertain as to whether or not to keep it, particularly if I want to keep stuff in Access 2000 format. The danger in not moving to 2003 is that sooner or later we get left behind...but that will be a few years away I suppose, especially when you consider there are lots of folks using Access 97 and that's 3 versions back.

    Anyhow, if you come to some definitive conclusion, after you experiment a bit more, I would appreciate it if you let me know your considerations.

    Best regards.
    Tom

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Functionality issue with 2003 (Access 2003)

    Tom

    There is one problem I have with 2003, that I am wondering if you have encountered.

    With a form or report open in Design View, I activate the VBA Editor.

    While coding, I want to return to the form/report to look at something, or check a name etc, and it won't let me! I use the mouse, or Alt + Tab , and the Properties Box appears, but the VBA code stays behind it. Sometimes it works if I try a few times, sometimes I have to close the Editor. I think minimising the Editor works too, but I am not sure.

    This problem is only intermittent. Most of the time it does the right thing.

    John
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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