Query Parameter Form

[Tony Williams]

I have a form which has 3 unbound controls which are used as parameters for
a query. When the user clicks a command button the query is run and the
results appear in a form. One of the unbound controls is a text box and the
other two are used for a start date and end date. At the moment the user has
to input the date parameters and if they don't they get a message asking
them to do so. However the users now want the option for the query to run
either with the parameters or with the date fields empty and the query
returns all the records based on the data in the text box. How do I do that?
Here is my code at the moment, I realise the msgbox code becomes superfluous
but I was thinking of replacing them with a message saying "You haven't
filled in any dates so all records will be shown Click OK or CANCEL"

Any help would be appreciated
Tony Williams

Private Sub cmdFindDocs_Click()
On Error GoTo Err_cmdFindDocs_Click
'Me.Visible = False
Dim stDocName As String
stDocName = "frmDocumentName"
If IsNull(Me.finddocnametxt) Then
MsgBox "Please enter Document Name", vbExclamation, "Enter Document
Name"
Me.finddocnametxt.SetFocus
Exit Sub
ElseIf IsNull(Me.StartDatetxt) Then
MsgBox "Please enter Start Date", vbExclamation, "Enter Start Date"
Me.StartDatetxt.SetFocus
Exit Sub
ElseIf IsNull(Me.EndDatetxt) Then
MsgBox "Please enter End Date", vbExclamation, "Enter End Date"
Me.EndDatetxt.SetFocus
Exit Sub
Else
DoCmd.Minimize
DoCmd.OpenForm stDocName, , , , , acHidden
If Forms(stDocName).RecordsetClone.RecordCount = 0 Then
MsgBox "Sorry there no records that match this criteria.", _
vbExclamation, "No records found"
DoCmd.Close acForm, stDocName
Me.SetFocus
DoCmd.Restore

Else
Forms(stDocName).Visible = True
Forms!frmDocumentName!NameParametertxt = Me.finddocnametxt
End If

End If


Exit_cmdFindDocs_Click:
Exit Sub

Err_cmdFindDocs_Click:
MsgBox Err.Description
Resume Exit_cmdFindDocs_Click
End Sub

 

[Pavel Romashkin]

Try this:

ElseIf IsNull(Me.StartDatetxt) Then
Me.StartDatetxt = "1/1/1900"
ElseIf IsNull(Me.EndDatetxt) Then
Me.EndDatetxt = "1/1/2090"
Else

This simply relaxes the query limits beyond anything you may
realistically have in the database.
Pavel

 

[Tony Williams]

Thanks Pavel that's a good idea. I don't think I'll be around in 2090 to
worry about the users having problems then!!!!
Tony

 

[Steve Schapel]

Tony,

First of all, remove the date criteria from the query, then try this...

Private Sub cmdFindDocs_Click()
On Error GoTo Err_cmdFindDocs_Click
'Me.Visible = False
Dim stDocName As String
Dim stCrit As String
stDocName = "frmDocumentName"
If IsNull(Me.finddocnametxt) Then
MsgBox "Please enter Document Name", vbExclamation, "Enter
Document Name"
Me.finddocnametxt.SetFocus
Else
If Not IsNull(Me.StartDatetxt + Me.EndDatetxt) Then
stCrit = "[YourDateField] Between " & CLng(Me.StartDatetxt) &
" And " & CLng(Me.StartDatetxt)
End If
DoCmd.OpenForm stDocName, , , stCrit, , acHidden
...

 

[Tony Williams]

Thanks Steve I'll try that tomorrow, I'm in the UK and it's 2030 so I think
I've had a long day my brain has gone stale so I'll try in the morning when
I'm fresh!!!
Tony

Tony,

First of all, remove the date criteria from the query, then try this...

Private Sub cmdFindDocs_Click()
On Error GoTo Err_cmdFindDocs_Click
'Me.Visible = False
Dim stDocName As String
Dim stCrit As String
stDocName = "frmDocumentName"
If IsNull(Me.finddocnametxt) Then
MsgBox "Please enter Document Name", vbExclamation, "Enter
Document Name"
Me.finddocnametxt.SetFocus
Else
If Not IsNull(Me.StartDatetxt + Me.EndDatetxt) Then
stCrit = "[YourDateField] Between " & CLng(Me.StartDatetxt) &
" And " & CLng(Me.StartDatetxt)
End If
DoCmd.OpenForm stDocName, , , stCrit, , acHidden
...

--
Steve Schapel, Microsoft Access MVP

I have a form which has 3 unbound controls which are used as parameters for
a query. When the user clicks a command button the query is run and the
results appear in a form. One of the unbound controls is a text box and the
other two are used for a start date and end date. At the moment the user has
to input the date parameters and if they don't they get a message asking
them to do so. However the users now want the option for the query to run
either with the parameters or with the date fields empty and the query
returns all the records based on the data in the text box. How do I do that?
Here is my code at the moment, I realise the msgbox code becomes superfluous
but I was thinking of replacing them with a message saying "You haven't
filled in any dates so all records will be shown Click OK or CANCEL"

Any help would be appreciated
Tony Williams

Private Sub cmdFindDocs_Click()
On Error GoTo Err_cmdFindDocs_Click
'Me.Visible = False
Dim stDocName As String
stDocName = "frmDocumentName"
If IsNull(Me.finddocnametxt) Then
MsgBox "Please enter Document Name", vbExclamation, "Enter Document
Name"
Me.finddocnametxt.SetFocus
Exit Sub
ElseIf IsNull(Me.StartDatetxt) Then
MsgBox "Please enter Start Date", vbExclamation, "Enter Start Date"
Me.StartDatetxt.SetFocus
Exit Sub
ElseIf IsNull(Me.EndDatetxt) Then
MsgBox "Please enter End Date", vbExclamation, "Enter End Date"
Me.EndDatetxt.SetFocus
Exit Sub
Else
DoCmd.Minimize
DoCmd.OpenForm stDocName, , , , , acHidden
If Forms(stDocName).RecordsetClone.RecordCount = 0 Then
MsgBox "Sorry there no records that match this criteria.", _
vbExclamation, "No records found"
DoCmd.Close acForm, stDocName
Me.SetFocus
DoCmd.Restore

Else
Forms(stDocName).Visible = True
Forms!frmDocumentName!NameParametertxt = Me.finddocnametxt
End If

End If


Exit_cmdFindDocs_Click:
Exit Sub

Err_cmdFindDocs_Click:
MsgBox Err.Description
Resume Exit_cmdFindDocs_Click
End Sub