Filter Form

[Sadukar]

Hi All,

This should be easy but I just dont know what to do and I cant find this particular q anywhere.

My form only displys one record at a time and I want to filter it so I can only browse the records that have a "status" of 1 (for example).

I am trying want to filter by a Textbox which contains record field called "status".
I tried:
Me.Filter = "[Texto100] = '1'"
Me.FilterOn = True

But it just stops all records from being displayed.

Do I have to filter the table first or something like that?
Any suggestions would be appreciated.

Thanks for your time
S.

 

[JeremyNYC]

Is the field [Texto100] is bound to a text field? If it's numeric, you'll have to get rid of the single quotes.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Sadukar]

Thanks anyway jeremy
I already got it working.
I did this:
Me.Filter = "[TableName]![FieldName] = '1'"
Me.FilterOn = True

I think I was going about it all wrong.

 

[SAXMAN99]

Sadukar, I also have had this problem, however, I still cannot get this to work, PO# is my field, assets is my table.
i tried what u suggested, when that didnt work, i also tried this.

Me.Filter = "[Assets]![PO#] = " & [cboName]
Me.FilterOn = True

oh yeah, cboname is a combo box, that will look up the values in the field,(to make it more idiot proof)
well, i hope someone can help.
i have also tried:
' Me.Recordset.FindFirst [PO#] = Me![cboName]
' Me.Bookmark = Me.RecordsetClone.Bookmark
' Me.Recordset.FindNext [PO#] = Me![cboName]

and yes the # has to be in the field, this is a very large database(over 4000 records) and changing field names, is something i would like to avoid if possible.
i am using a subform in the table view so i can see multiple records, however i see all of them all the time.

Saxman99

 

[JeremyNYC]

Well, 4000 records is actually a very small database. And it has nothing to do with how hard it is to change the name of a field. You should go get rick fisher's find and replace utility. You can find it with google.

Is PO# a numeric field? If not, use single quotes around the control reference.

Also, it's best to disambiguate your references, by using me!cboName.

If both of those changes were made, it would look like this:
Me.Filter = "[Assets]![PO#] = '" & [cboName] & "'"

Jeremy


==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Sadukar]

Did that work Saxman?

Are you filtering a subform?
4000 records might not be a "big" database but it is still enough to be cautious about. Backup maybe before messing around.

The following is another way to filter. Might seem to make little sense but it works.

If Me.Form.RecordSource = "SELECT * FROM Assets Where PO# = [cboName]" Then
Me.Form.RecordSource = "SELECT * FROM Assets Where PO# = [cboName]"
Else
Me.Form.RecordSource = "SELECT * FROM Assets Where PO# = [cboName]"
End If

But this where you had the other code.

Hope this helps
S.

 

[Kherozen]

Sadukar, I don't maybe my eyes aren't fullly open yet but that code : [tt] If Me.Form.RecordSource = "SELECT * FROM Assets Where PO# = [cboName]" Then
Me.Form.RecordSource = "SELECT * FROM Assets Where PO# = [cboName]"
Else
Me.Form.RecordSource = "SELECT * FROM Assets Where PO# = [cboName]"
End If
[/tt]

could be replace by just :[tt] Me.Form.RecordSource = "SELECT * FROM Assets Where PO# = [cboName]"[/tt]

the idea is that you do the same thing either it's true or false since it's always True or False then you don't have to make a check on it since you will do [tt]Me.Form.RecordSource = "SELECT * FROM Assets Where PO# = [cboName]"[/tt] anyway ... following me?



jul ^_^
"Computer Science is no more about computers than astronomy is about telescopes"
E. W. Dijkstra.

 

[Sadukar]

I was given that code by another member of Tek-Tips. Originaly for a sort but it works the same way for filter. I have never understood it properly.

I understand what you are saying (above) and it makes sense but I cannot get the way you sugested to work.
Have you ever got it to work in this situation?

Does anyone know why it is necessary to use IF and SQL to get this type of filter to work?

s.

 

[Kherozen]

well the "IF" I really don't see why and I personnally never encounter any problem when modifying the recordsource of a form but actualy I always do it from another form
like that
[tt]
Forms![ProjectViewer].RecordSource = stSQL 'set the record source of the to be open form
DoCmd.Close acForm, Me.Name 'closing this window
DoCmd.OpenForm stDocName 'Opening the result in the currentform
[/tt]

but for the current form I suggest you try to do something like that :
Me.Form.RecordSource = "SELECT * FROM Assets Where PO# = [cboName]"
Me.refresh ' that refresh the records


jul ^_^
"Computer Science is no more about computers than astronomy is about telescopes"
E. W. Dijkstra.

 

[Sadukar]

This does not work.

Me.Form.RecordSource = "SELECT * FROM Assets Where PO# = [cboName]"
Me.refresh ' that refresh the records

Have you ever tried to attach it to a button etc on a form. Still seems to need IF etc...