Limiting a parameter query

[bmersereau]

I have a parameter query opens a form. If you enter invalid data into the
input box a blamk form opens up. I don't want that to happen. If invalid
data is entered and the query run and there is not matching result I want the
query to return a message that says to check the number that was entered.
How do I do that?

 

[Allen Browne]

The query parameters don't have enough flexibility to do what you want.

How about placing an unbound text box at the top of your form?
When the user enters a value, filter the form to only records that match.
You can then remove the parameter from the query.

This kind of thing:

Private Sub txtFindCity_AfterUpdate()
Dim strWhere As String

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

If IsNull(Me.txtFindCity) Then
Me.FilterOn = False 'show all records again.
Else
strWhere ="[City] = """ & Me.txtFindCity & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub


Note: remove the extra quotes if the field is a Number field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...

 

[David]

Allen,

Can you use this same approach to search for a text value
in any part of the field? Just add a few *'s and &'s?
Similar to the CTRL+F function?

-----Original Message-----
The query parameters don't have enough flexibility to do what you want.

How about placing an unbound text box at the top of your form?
When the user enters a value, filter the form to only records that match.
You can then remove the parameter from the query.

This kind of thing:

Private Sub txtFindCity_AfterUpdate()
Dim strWhere As String

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

If IsNull(Me.txtFindCity) Then
Me.FilterOn = False 'show all records again.
Else
strWhere ="[City] = """ & Me.txtFindCity & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub


Note: remove the extra quotes if the field is a Number field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"[email protected]"

 

[Allen Browne]

Sure. Use the Like operator, with * as the wildcard before and after the
text:

strWhere = "[City] Like ""*" & Me.txtFindCity & "*"""

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Can you use this same approach to search for a text value
in any part of the field? Just add a few *'s and &'s?
Similar to the CTRL+F function?

-----Original Message-----
The query parameters don't have enough flexibility to do what you want.

How about placing an unbound text box at the top of your form?
When the user enters a value, filter the form to only records that match.
You can then remove the parameter from the query.

This kind of thing:

Private Sub txtFindCity_AfterUpdate()
Dim strWhere As String

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

If IsNull(Me.txtFindCity) Then
Me.FilterOn = False 'show all records again.
Else
strWhere ="[City] = """ & Me.txtFindCity & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub


Note: remove the extra quotes if the field is a Number field.


"[email protected]"

in message