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