Trying to open subform with Filter value

[mays0515]

Hi,

When a user double clicks on my last_name field, I want a subform to open. The subform should only display the filtered last name data but I get all records displayed instead. Not sure what I am doing wrong - can someone please help?

Below is my code:

Private Sub last_name_DblClick(Cancel As Integer)
 
last_name_Trim = Trim(Me.last_name)
 
DoCmd.OpenForm "subform - employee drill down east", acNormal, , "last_name ='" & last_name_trim & "'", acFormReadOnly, acWindowNormal
 
End Sub

[jimatqsi]

mays0515,
Welcome to Bytes.com.

Are you opening this form anywhere else? Is it possible another event is opening the form without the where clause? Have you tried setting a debug stop at line 5 and verifying the content of last_name_trim?

Also,
1. If last_name_Trim is a local variable it should declared locally. If it's a global variable, it should be identified with a lower case 'g' or some other obvious prefix. We are left to assume it is a string.
2. You should include some error handling to display or log an error message.
3. You are not opening a subform, in spite of the word 'subform' in the name of the form. A subform is a form positioned on a form, not a form opened by a form's code.

Jim

[mays0515]

Hi,

Yes I have tried setting a debug stop at line 5 and verified the last name trim. also, the form is not being opened anywhere else.
1. I have tried running with and without last_name_trim (used me.last_name directly in the openform but had the same results)
2. Hmmm I do not get any error messages - the form does get displayed - would I still need to add some sort of error handling to figure out my issue?
3. You are right - it is just another form being opened - sorry about that

Any other ideas?

Thanks so much for your help!

[jimatqsi]

Is the recordsource a table or query? It would be interesting to make sure it is a query and add that criteria to the query, just to verify the results are actually achieved outside of the form.

Just kind of throwing darts now, I don't see any problem. Oh, what is the value of the last name you tried? Any special characters involved?

Jim

[jimatqsi]

About error handling, you need to make that a habit. It may be there are no errors being encountered in this case and so you're not missing anything. But it is an important habit to establish.

Jim

[mays0515]

I think i found the problem - the "subform - employee drill down east" is a form and within this form, i have a subform that is linked to a query. When i create a "Stand alone" form directly linked to the query, my where conidition works in the OpenForm works. But now, instead of datasheet view being displayed on the double click of the last name field, I get form view - how do i change it to datasheet view?

[jimatqsi]

You can change the default view of the form in the Format tab of the form properties. Will that do?

Jim

[mays0515]

that worked - thanks so much for your help!! Really appreciate it!

[jimatqsi]

Excellent! So glad to help.