Help w/ Filter

[Brian]

Hello all,

I have a popup modal form that I use to search with. It has an unbound
textbox on it. I have users fill in their record number, then I want
the popup to close and filter a subform on the page on the value passed
in the textbox. Here's my code, I'm having problems...

If txtRecordNumber.Value = "" Or txtRecordNumber.Value = 0 Then
'value was null, so tell user
DisplayMessage ("Some Arbitrary Error Message.")
Else
'test to make sure value is num

'set search criteria
Dim criteria As String
criteria = "[Recno]=" & Me!txtRecordNumber.Value

'send value back to form for filtering
Forms("mainFrame").subFormName.Form.Filter = criteria
Forms("mainFrame").subFormName.Form.FilterOn = True

'update the data in underlying form
Forms("mainFrame").sbfPrimaryElements.Form.Requery

'close the search form
DoCmd.Close acForm, "mySearch", acSaveNo
End If

criteria is ending up to be "[Recno]=", it's not accepting the value of
the textbox?

Can I get some assistance here?

 

[chris]

Check Help for the difference between a text box's Value
and Text properties.

You need to use the Text property here, and to do that you
will need to set the focus to the textbox (bless access).

 

[Allen Browne]

What event are you using to fire this code?

If focus is still in the text box (e.g. in its Change event), the Text
property will not have been converted to the Value proeprty yet. If that's
what is happening, you could solve the problem by adding Ok and Cancel
buttons to your dialog form. Put the code into the Click event procedure of
your Ok button. When the focus moves to the button, the Value of the text
box will be updated.

You may also need to test for null:
If IsNull(Me.txtRecordNumber.Value) Then

 

[Brian]

What event are you using to fire this code?

If focus is still in the text box (e.g. in its Change event), the Text
property will not have been converted to the Value proeprty yet. If that's
what is happening, you could solve the problem by adding Ok and Cancel
buttons to your dialog form. Put the code into the Click event procedure of
your Ok button. When the focus moves to the button, the Value of the text
box will be updated.

You may also need to test for null:
If IsNull(Me.txtRecordNumber.Value) Then

I'm using the _Click() event to fire off the code. I've got a custom
button for submit.

 

[Allen Browne]

Try testing for IsNull()?

 

[Emilia Maxim]

I have a popup modal form that I use to search with. It has an unbound
textbox on it. I have users fill in their record number, then I want
the popup to close and filter a subform on the page on the value passed
in the textbox. Here's my code, I'm having problems...

If txtRecordNumber.Value = "" Or txtRecordNumber.Value = 0 Then

If the control is left empty, it has the "value" Null, not an empty
string. So a better check would be with the function Nz which returns
either the value of the control or the given substitute if the control
is left empty. The following comparison is true for either control
left empty or user entered 0:

If Nz(Me!txtRecordNumber, 0) = 0 Then

Else
'test to make sure value is num

This test would look like:

If Not IsNumeric(Me!txtRecordNumber) Then
MsgBox "It's not a number", vbOKOnly
Else

'set search criteria
Dim criteria As String
criteria = "[Recno]=" & Me!txtRecordNumber.Value

The Value property returns the last saved value of a control. As the
text box is not bound, there is no last saved value. IOW Value returns
Null in this case.
You would need instead the Text property. Which again being the
default property can be left out:

criteria = "[Recno]=" & Me!txtRecordNumber

'send value back to form for filtering
Forms("mainFrame").subFormName.Form.Filter = criteria
Forms("mainFrame").subFormName.Form.FilterOn = True

'update the data in underlying form
Forms("mainFrame").sbfPrimaryElements.Form.Requery

Is sbfPrimaryElements the underlying form of the subform control? If
so, the above statement is not correct. You need the name of the
_subform control_. Plus there's no need for Requery when applying a
filter.

And I do hope the record numbers have some meaning for the user.
Forcing them to remember meaningless autonumbers is the worst case for
searching. Maybe you have some often used criteria which you could
display in an unbound combo on the search form.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de