TempVar Need help on using TempVar in VBA and passing it to Access 2007 Query.

[Phred]

I need to pass a Date from VBA to an Access 2007 query. Very little information exists. Most of it is related to Macros. I need help with it in VBA.



I pull a Date field into my VBA. I need to subtract 1 year from it, and then pass it using a TempVar to an Access 2007 Append query. My understanding is that TempVars are available to Access Queries. My problem is that there is almost no practical information on TempVars. Here is my code so far:

Private Sub BTNNewPYD_Click()
Dim stFFTaxYear As Date 'Current File Tax Year
Dim stVar1 As TempVar
stFFTaxYear = Me.TaxYear
stFFTaxYear = (DateAdd("YYYY", -1, stFFTaxYear))
stVar1 = stFFTaxYear 'It errors out on this line. Error 91 Object Variable or With Block variable not set.

Can anyone help or point me in a practical direction?

Thanks Phred

[NTC]

mmmm well I like to be able to set things up for a more straight forward visual debug.....so I see you have Me.TaxYear which implies there is a form open...
so I would put in a new unbound field into this form named PriorYear and put its record source to be your formula for getting prior year

then you can see the value in the field temporarily for development purpose - and then make it not visible later when you release the app...

visible or not visible your query can call the PriorYear field easily

[June7]

As I understand them, can define TempVars in VBA or macro. http://blogs.msdn.com/b/thirdoffive/...13/570539.aspx

The trick is figuring out where and when. Perhaps in the Open event of a form set to open by default or in an AutoExec macro which will run when the db opens.

[keviny04]

Alternatively, you may also consider building queries with code. That way, you can use your public variables or any variables, as you originally intended. I replied to your other thread as well. E.g.:

Dim q As QueryDef
Set q = DBEngine(0)(0).QueryDefs("My Query Name")
q.SQL = "select * from MyTable where field1=' & MyPublicVar & "'"

[Phred]

NTC, I get what you are saying. Hadn't thought of that. I think this would work. Much easier than TempVars. Iwill try it and let you know.

Thanks. All your guesses on the form were correct. Sorry I didn't have more info for you.

Phred

[ItsMe]

If you are trying to pass the value from one or two fields from a form to a report, you can use Open Args as an alternative to Global Variables.

Here is a copy from a mocrosoft example opening a form and passing literal text via Open Args. The value is Callahan

DoCmd.OpenForm "Employees", acNormal, , , acReadOnly, ,"Callahan"

[Phred]

June7, thanks for the link. I found this code in the article. Do you think this code would function inside a vba module? Would you create a separate vba module to handle this such as in OnLoad. It looks like it operates under its own function. I don't see a Dim. Where did the tv come from? I know it is short for TempVar but why? It looks like a For Next loop that runs three times based on the value "3" set in the code. How is the value placed in the TempVar? The code is to cryptic for me to understand. I am hoping some of you more experienced coders could pick it apart and under stand its operation. My goal is to drop a date (it could be converted into a string) into the TempVar and, as I understand it, I would be able to drop it into a Access 2007 query to run. I would like to work on this on the side. If I can get it running, post it on the forum for others to use.

Function foo()
TempVars("Temp1").Value = 3
For Each tv In TempVars
    MsgBox tv.Name & " = " & CStr(tv.Value)
Next t
End Function

Phred

[June7]

tv is an undeclared variable. Not the best code example because it lacks Dim statements. Without Dim statement the variable defaults to Variant type.

All that function does is list TempVars variables that have been established elsewhere. I don't see that it is relevant to your requirements. The link was offered just for education about TempVars, not any code particularly suited to your needs.