Passing VB variable to Form Where

[FrancieFL]

I have a form that runs a VB function then calls a subform. I need to use the computed VB variable for my filtering in the subform.

Is there a way to do this?

Here is the VB code I have.

NewClientCode = Chr$(39) & rst.Fields(0).Value & Num & Chr$(39)

' Go to the New Client form

DoCmd.OpenForm ("NewClientEntry&quot

I set my Record Source to my SQL Query and the Filter to "[Customer_Code] = Forms!GetInitials!NewClientCode", but it didn't work. "GetInitials" is the form that has runs the above VB code.

Thanks for any help you might be able to give.
Francie

 

[jromeroc]

OPTION A)

Declare NewClientCode as a Public variable in the first form :

Option Compare Database
Public NewClientCode

On the second form reference the variable as :
[Customer_Code] = Form_GetInitials.NewClientCode

OPTION B)
In the first form use the OpenArgs argument when open the second form:

DoCmd.OpenForm "Second", , , , , , NewClientCode

Then in the second form reference the variable as:
[Customer_Code] = me.OpenArgs

 

[FrancieFL]

I tried both methods and don't seem to be able to get it to work. I'm sure it is something I am doing, or failed to mention in the above note.

The process is that I have form one (GetInitials) with a button that calls my VB Public Sub.

Public Sub GetNumber_Click()
NewClientCode = Chr$(39) & rst.Fields(0).Value & Num & Chr$(39)
DoCmd.OpenForm "NewClientEntry"

NewClientEntry has Record Source set to a query that has no filter, and Filter is set to "[Customer_Code] = Form_GetInitials.NewClientEntry".

Any idea what I am doing wrong?
Thanks,
Francie

 

[jromeroc]

You have filter is set to
"[Customer_Code] = Form_GetInitials.NewClientEntry

And must be:
"[Customer_Code] = Form_GetInitials.NewClientCode


and NewClientCode must be declared Public in GetInitials form.