Prev: What is equivalent to "acSpreadsheetTypeExcel9" in 2007 i.e .xlsx
Next: looking for a way around a write conflict...
From: Steve Stad on 21 Apr 2010 10:41
I have a button on a form to open a report based on a parameter query. The
button activates the parameter query and the parameter msgbox says enter MIL
or CIV. I would like to ensure the user spells MIL or CIV correctly so the
parameter query runs correctly everytime. The current button code is below.
Can you tell me how to add/edit or redo code to force user to spell MIL or
CIV correctly. For example with if statement ..If input box <> "CIV" and
inputbox <> "MIL" then msgbox "You Must spell MIL or CIV correctly"

Private Sub MILCIVPercentCompRep_Click()
On Error GoTo Err_MILCIVPercentCompRep_Click
Dim stDocName As String
stDocName = "Rep_CompletenessMIL&CIVPerc"
DoCmd.OpenReport stDocName, acPreview
Exit_MILCIVPercentCompRep_Click:
Exit Sub
Err_MILCIVPercentCompRep_Click:
MsgBox Err.Description
Resume Exit_MILCIVPercentCompRep_Click
End Sub

From: Daryl S on 21 Apr 2010 11:11
Steve -

You could put an option group on the form next to the report button that
lets them only choose MIL or CIV. Then for the parameter query, change it to
pull the value selected in the option group from the form.

--
Daryl S


"Steve Stad" wrote:

> I have a button on a form to open a report based on a parameter query. The
> button activates the parameter query and the parameter msgbox says enter MIL
> or CIV. I would like to ensure the user spells MIL or CIV correctly so the
> parameter query runs correctly everytime. The current button code is below.
> Can you tell me how to add/edit or redo code to force user to spell MIL or
> CIV correctly. For example with if statement ..If input box <> "CIV" and
> inputbox <> "MIL" then msgbox "You Must spell MIL or CIV correctly"
>
> Private Sub MILCIVPercentCompRep_Click()
> On Error GoTo Err_MILCIVPercentCompRep_Click
> Dim stDocName As String
> stDocName = "Rep_CompletenessMIL&CIVPerc"
> DoCmd.OpenReport stDocName, acPreview
> Exit_MILCIVPercentCompRep_Click:
> Exit Sub
> Err_MILCIVPercentCompRep_Click:
> MsgBox Err.Description
> Resume Exit_MILCIVPercentCompRep_Click
> End Sub
>
From: Steve Stad on 21 Apr 2010 11:34
Thanks Daryl - that is one solution. But sometimes I run the query from the
query design window and prefer to type in MIL or CIV in the parameter window.
Can I overide the CIV MIL in the option group on the form if I run it from
w/i the Query Design mode. The idea is to prevent fat fingering and typing
CVI which causes the parameter query to run nothing or nulls.

"Daryl S" wrote:
> Steve -
> You could put an option group on the form next to the report button that
> lets them only choose MIL or CIV. Then for the parameter query, change it to
> pull the value selected in the option group from the form.
>
> --
> Daryl S
>
>
> "Steve Stad" wrote:
>
> > I have a button on a form to open a report based on a parameter query. The
> > button activates the parameter query and the parameter msgbox says enter MIL
> > or CIV. I would like to ensure the user spells MIL or CIV correctly so the
> > parameter query runs correctly everytime. The current button code is below.
> > Can you tell me how to add/edit or redo code to force user to spell MIL or
> > CIV correctly. For example with if statement ..If input box <> "CIV" and
> > inputbox <> "MIL" then msgbox "You Must spell MIL or CIV correctly"
> >
> > Private Sub MILCIVPercentCompRep_Click()
> > On Error GoTo Err_MILCIVPercentCompRep_Click
> > Dim stDocName As String
> > stDocName = "Rep_CompletenessMIL&CIVPerc"
> > DoCmd.OpenReport stDocName, acPreview
> > Exit_MILCIVPercentCompRep_Click:
> > Exit Sub
> > Err_MILCIVPercentCompRep_Click:
> > MsgBox Err.Description
> > Resume Exit_MILCIVPercentCompRep_Click
> > End Sub
> >
From: John W. Vinson on 21 Apr 2010 12:29
On Wed, 21 Apr 2010 07:41:01 -0700, Steve Stad
<SteveStad(a)discussions.microsoft.com> wrote:

>I have a button on a form to open a report based on a parameter query. The
>button activates the parameter query and the parameter msgbox says enter MIL
>or CIV.

Rather than the prompt [Enter MIL or CIV] I'd strongly suggest using an
unbound combo box on your Form, and using

=[Forms]![YourFormName]![comboboxname]

as a parameter. If the form isn't open you'll get prompted.
--

John W. Vinson [MVP]
From: Steve Stad on 21 Apr 2010 13:41
Thanks guys. I added the [FORMS]![frm_Completeness]![Combo14] in both
underlying queries and it works great. If the form is open the form controls
the qrys and if the form is closed I can over ride and type in the MIL or
CIV.

"John W. Vinson" wrote:

> On Wed, 21 Apr 2010 07:41:01 -0700, Steve Stad
> <SteveStad(a)discussions.microsoft.com> wrote:
>
> >I have a button on a form to open a report based on a parameter query. The
> >button activates the parameter query and the parameter msgbox says enter MIL
> >or CIV.
>
> Rather than the prompt [Enter MIL or CIV] I'd strongly suggest using an
> unbound combo box on your Form, and using
>
> =[Forms]![YourFormName]![comboboxname]
>
> as a parameter. If the form isn't open you'll get prompted.
> --
>
> John W. Vinson [MVP]
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: What is equivalent to "acSpreadsheetTypeExcel9" in 2007 i.e .xlsx
Next: looking for a way around a write conflict...