Filter form by using unbound subform 1

[Xiphiaz]

Hi all.

I have a form with shows a name/address record.
To select a name I first used a ComboBox but that didnt me full satisfaction.

Now I made a unbound subform (data sheet style) with all the names in it.
After a dubbel click on a name in the sub form, should bring up name/address in main form.

But I dont know how to start the filter.

Can someone give me "a hand" on this one?

Thanx
Xiphias

 

[MakeItSo]

You need to things:
1) The Filter statement:
e.g. Me.Parent.Filter="field1='" & filterstring
2) One to activate the Filter:
Me.Parent.FilterOn=True

P.S: For other cases where the filter is not swet from a subform, just leave away the .Parent part.
;-)
Hope this helps,
MakeItSo


Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[TheAceMan1]

How are ya Xiphiaz . . . . .
MakeItSo's post is right on target!

Just as a thought, if you went back to the combobox, just look at the space you would save in the view for other important things! . . . . .

If you like and are having problems with the combobox, we can help you with that as well.


TheAceMan

 

[MakeItSo]

Yepp - and totally mis-spelled. Must have had clumsy fingers yesterday...

 

[Xiphiaz]

Hi MakeItSo and TheAceMan1

Thank you for assistance.
Filter is working fine

The only thing I would like to change is that the subform will keep its focus on the name I dubbel click.
Now it jumps back to the top record.

If you know how to change that...

About the combo box:
If I first open the form then the form was focused on first record but the combo box was empty.
And if I open and close a linked form then the combo box shows the last used name and the form itself shows first record(now that I think about it, that problem will be the requery I use).

The Combo box would save some space, but I sometimes have to quikly look up info and thats more easy with the subform.

Cheers,
Xiphias

 

[MakeItSo]

Hi Xiphias.
That's good news.

I guess you need a record search for that:

Dim rs as recordset,nam as String
nam=Me![Surname]
...
Me.Parent.FilterOn=True

Set rs=Me.RecordsetClone
rs.FindFirst "[Surname]='" & nam & "'"
Me.Bookmark=rs.Bookmark
rs.close
Set rs=Nothing


Have a try with it. I can't guarantee it'll work - but it should. ;-)

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[Xiphiaz]

Hi MakeItSo,

... it doesnt work...


Just to be sure that I did it right:

In the dubbel click event I put this:
---------------------------------
Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_Form__DblClick

Me.Parent.Filter = "Name='" & Me![Name] & "'"
Me.Parent.FilterOn = True

Set rs = Me.RecordsetClone
rs.FindFirst "Name='" & Name & "'"
Me.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing

Exit_Form__DblClick:
Exit Sub

Err_Form__DblClick:
MsgBox Err.Description
Resume Exit_Form__DblClick

End Sub
--------------------------------------

I also tried it with: rs.FindFirst "Name='" & Me![Name] & "'"
But it stay's the same.

Thanx anyhow..

Xiphias

 

[MakeItSo]

I see:

You need to "save" the Name in a variable before you apply the filter, else it is lost!
--> try this instead:
[blue]
Private Sub Form_DblClick(Cancel As Integer)
Dim nam as String, rs as Recordset

On Error GoTo Err_Form__DblClick
nam=Me![Name]
Me.Parent.Filter = "Name='" & Me![Name] & "'"
Me.Parent.FilterOn = True

Set rs = Me.RecordsetClone
rs.FindFirst "[Name]='" & nam & "'"
Me.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing

Exit_Form__DblClick:
Exit Sub

Err_Form__DblClick:
MsgBox Err.Description
Resume Exit_Form__DblClick

End Sub
[/blue]

P.S: For the future: avoid field names like "Name", "Date" a.s.o. They're reserved Words and can cause trouble unless you always use the fields with square brackets around.
;-)

 

[Xiphiaz]

I changed it and now I got a error on:
rs.FindFirst "[Name]='" & nam & "'"

Error is:
Method or data member not found (Error 461)

But I also changed:
Me.Parent.Filter = "Name='" & nam & "'"

And that is working perfect...

CU
Xiphias

 

[MakeItSo]

OK. That's the reference.
If it is still jumping to the first record on the subform, change
Dim rs as recordset
to
Dim rs as DAO.Recordset

If "DAO" does not appear as a valid choice after "as", then you must add DAO reference.
In that case, go to tools-references and add "Microsoft DAO 3.6 objects library" or any other version.

Getting closer...
MakeItSo

 

[Xiphiaz]

YESSSSSSSSSSSSSSSSSSS!!!!!!!!!!

Its working!!

Thank you very much for your paticience!!


have a nice day,
Xiphias

 

[MakeItSo]

A-oooooo-ga!

 

[Xiphiaz]

Hi,

Sorry to bother you again, but I have a simuler question as above:

After pushing a button on my form,a linked form(as dialogbox) opens where you can add a new name/address.
After closing this box I would like to select the new record on the "main"form.

It seems that that works a little different as supscribed above ;-)

Can you help me again

 

[MakeItSo]

Well, if I get this right, it's not too different.
I assume you use this form as a sort of "Do new search"?

You close the form with some sort of OK button, I assume?

Then you need to release the filter of your main form from the button's click event, re-search the corresponding name and re-set the filter:
So you'll have this in the dialog form's button's click event:

Private Sub OKBtn_Click()
Dim nam as String, rs as Recordset

On Error GoTo Err_OKBtn_Click
nam=Me![Name]
Forms![Main Form name].FilterOn=False

Set rs = CurrentDb.OpenRecordset("data source query of main form", DBOpenSnapshot)
rs.FindFirst "[Name]='" & nam & "'"
Forms![Main Form name].Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing

Exit_OKBtn_Click:
    Exit Sub

Err_OKBtn_Click:
    MsgBox Err.Description
    Resume Exit_OKBtn_Click
    
End Sub

Hope this works,
MakeItSo