Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Office 9 object library (Access 2000)

    Microsoft Office 9 object library

    I cannot understand what fails to my code for creating a reference to the Microsoft Object library.it seems to me that the code is all right, the path is the same
    but my code does not produce a reference to this library.
    Could someone help me ?


    Function AddOffice9() As Boolean
    Dim ref As Reference, strFile As String
    strFile = "C:Program FilesMicrosoft OfficeOfficeMS09.DLL"
    On Error GoTo Error_AddOffice9
    Set ref = References.AddFromFile(strFile)
    AddOffice9 = True
    Exit_AddOffice9:
    Exit Function
    Error_AddOffice9:
    'MsgBox Err & ": " & Err.Description
    AddOffice9 = False
    Resume Exit_AddOffice9
    End Function

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

    Re: Office 9 object library (Access 2000)

    I would avoid setting references in code, it is a very tricky subject.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Office 9 object library (Access 2000)

    Acepting your remark as quite true, i will be really grateful if you have one short look at my code.

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

    Re: Office 9 object library (Access 2000)

    But why do you want to set a reference to the Office 9 library in code? There should be no need to do so. If you set it manually, the reference will remain set, and it will even be adapted automatically if the database is opened in Access 2002 or later. If you would try to run your code in Access 2002 or later, you'd cause an error since the Office 9 library is not available there.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Office 9 object library (Access 2000)

    Because we are sending the database to different cities and it will be easier for us if we do it by code. Otherwise we will have a lot of headaches. We will do that if we fail to do it in code.I have a code that creates the toolbar,but it needs a reference with Office 9.Do you know by chance any code that does not require reference to Office 9 ?

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

    Re: Office 9 object library (Access 2000)

    You're in for a lot of headaches if you try to set the reference in code.. Simply set the reference manually, in Tools | References... in the Visual Basic Editor. The reference will be stored in the database, and it will still be there when the database is opened in another city.

    You can omit the reference if you modify your code as follows:
    - Instead of declaring an object as CommandBar or as CommandBarButton, declare it as Object.
    - While the reference is still set, use the Object Browser to look up the values of symbolic constants such as msoControlButton, and replace the constants in the code with their values.

    Here is an example of such code. You do *not* need a reference to the Microsoft Office n.0 Object Library to run it:

    Sub CreateToolbar()
    Dim cbr As Object
    Dim cbb As Object
    ' msoBarTop = 1
    Set cbr = CommandBars.Add(Name:="My Toolbar", Position:=1, Temporary:=True)
    ' msoControlButton = 1
    Set cbb = cbr.Controls.Add(Type:=1, Temporary:=True)
    With cbb
    .Caption = "My Button"
    ' msoButtonCaption = 2
    .Style = 2
    .OnAction = "MyBeep"
    End With
    cbr.Visible = True
    Set cbb = Nothing
    Set cbr = Nothing
    End Sub

    Function MyBeep()
    Beep
    End Function

  7. #7
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Office 9 object library (Access 2000)

    Dear Hans,

    it is a wonderful code that you have sent me.It appears on the right place, no need of any reference.!!!!!!!!!!
    How can i create a second button ? If i could create a second button, that it will be no problem for me to create the further buttons.Can you help me create the first button with the image LeftArrow with your code ?. In order to help me i want to send you the code i intended to use before your suggestion,where the first button was a left arrow( Faceid = 41)
    Sub BuildCustomToolbar()
    Dim oCmdBar As CommandBar
    On Error Resume Next

    'point to custom toolbar
    Set oCmdBar = CommandBars("aaaa")

    'if it doesn't exist create it
    If Err <> 0 Then
    Set oCmdBar = CommandBars.Add("zzzzz")
    Err = 0
    With oCmdBar
    .Position = msoBarTop
    'now add a control
    With .Controls.Add(msoControlButton)
    .Caption = "zzz"
    .OnAction = "Myback"
    .Caption = "Choices"
    .TooltipText = "Select choice from the list"
    .Width = 80


    '.Style = msoButtonCaption
    .Tag = .Caption
    'set the image, don't know how to make custom one
    'but 80 onwards are letters of the alphabet
    .FaceId = 41
    End With


    'now add another control
    With .Controls.Add(msoControlButton)
    .Caption = "zzz"
    .OnAction = "Myback"
    .Caption = "Choices"
    .TooltipText = "Select choice from the list"
    .Width = 80


    .Style = msoButtonCaption
    .Tag = .Caption
    'set the image, don't know how to make custom one
    'but 80 onwards are letters of the alphabet
    '.FaceId = 41
    End With

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

    Re: Office 9 object library (Access 2000)

    My previous reply already tells you what you have to do:

    1) Change the declaration of oCmdBar to ... As Object instead of ... As CommandBar.
    2) Replace the constants such as msoBarTop with their values - you can look them up in the Object Browser (press F2) or in my reply.

    You can use FaceID's 38, 390, 40 and 41 for Up, Right, Down and Left arrows, respectively.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Office 9 object library (Access 2000)

    Thank you Hans.I did understand that.My problem is that when i try to create the second button,the first button is not to be seen. One can see only My second button ?
    Sub CreateToolbar()

    Dim cbr As Object
    Dim cbb As Object
    ' msoBarTop = 1
    Set cbr = CommandBars.Add(Name:="My Toolbar", Position:=1, Temporary:=True)
    ' msoControlButton = 1
    Set cbb = cbr.Controls.Add(Type:=1, Temporary:=True)
    With cbb
    .Caption = "My First Button"
    ' msoButtonCaption = 2
    .Style = 2
    .OnAction = "MyBeep"
    End With
    cbr.Visible = True
    , create second button

    With cbb
    .Caption = "My Second Button"
    ' msoButtonCaption = 2
    .Style = 2
    .OnAction = "MyBeep"
    End With
    cbr.Visible = True



    Set cbb = Nothing
    Set cbr = Nothing
    End Sub

    Function MyBeep()
    Beep
    End Function

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

    Re: Office 9 object library (Access 2000)

    That is because you never create that second button. The first one is created by the line

    Set cbb = cbr.Controls.Add(Type:=1, Temporary:=True)

    You must repeat that line for each new control you create (of course, after setting the properties of the previous one)

  11. #11
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Office 9 object library (Access 2000)

    Dear Hans,

    I have successfully set with your code the toolbar buttons.I also set the arrows for moving left and right through the list of the form.However i didnt succeed quite with this.I wrote the function
    Public Function MoveOn()
    DoCmd.GoToRecord , , acNext
    End Function
    and then
    .OnAction = "MoveFrom"

    However the movement to the left was not successful.
    My successful code in the previous way was the folloing:
    Private Sub CmdOn_Click()
    On Error GoTo Err_CmdOn_Click
    DoCmd.GoToRecord , , acNext
    Exit_CmdOn_Click:
    Exit Sub
    Err_CmdOn_Click:
    MsgBox Err.description
    Resume Exit_CmdOn_Click
    End Sub

    How can i use here the On Error description and is there a more sound way of moving on and back in the subform ?

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

    Re: Office 9 object library (Access 2000)

    Whay prevents you from adding error handling to the function?

  13. #13
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Office 9 object library (Access 2000)

    I jave rewritten the command lines of the command button:
    Private Sub CmdOn_Click()
    On Error GoTo Err_CmdOn_Click
    DoCmd.GoToRecord , , acNext
    Exit_CmdOn_Click:
    Exit Sub
    Err_CmdOn_Click:
    MsgBox Err.description
    Resume Exit_CmdOn_Click
    End Sub
    Into the following public function
    Public Function MyOn()
    On Error GoTo MyOn
    DoCmd.GoToRecord , , acNext
    MyOn:
    Exit Sub
    Err_MyOn:
    MsgBox Err.description
    Resume MyOn
    End Function

    then i have written the name of the function in the code:
    Set cbb = cbr.Controls.Add(Type:=1, Temporary:=True)
    With cbb
    '.Caption = "My Button"
    ' msoButtonCaption = 2
    ' .Style = 2
    .FaceId = 198
    .Width = 30


    .OnAction = "MyOn"
    End With

    By clicking the error i get the message Yyou cant go to the specified record, while by clicking the command button the codes moves ro the next record

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

    Re: Office 9 object library (Access 2000)

    In a sub, you use the line Exit Sub to end execution. In a function, you should of course use Exit Function, not Exit Sub.

    However, in this particular function, I wouldn't display an error message - trying to go before the first record or after the last/new record will cause an error, but it is not necessary to show a message box. So the code can be simplified to

    Public Function MyOn()
    On Error Resume Next
    DoCmd.GoToRecord , , acNext
    End Function

  15. #15
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Office 9 object library (Access 2000)

    Thank you for your comments, Hans ! You help me a lot. The problem with my new code in the toolbar is, that it does not work as faultlessly as in my command button. For example, the code goes through the customer id's.
    It can go from customer say 1001 to customer 1002.However if the next customer is 1008, then i get the error message that it cant go to the next record.While with the code in the button, i easily go to the next customer number 1008.
    That is my problem

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
  •