Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dim dbs As Database (Access 2000)

    Hello:
    Why are some of my new modules causing an error with the "Dim dbs As Database" ? The error is: "User defined type not defined". This is especially true when using code from Access' Help file?
    Thank you,
    Doug

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    You have to set a reference in Tools / References and check the Microsoft DAO 3.6 Object Library.
    Change the line to :
    Dim dbs As DAO.Database
    This is because the help file use DAO code and Access 2000 has by default ADO.
    Francois

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    I don't see the 'References' in 'Tools'.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    You have to do it in the Visual Basic Editor. Sorry I have to mention it.
    Francois

  5. #5
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    Francois:
    OK, I see it. It's only available when I'm in the module's design view.
    It's getting past the Dim statement and now is hanging at the "Set rst = dbs.OpenRecordset("Names")" statement.
    Runtime Error 13, type mismatch.
    Thanks,
    Doug

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    You have also to change the Dim of the recordset.
    Dim rst As DAO.Recordset
    Francois

  7. #7
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    Thank you, that works.

    This seems like a long way 'round the barn just to put the first and last names at the top of the page in a phone directory. I was using the example in the "Samples" database for 2000.

    On another related note, I've tried to get the phrases "Continued on next page" and "Continued from last page" on the bottom and top of a multi-column directory. Do you have any ideas?

    I don't think the WillContinue and HasContinued properties work as advertised.
    Doug

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    Are you speaking about the RptSmp00.mdb database ?
    Witch report do you mean ?
    Francois

  9. #9
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    Yes, its the one from the Solutions database. I think you can get it separately as RptSam00.mdb. I wanted the directory I was building to show the first and last names on the page as in the sample report, "Customer Phone List." That's the piece of formatting you helped me with.

    Now I see that on the last page of my new directory that instead of showing the last name in the set (Zarenengo), its showing "Johnson" at the top right. The other pages work fine. I wonder if this is due to the multi-column formatting of the report?
    Doug

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    I'll try to explain what I think the problem is.
    In the Solution database the table Customers have a key index on the CustomerID witch is a text field and so it is always sorted in the right way.
    What you have to do is creating a query sorted on the field containing the name you wanted in the header. Maybe you have already one. Use that query in the sub ReportFooter4_Format.
    Replace
    Set rst = dbs.OpenRecordset("Customers")
    by
    Set rst = dbs.OpenRecordset("YourQueryName")

    Hope my explanation is clear and that is the error.
    Francois

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    For the "Continued on next page", put a unbound textbox in the header containing your text. In the On format event of the header, enter the following :
    <pre> If Me.Pages > 1 Then
    Me.MyTextBox.Visible = True
    Else
    Me.MyTextBox.Visible = False
    End If</pre>

    Do the same in the footer for "Continued from last page" but change the condition to
    If Me.Page <> Me.Pages Then
    Francois

  12. #12
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    Thank you, Francois. Your suggestion worked fine for the last name in header question I had.
    Thank you, too for the tip on the "Continued on next page" and "Continued from last page" question. What I have is a multi-column phone directory where the alphabetized listings sometimes go across several pages. When one of the columns iscontinued on the next page I would like to see the phrase, "Continued on next page" at the bottom of the column which is continued on the next page. Then on the following page in the first column I would like to see, "Continued from last page".
    It's the multi-column format that is making it hard to do. Access doesn't know that the items from one column have been continued on the next page. Maybe this requires some type of comparison across pages or something.
    Thank you,
    Doug

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    Can you attache the database so i can work on something real ? If its to big, refer to Hans' <post#=340627>post 340627</post#> to make it under the 100k limit. If it's still to big you can send it to my address (see my profile)
    Francois

  14. #14
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    Francois:
    Thank you for the offer. I'll send it directly to you.
    Doug

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dim dbs As Database (Access 2000)

    Does anyone know a system in VBA to detect when you are printing the last record on a page, before the code is going to the footer section ?
    What I want to do is make a textbox visible in the detail section when I am in the last record of a page. I have no system to know in advance how many records there will be printed on the page.
    Francois

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
  •