Global Variable disappears on Event Sub Exit

[pledbetter]

I created a Public variable:

Option Compare Database
Option Explicit


Public NumApplicantID As Integer

In the Current Event of a form, I set the variable:

Sub Form_Current()    

NumApplicantID = Me.ApplID  ' Set the Applicant ID

End Sub

Upon exit from this sub, the Public variable is no longer in scope and disappears. What am I doing wrong? I need to use that variable in another Event on a Button control on this same form.
I expect that it will be visible across the entire application.

[pbaldy]

Where is it declared? I would have it in a standard module, not behind a form.

[pledbetter]

It's declared in a form. It just seems to me a Public variable is a Public variable, irrespective of where it is declared. I'll move it to a standard module and see what happens.

[Welshgasman]

Works for me?
Public in Form module and MSGBOX in Current event?

[pledbetter]

Created a separate module. I got past where I was. However, in the same form, but in a button OnClick event, when trying to execute

DoCmd.RunSQL "insert into [tblFormerEmployers] ([numApplID]) values(numApplicantID);"

(Shortened for brevity but still syntactically correct)

It asks for a "Parameter" "numApplicantID", as if that Public variable is not visible at all in this OnClick event.

[pbaldy]

You have to concatenate the value. Since it's numeric you don't need delimiters:

DoCmd.RunSQL "insert into [tblFormerEmployers] ([numApplID]) values(" & numApplicantID & ");"

[pbaldy]

By the way, if that doesn't work you may want to use a variable for the SQL so you can see what it's resolving to:

https://www.baldyweb.com/ImmediateWindow.htm

[Welshgasman]

NOT syntactilly correct at all.
You need to concatenate the value into the sql statement.

[Edgar]

Tempvars is pretty stable, I think you would be safe using it instead of a global variable.

[Welshgasman]

Tempvars is pretty stable, I think you would be safe using it instead of a global variable.

Still need to concatenate correctly though?

[pledbetter]

You have to concatenate the value. Since it's numeric you don't need delimiters:

DoCmd.RunSQL "insert into [tblFormerEmployers] ([numApplID]) values(" & numApplicantID & ");"

That was it! I keep forgetting that non-intuitive syntax. Thanks!

[Welshgasman]

That was it! I keep forgetting that non-intuitive syntax. Thanks!

A bad workman always blames his tools.

[pledbetter]

Oooooh

[pbaldy]

That was it! I keep forgetting that non-intuitive syntax. Thanks!

Happy to help! I would suggest it is intuitive once you understand what's happening. You're building an SQL string to send to the database engine. That engine has no knowledge of what the variable might contain, hence the parameter prompt.