asked on

Customizing Prompt on Parameter Box for MS Access Query

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

ASKER CERTIFIED SOLUTION

irudyk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mildurait

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

pilk123

ASKER

Worked perfectly for the Parameter Box. Thanks!