A
Antonio
Please forgive the lengthy post...
I just recently followed the online instructions in
the "Microsoft Office Assistance:Using parameters with
queries and reports" document to create a form to supply
parameters to a query. (a dialog box?) This form (Called
Form A) contains two fields - StartDate and End Date and
runs a query/report for monetary totals.
I used vba code provided by Van T. Dinh and Doug Steele
(Microsoft New Groups/General Discussoin/6/8/2004)to
facilitate the entering of the dates in the two fields.
When the two date fields have the focus, and the user
types the letter "T" on their key board, todays date
("Date()")is entered into the field. When the user hits
the "Y" key, yesterdays date ("DateDiff("d", 1, Date)
pops into the field, etc. I wanted to take it one step
further...
When the user hits the "-" key, a seperate form (dialog
box) opens up asking "Enter number of days ago". The user
types 5 into the field and when they hit the "ok" button,
DateDiff("d", 5, Date) is entered into the original
dialog box (Form A).
When the user hits the "+" key, another form opens up
asking "Enter number of days from now"...and works just
like the one above except it adds the number of days
specified to todays date and places it into the field in
Form A.
Here is my question: My original form contains only two
fields (Start Date and End Date), but in order to have
the "-" and "+" capability I described above for both
fields, I had to create a "Enter number of days from now"
and "Enter number of days ago" form for each data field
on the original form. I need to make several more forms
and would like to avoid creating the same "-" and "+"
forms over and over again depending on how many date
fields I have on the Parameter Form. It would be nice to
have just two "_" and "+" forms that, when closed,
automatically send the info to the form at which they
were opend at (or more specific, to the field that had
the focus when they were opend) In this way, I can have
one "-" form and one "+" form, but use them for all of
the future parameter forms I create.
Below is the code I use to enter the dates and open the "-
" and "+" forms (Thanks again to Van and Doug):
Private Sub StartDate_KeyPress(KeyAscii As Integer)
If Chr(KeyAscii) = "y" Then
KeyAscii = 0
Me.StartDate = DateDiff("d", 1, Date)
ElseIf Chr(KeyAscii) = "t" Then
KeyAscii = 0
Me.StartDate = DateAdd("d", 1, Date)
ElseIf Chr(KeyAscii) = "n" Then
KeyAscii = 0
Me.StartDate = Date
ElseIf Chr(KeyAscii) = "-" Then
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "fqryPaymentsStartMinus"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ElseIf Chr(KeyAscii) = "+" Then
stDocName = "fqryPaymentsStartAdd"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End Sub
TIA
I just recently followed the online instructions in
the "Microsoft Office Assistance:Using parameters with
queries and reports" document to create a form to supply
parameters to a query. (a dialog box?) This form (Called
Form A) contains two fields - StartDate and End Date and
runs a query/report for monetary totals.
I used vba code provided by Van T. Dinh and Doug Steele
(Microsoft New Groups/General Discussoin/6/8/2004)to
facilitate the entering of the dates in the two fields.
When the two date fields have the focus, and the user
types the letter "T" on their key board, todays date
("Date()")is entered into the field. When the user hits
the "Y" key, yesterdays date ("DateDiff("d", 1, Date)
pops into the field, etc. I wanted to take it one step
further...
When the user hits the "-" key, a seperate form (dialog
box) opens up asking "Enter number of days ago". The user
types 5 into the field and when they hit the "ok" button,
DateDiff("d", 5, Date) is entered into the original
dialog box (Form A).
When the user hits the "+" key, another form opens up
asking "Enter number of days from now"...and works just
like the one above except it adds the number of days
specified to todays date and places it into the field in
Form A.
Here is my question: My original form contains only two
fields (Start Date and End Date), but in order to have
the "-" and "+" capability I described above for both
fields, I had to create a "Enter number of days from now"
and "Enter number of days ago" form for each data field
on the original form. I need to make several more forms
and would like to avoid creating the same "-" and "+"
forms over and over again depending on how many date
fields I have on the Parameter Form. It would be nice to
have just two "_" and "+" forms that, when closed,
automatically send the info to the form at which they
were opend at (or more specific, to the field that had
the focus when they were opend) In this way, I can have
one "-" form and one "+" form, but use them for all of
the future parameter forms I create.
Below is the code I use to enter the dates and open the "-
" and "+" forms (Thanks again to Van and Doug):
Private Sub StartDate_KeyPress(KeyAscii As Integer)
If Chr(KeyAscii) = "y" Then
KeyAscii = 0
Me.StartDate = DateDiff("d", 1, Date)
ElseIf Chr(KeyAscii) = "t" Then
KeyAscii = 0
Me.StartDate = DateAdd("d", 1, Date)
ElseIf Chr(KeyAscii) = "n" Then
KeyAscii = 0
Me.StartDate = Date
ElseIf Chr(KeyAscii) = "-" Then
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "fqryPaymentsStartMinus"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ElseIf Chr(KeyAscii) = "+" Then
stDocName = "fqryPaymentsStartAdd"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End Sub
TIA