Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling a Module from a Macro (Access 2003)

    I currently have a main search form with approximately 10 unbound fields used as criteria for records in a list box below. The user can double-click the record in the list box to open more details. So far, what I've been doing is using a macro to requery this listbox after the user would input any criteria above. This was efficient when all I had to do was requery the listbox because I could select all the unbound search fields and then select the requery macro one time (instead of having to do it for every unbound field). Now I have a module that has a small amount of code that I would like to run after the requery function in the macro, but can't figure out how to call it (if possible). Can someone help?

    Thanks in advance!
    Drew

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

    Re: Calling a Module from a Macro (Access 2003)

    You must put your code into a public function. You can call this function from your macro using the RunCode action.
    Alternatively, you can convert the macro to VBA code, then turn it into a function. You can set the After Update event of the text boxes to
    =FunctionName()
    where FunctionName is the name of the VBA function. You can do this for a multiple selection of text boxes.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a Module from a Macro (Access 2003)

    Hans,

    I took your alternative advice and setup a function as follows (keep in mind I'm fairly inexperienced VB user)...

    Function Search_Main_F1()
    Dim Records_Shown As Long
    Dim Records_Total As Long

    Records_Shown = DCount("*", "Q_Search_LB_RFQ")
    Records_Total = DCount("*", "Q_Total_LB_Records")

    LB_RFQ.Requery
    Rec_Shown.Value = Records_Shown
    Rec_Total.Value = Records_Total
    End Function

    I get the function to come up, but I have errors on every line of code.

    Thanks,
    Drew

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

    Re: Calling a Module from a Macro (Access 2003)

    If you put this code in a standard module, you must modify it as follows:

    Function Search_Main_F1()
    Dim Records_Shown As Long
    Dim Records_Total As Long

    Records_Shown = DCount("*", "Q_Search_LB_RFQ")
    Records_Total = DCount("*", "Q_Total_LB_Records")

    CodeContextObject!LB_RFQ.Requery
    CodeContextObject!Rec_Shown.Value = Records_Shown
    CodeContextObject!Rec_Total.Value = Records_Total
    End Function

    CodeContextObject is the object (in this case form) that runs the code.

    You shouldn't get errors on the lines calculating the DCounts.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a Module from a Macro (Access 2003)

    Hans,

    It works great.
    Thank you very much!

    Drew

Posting Permissions

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