Limiting a parameter query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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]...
 
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.

"(e-mail address removed)"
Click to expand...
 
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.

David said:
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.


"(e-mail address removed)"
Click to expand...
in message
Click to expand...
Click to expand...
 
Back
Top