Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Call a Function in CBF (Access 2000)

    I am used to using Access 97 and I'm now designing a database in Access 2000 and have a puzzle. I have created a function in the code behind a form and for testing purposes I created a simple subroutine there that simply calls the function and assigns the returned value to a variable. When I try to run that test subroutine a dialog box pops up with a list of the routines I have in a general module and it expects me to pick one of those routines! I believe the title of this dialog box is "Macro". My new function is not listed. I tried declaring the new function as Public but that didn't help.

    What am I doing wrong?

    Thanks,
    Bill

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Call a Function in CBF (Access 2000)

    There shouldn't be any difference between Access97 and Access2000. Where is that "simple routine" that calls the function? Is it code in that same form as the function?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Call a Function in CBF (Access 2000)

    Mark,

    Yes the test routine is immediately before the function in the code behind the form. I have included the code in the attached file. If I put the code in the body of this message is there any way to retain my formatting (indenting)? I just noticed that I had a variable name misspelled inside the routine that will not run and that wasn't flagged when I clicked Debug > Compile! It acts as if that block of code is invisible. However, if I change its name declaration to Sub instead of Function I get an error in the Test routine so it is obviously recognized to some degree.

    Any ideas?

    Thanks,
    Bill
    Attached Files Attached Files

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

    Re: Call a Function in CBF (Access 2000)

    You can preserve indenting if you insert a <!t>[pre] tag above and a <!t>[/pre] tag below it. Make sure that you don't have very long lines - they won't be wrapped automatically between <!t>[pre] and <!t>[/pre] tags.
    Example:
    <pre>Sub Test()
    Dim i As Integer
    For i = 1 To 4
    MsgBox "This is step " & i
    Next i
    End Sub
    </pre>

    Another possibility is to replace the number of spaces you use for indenting with <!t>[tab] tags.

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

    Re: Call a Function in CBF (Access 2000)

    You write
    <hr>I have created a function in the code behind a form and for testing purposes I created a simple subroutine there<hr>
    The module behind a form is not a standard code module, it behaves like a class module. You can't simply run procedures in a form module. You can call it in event procedures, for example in the On Click event procedure of a command button on the form.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Call a Function in CBF (Access 2000)

    Hans,

    Thanks for the info about the Pre and /Pre flag. I saw it in the TagPanel but didn't know what it did.

    I'm surprised about not being able to call a function from a subroutine in a form module! Was Access 97 the same? I have used this technique for testing many times but I didn't realize that all those times were in a regular module. Actually, I guess the restriction is not on the test routine calling the function but on my manual execution of the test routine that does the calling -- surely one routine can call another one in a form module. I'll assign the test routine to a button and initiate it that way.

    Thanks
    Bill

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

    Re: Call a Function in CBF (Access 2000)

    Access 97 behaved the same way in this respect.

    And yes, one routine (Sub or Function) can call another one in a form module, you just cannot start a procedure in a form module (or report module, for that matter) manually.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Call a Function in CBF (Access 2000)

    I probably learned that many years ago and didn't try it afterwords but forgot it over the last 3 years while I haven't been doing any Access programming. <img src=/S/time.gif border=0 alt=time width=32 height=48>

    Bill

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Call a Function in CBF (Access 2000)

    You used InStr in your function, which is wrong. InStr is used to search a string. You needed to use the Mid() function, which extracts from a string.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Call a Function in CBF (Access 2000)

    Yes, you are right! I hadn't had a chance to step through the code and see what worked and what didn't.

    Thanks, Mark.

    Bill

  11. #11
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Call a Function in CBF (Access 2000)

    I have fixed several errors in that little routine so now it is creating a correct SQL statement and I can paste that into a query and see the correct results.

    Now I would like to just display the query result when I click the button on the form, so I thought I could use teh following statement:

    DoCmd.OpenQuery Choice

    at the end of the little 'test' routine that I posted. However, I get an error on that statement that says it cannot find the tables used in the SQL statement. Is OpenQuery not supposed to be used in that way? What technique would you use?

    Thanks,
    Bill

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

    Re: Call a Function in CBF (Access 2000)

    DoCmd.OpenQuery is used to open a saved query; you can't use it with an SQL string.
    You can either set the SQL of an existing query to your SQL string, then open that query, or you could create and save a form based on the SQL minus its WHERE part, and open it using DoCmd.OpenForm with the WHERE part as WhereCondition argument.

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Call a Function in CBF (Access 2000)

    What is Choice? This has to be a string containing the name of a query in your database.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Call a Function in CBF (Access 2000)

    Choice is a string variable defined in the code in the attachment to <post:=743,470>post 743,470</post:> higher up in this thread. It's set to an SQL string, so OpenQuery fails.

  15. #15
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Call a Function in CBF (Access 2000)

    I've done the SQL change in a saved query in the past but in that case I wanted the new SQL to stay around for future uses. I was just trying to save a few steps in this case! I kind of like the form idea and I think I'll try that.

    Thanks,
    Bill

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
  •