We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Customizing Prompt on Parameter Box for MS Access Query

Medium Priority
819 Views
Last Modified: 2013-11-25
I have a table in which one of the fields is a short date.  I've created a delete query to drop out records before and after a "begin date" and "end date" input by the user in a parameter box.

To keep unnecessary errors to a minimum, I'd like to be able to instruct the user in the Parameter Box as to the correct date format to use:  eg, 01/01/2009

I suspect this requires VBA coding.  If so, how is such a thing accomplished?

The VBA coding behind the macro button to run my delete query is shown below, as well as a screenshot of what the parameter boxes looks like.

Thanks!
Private Sub Macro___Delete_Qry_for_Period_of_Review_Click()
On Error GoTo Err_Macro___Delete_Qry_for_Period_of_Review_Click
DoCmd.SetWarnings False
    Dim stDocName As String
 
    stDocName = "Delete 4 Period of Review 2-1-09"
    DoCmd.RunMacro stDocName
 
Exit_Macro___Delete_Qry_for_Period_of_Re:
    Exit Sub
 
Err_Macro___Delete_Qry_for_Period_of_Review_Click:
    MsgBox Err.Description
    Resume Exit_Macro___Delete_Qry_for_Period_of_Re
    
End Sub

Open in new window

Parameter-Box.JPG
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008
Commented:
In your delete query you've defined a parameter [Enter Begin Date] and probable one called [Enter End Date]
Try renaming the parameters to something like [Enter Begin Date (format as MM/DD/YYYY)] and [Enter End Date (format as MM/DD/YYYY)]

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Commented:
You could try running the docmd.RunSQL Method which would not require a delete query at all.

1) Add a textbox on your form namext txtDateCriteria with the appropriate date format set, so that the textbox will only accept a valid date entry.
b) Add a button called cmdDelete
c) Tweek the code below and linking it to the cmdDelete button's click method.

Good Luck
cmdDelete_Click()
   If isnull(me.txtDateCriteria)=false then
       Dim dDate as date    
       dDate = me.txtDateCriteria
       Dim sql as string
       sql = "DELETE * FROM [tablename] WHERE [datefield]=#" & Format(DateValue,"MM/DD/YYYY") & "#"
       DoCmd.SetWarnings False
       DoCmd.RunSql(sql)
       DoCmd.SetWarnings True
   End If
End Sub

Open in new window

Author

Commented:
Worked perfectly for the Parameter Box.  Thanks!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.