custom dialog box for entering query parameters

  • Thread starter Thread starter Suzanne
  • Start date Start date
S

Suzanne

I thought this was a simple thing to do, but I have spent
hours in research and can't figure it out. The Access
help files have not helped.

When a user runs a report, I want a pop up dialog box to
request three things: last name, first name and semester
name and then to print the report based on those three
parameters. I know how to do it in the query itself, but
it pops up three boxes and I would like it just to be one
box. I know it requires designing a form for the pop up
and attaching it somehow to the to the report, but I just
can't figure it out. Are there some step by step
instructions somewhere?

Suzanne
 
Suzanne said:
I thought this was a simple thing to do, but I have spent
hours in research and can't figure it out. The Access
help files have not helped.

When a user runs a report, I want a pop up dialog box to
request three things: last name, first name and semester
name and then to print the report based on those three
parameters. I know how to do it in the query itself, but
it pops up three boxes and I would like it just to be one
box. I know it requires designing a form for the pop up
and attaching it somehow to the to the report, but I just
can't figure it out. Are there some step by step
instructions somewhere?

Suzanne
Click to expand...

You first need to create an unbound form with a text box for each
parameter. Then add an OK button and a Cancel button if desired. In
the OnClick event of the button, enter "Me.Visible = False" (without
the "") so that the query can see the parameters while hiding the
form.
In the criteira of each query field, enter "Forms!FormName!Parameter"
(without the "") "FormName" will be the name of your form and
"Parameter" will be "Last Name", "First Name" or "Semester Name"
respectively.
In the Open event of the Report place code to open the form:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "FormName", , , , , acDialog
If Not IsLoaded("FormName") Then
Cancel = True
End If
End Sub

In the Close event of the Report, place code:
Private Sub Report_Close()
DoCmd.Close acForm, "Mileage Report Form"

End Sub

You will need the following code for the "If Not IsLoaded":

Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or
Datasheet view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function
Place this code in a new module.

While no expert, this was posted in answer to a similar question of
mine and I just thought I would attempt to contribute after recieving
so much help here.

HTH
Justin
 
Dear Justin,
This was exactly one of my problems in a medical report database, as I have
a report form and its items is patient ID, Name, Type of procedure, and I
wanted to create a lookup formto get the patient report by entering one or
more of these data, and your method waz amazing in getting this report, yet
it doen't work on forms or there is specific correction to work on forms, I
mean can I open the form to edit specific chosen records??
 
Why don't you just place a button on the form that lets you edit that
particular name.

You can then search for the student name. At this point you could review any
information that needs to be changed. With a button on that student form,
you click it, and ONLY the one student would be sent to the printer.

Your code behind the print report button could be:


dim strWhere as string

me.refresh
docmd.openreport "your report",acViewPreview,,"id = " & me.id
 
Back
Top