Filter Form using VBA, Client did not like my previous Bookmarking solution.

[Phred]

I have two sub forms on one modal form.

Form 1 = Edit_Frame_Frm - Modal form that displays the following two forms side by site. See attached pic.

Form 2 = Edit1_Frm

Form 3 = Edit2_Frm

The user will click a button on Edit1_Frm. The button will pick up the text field "DT_KEY_PIN" and filter the other form Edit2_FRM by the DT_KEY_PIN.

On click:
Dim stDTKEY As String

stDTKEY = Me.DT_KEY_PIN

Forms!Edit_Frame_FRM!Edit2_FRM.Form.FilterOn = False

The above reference between the two forms is correct because I can watch the code turn off the filter on the other form.

Forms!Edit_Frame_FRM!Edit2_FRM.Form.Filter = DT_KEY_PIN = stDTKEY

I've tried all sorts of combinations of syntax on the above line but I just cant get it correct. It should filter Edit2_FRM showing the 2 corresponding DT_KEY_PIN records.

Forms!Edit_Frame_FRM!Edit2_FRM.Form.FilterOn = True

Thanks



Fred

[pbaldy]

It needs to be a string, so presuming a numeric data type:

Forms!Edit_Frame_FRM!Edit2_FRM.Form.Filter = "DT_KEY_PIN = " & stDTKEY

[NTC]

Forms!Edit_Frame_FRM!Edit2_FRM.Form.Filter = DT_KEY_PIN = stDTKEY

I am generally suspicious of syntax that has 2 = = signs without any " " bracketing....are you sure about that syntax??

[Phred]

It needs to be a string, so presuming a numeric data type:

Forms!Edit_Frame_FRM!Edit2_FRM.Form.Filter = "DT_KEY_PIN = " & stDTKEY

I did not understand your opening statement, "It needs to be a string, so presuming a numeric data type:"

It does need to be a string. Hence the dim stDTKEY as string. Although the field contains 14 numbers they sometimes have leading zeros. They are PINs Property Index Numbers so they are never calculated.

I get a data type mismatch from your Forms line above.

[Phred]

I am sure about the syntax in that I am sure it is wrong. That is the statement I am trying to resolve. I have tried every combination I can think of apparently except the correct one. So I just stuck in what I ended up with.

[ssanfu]

My $0.02....

Since "DT_KEY_PIN" is a string, "stDTKEY" needs to be delimited.

I would use:

Forms!Edit_Frame_FRM!Edit2_FRM.Form.Filter = "DT_KEY_PIN = '" & stDTKEY & "'"

Forms!Edit_Frame_FRM!Edit2_FRM.Form.FilterOn = True

[Phred]

Steve: Thanks, that did it. I sure appreciate all the help. So thanks to all. I will mark this closed.

[pbaldy]

I'll get out of the way.

[ssanfu]

@Phred,

Thank Paul. I just copied his post and added the single quotes....

[Phred]

I'll get out of the way.

That thanks was meant for both of you, just poorly presented. You have helped me so many times and so generously as well.

Apologies and Cheers. May your trick-or-treat bag be full.

Fred