Calling a VBA Function

Question

Dear Experts ( Ms Access);

I asking for your help again on how to call a J Street function from a module , I managed to import it together with a macro the way it is in the sample database without errors or error free. Now how do I call it on my login form (Start-up form) , I like using this form because that where the module for checking value date is  see below no warning is required if the subscription period is over:

Private Sub Form_Open(Cancel As Integer)

  if date>= "#10/15/2016#" then

 msgbox("Sorry Evaluation Period Expired")

   DoCmd.quit

Exit sub

End if

End Sub

Assuming now I decide to use on load event below , what name should I use to call the J Street function. Example

Private Sub Form_Load()

Call ???????????????

End Sub

Regards

Chris

Answer 1

you'd do something like:

Call jstCheckTableLinks_Full()

But, I normally wouldn't do so as part of any form.  I'd use an AutoExec Macro and make it the 1st thing that get run.  If you can't reconnect the table there is no reason to go forth with launching the db.  Just copy their AutoExec macro and then add a command to open the Splash screen after or switch the whole thing to call a function and code everything there is you prefer VBA over Macros.

Answer 2

I'm going to differ from Daniel. I do use a startup form, but that's because I do some preliminary setup.

Note there are two types of VBA procedures; Functions and Procedures. The major difference is that a Function returns a value so it can be called using:

x=functionname()

However, either can be called using the  Call command:

Call function/procedurename()

However, if you are going to use a macro, it has to be a function, you can't call a procedure from a macro.

Answer 3

Not sure why just using the provided macro that runs the re-link code on start-up is a problem?

The fact that you have some start-up code, or start-up form that checks for some expiry date should not matter in terms of the re-link code running, or not running.

In other words, it likely does not matter some re-link code runs, or does not run? I suppose you could delete or remove the provided Auto Exec macro, and “after” your expiry date code runs, you THEN call the re-link code.

 Anyway, for the sake of curiosity you can simply call the re-linking code with this line of VBA code:

jstCheckTableLinks_Full()

So you could place the above one line of code say after the several lines of code that runs to check the expiry date. As pointed out, since it is a function, then just placing the line of code as per above will  run the re-link code. But perhaps you change your startup form, make a new one etc - then you have to remember to add that re-link function call.  So likely it still rather ok to use the AutoExec macro to run the re-link code since you want it to always run - regardless of what kind of startup code you run in some form.

So calling the re-link routines in your VBA code, or using the provided AutoExec macro to run the re-link routines is an acceptable approach that will not change much in application outcome.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 4

Thank you so much sir for the help , the problem I have is as follows :

(1) If I call the function in question like below it cannot compile it says Not defined compile error .

Option Compare Database

Option Explicit

Private Sub Form_Load()

CalljstCheckTableLinks_Full()

End Sub

**(2)**If I call the function in question like below it 

Option Compare Database

Option Explicit

Private Sub Form_Load()

CalljstCheckTableLinks_Prompt()

End Sub

Then it compiles with No errors , so I do not know what to do on this, could it be I'm missing something on this? The import of JST code was successful there no errors, as at now I can even see both the macro & module separately in my databases , I also ran debug/ compile to ensure that there were no error and for sure it compiled without errors. 

(3) I want also to move the completed database to 2 independent branches with independent local network  (LAN) not WAN , my intention is to put BE on each local server. Now since the splitting of my database was done on 2 separate folders  , that is one containing the FE and the other containing BE , should I copy all the two folders and store them on each target computer  and then copy the BE folder and put it on the local server. Then after that, open the application on that target computer since it will have both folders for FE & BE , then when the prompt comes then direct it to the local sever where the final copied BE will be installed and after successful refreshing I should now delete the BE stored on the target computer because it will now be redundant. Kindly guide on this as well , I will highly appreciate your effort.

Regards

Chris

Answer 5

Yes, take a look at the several posters here who outlined that because the Code you are “using” or “calling” is a function, and NOT a Sub, then you DO NOT place the word “call” in front.

Based on the feedback and suggestions here, then:

Call jstCheckTableLinks_Full()    - WRONG! Will NOT work

jstCheckTableLinks_Full()         - correct – this should work fine

So please do take the time to read the suggestions here – such information is for your benefit.

> I copy all the two folders and store them on each target computer  and then copy the BE folder and put it on the local server.

Well, I don’t see any reason to copy that BE folder to the computer when you want to place it on the server? For what possible reason, idea, or concept would you want to place that BE folder on each computer? The WHOLE idea here of a split system is that you can share ONE BE for everyone on some shared server folder. So unless you can explain why or come up with a good reason, then I can’t see what benefit or outcome you are attempting to achieve by copying the BE folder to each computer?

Could you explain why you think this is a good idea? I am VERY VERY VERY curious here.

I can say for deployment for a new setup, then BEFORE you copy the FE to each computer, is you run it once, and link it to the BE data file on that shared server folder.

The FE when run for the first time will force every user to be prompted for the BE location data file. Such users might not know where that location is (and often they don’t want to care or know anyway). So when you setup a customer’s site, you likely want to run your FE, browse and connect it to the BE accDB, and THEN USE THAT FE copy to deploy to each desktop. The reason for this is now that each user (even on first time run) will not be prompted to browse to the BE location. So you want (if possible) to distribute to each user a correctly linked FE that points to the correct BE.

Of course, if it some far away customer and you don’t have remote ability, then all you can do is provide them with a FE folder that you place on each computer, and then the BE folder that they place and share on their server. Then when each user for the first time launches the application, the user(s) will have to browse to that folder on the server and select the BE data file. This browse + selecting will ONLY occur the first time since the re-linking code on start will find the existing “last” linked BE still in the same location and still functional. So the linking only has to occur one time – after that you only need to “check” if the BE not been moved.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada