Results 1 to 6 of 6
  1. #1
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Pass VBA variable to Public Variable and pass to Access 2007 Append Query attached to a form.

    Access 2007 front end SQL Server 2008 back end.



    I have never used or created a public variable that can be grabbed by an Access Query. In this case my code picks up the Current Tax year on the form. Then, if they pick Historical tax date it creates it as the code shows with -1. Here is what I have picked up so far. I know the code is not finished. I am just trying to get the Public variable working and expose it to the Append Query. I've looked at a lot of postings but I just don't get it.

    Code:
    Private Sub BTNNewPYD_Click()
    Dim stFFTaxYear As Date 'Current File Tax Year'
    Dim stHTaxYear1 As Date 'Historical Tax Year = Current -1 year
    Dim stFTaxYear1 As Date 'Future Tax Year = Current +1
    
    Dim vHdate As Date 'vHdate = historical date I want to expose to Access query.
    
    stFFTaxYear = Me.TaxYear
    stHTaxYear1 = (DateAdd("YYYY", -1, stFFTaxYear))
    stFTaxYear1 = (DateAdd("YYYY", 1, stFFTaxYear))
    Select Case MsgBox("Do you wish to add a new historical tax year record?" _
                       & vbCrLf & "" _
                       & vbCrLf & "Your current tax year is: " & stFFTaxYear & "" _
                       & vbCrLf & "" _
                       & vbCrLf & "Your historical tax year will be:" & stHTaxYear1 & "" _
                       & vbCrLf & "" _
                       & vbCrLf & "Click Ok to add or click Cancel to exit." _
                       , vbOKCancel Or vbInformation Or vbDefaultButton1, "Add Historical Tax Year")
        Case vbOK
        
    'HERE IS WHERE I GET STUCK. I HAVE NEVER DONE THIS BEFORE.
    
    Public Function GetvHdate() As Date
    GetvHdate = vHdate
    End Function
     
    'add new PYD record
        
        Case vbCancel
    
    Call MsgBox("No record has been added.", vbInformation Or vbDefaultButton1, "Record Canceled")
    Exit Function
    End Select
    End Function
    Can someone show me a practical example of how my code should look? I need to use this capability in a number of areas.

    Thanks Phred

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want variable available to any module of the db, must be declared in heading of a general module. vHDate is not declared in header. I also don't see any code that sets value of vHDate.

    Queries cannot grab VBA public declared variables or constants. They can reference a custom function that returns value of the variable. Also, queries can refer to TempVars.

    Issue with public variables is that when code execution is interrupted, variable loses value - can be a real frustration when debugging code. TempVars do not lose value.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    TempVAR is this correct so far

    Code:
    Private Sub BTNNewPYD_Click()
    
    Dim stFFTaxYear As Date 'Current File Tax Year'
    
    Dim stVar1 as String 'Where and how do I identify this as a TempVar
    
    stFFTaxYear = Me.TaxYear 'The date on the form I want to grab and subtract a year.
    
    stVar1 = (DateAdd("YYYY", -1, stFFTaxYear))
    
    Should it be:
    
    dim stVar1 as TempVar ?
    Having problem finding simple explanation of how to use TempVar.

    Where do I actually dim this as a TempVar?

    All the books show examples for Macros. One book says to insert into the query

    [TempVars]![<name of temporary variables>] Would the name be "stVar1"?
    Last edited by Phred; 04-27-2015 at 02:14 PM. Reason: more questions

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I've never used TempVars, just know about them.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    I'm going to close this out and start a new post looking for information on TempVar.

    Thanks June7

  6. #6
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Apr 2015
    Posts
    128
    Consider building queries with code. That way, you can use your public variables. E.g.:

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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-10-2012, 02:23 PM
  2. Replies: 4
    Last Post: 01-03-2012, 08:11 PM
  3. Replies: 15
    Last Post: 04-21-2011, 02:50 PM
  4. Pass Variable Values From One Form to Another
    By Nokia N93 in forum Forms
    Replies: 3
    Last Post: 03-07-2011, 11:47 AM
  5. VBA Pass-through Query with Variable
    By smaumau in forum Programming
    Replies: 0
    Last Post: 12-06-2010, 09:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums