Dialog Box

[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

 

[Wayne Morgan]

There are a couple of options I can think of.

1) Open the pop-up form using the acDialog Window Mode argument. This will
pause the calling code until you close or hide the form. Instead of closing
it, hide it to continue (Visible = False) then retrieve the value from the
pop-up as the next line in the calling code then close the pop-up. This has
a potential problem if you have more than one form try to call the pop-up at
the same time.

2) Pass the names of the calling form and control in the OpenArgs argument
of the DoCmd.OpenForm call. This way the pop-up will know where to send the
value to.

Both of these could cause problems if you try to open the pop-up form more
than once by calling it from different forms while one form already has it
open. This link will give you some information on working around that.

http://www.mvps.org/access/forms/frm0002.htm
http://members.iinet.net.au/~allenbrowne/ser-35.html