How to use AND with "WHERE" in OpenForm... like (idA=Me.idA) and (idB=Me.idB)

[MyEmailList]

How do you specify two conditions in the where clause in a
DoCmd.OpenForm instruction ??

So the "Where part of.... DoCmd.OpenForm "AltForm_A"

would look something like, , ,

"[idA] AND [idB] = " & (Me.idA) AND (Me.SubForm_B.idB)

----- Below is my simple setup ---- showing what I'm trying to do.

1 - I have 2 tables...tblA and tblB

2 - While looking at... MainForm_A with SubForm_B

3 - Double click on a record in SubForm_B

4 - and open AltForm_A with AltSubForm_B - a form with a different
view of the same data

Thanks for any help with this.

Mel

 

[Rick Brandt]

How do you specify two conditions in the where clause in a
DoCmd.OpenForm instruction ??

So the "Where part of.... DoCmd.OpenForm "AltForm_A"

would look something like, , ,

"[idA] AND [idB] = " & (Me.idA) AND (Me.SubForm_B.idB)

Assuming all values are numeric...

"[idA] = " & Me.idA & " AND [idB] = " & Me.SubformControlName.Form.idB

 

[MyEmailList]

Rick,

Thanks & I will try this after lunch but a question...

Isn't the LEFT hand side of the equal sign stuff about the form that
is GOING to be opened ??

(which would have altready been has already been defined in the
DoCmd.OpenForm statement)

Would "Me" in "Me.idA" work in this case?

I get confused about when "Me" will work. I thought it would refer to
the form that is already open... the form with the code.

I'll let you know after lunch how it goes.

Thanks again.

Mel

 

[Rick Brandt]

Rick,

Thanks & I will try this after lunch but a question...

Isn't the LEFT hand side of the equal sign stuff about the form that
is GOING to be opened ??

(which would have altready been has already been defined in the
DoCmd.OpenForm statement)

Would "Me" in "Me.idA" work in this case?

I get confused about when "Me" will work. I thought it would refer to
the form that is already open... the form with the code.

I'll let you know after lunch how it goes.

Well, YOU used the ME reference. I just moved it. You are correct that you
cannot use that to refer to something on the form being opened. That will
always refer to the form running the code.

 

[John W. Vinson]

Rick,

Thanks & I will try this after lunch but a question...

Isn't the LEFT hand side of the equal sign stuff about the form that
is GOING to be opened ??

No. The entire string needs to be a valid SQL WHERE clause without the word
WHERE. You're building it up from pieces; for instance, in

"[idA] = " & Me.idA & " AND [idB] = " & Me.SubformControlName.Form.idB

if the control named idA contains the value 317, and the subform control named
idB contains 85, the WhereCondition argument becomes

[idA] = 317 AND [idB] = 85

When the form being opened creates its recordsource, it uses this expression
to select the record or records which match that criterion.

John W. Vinson [MVP]

 

[Mel]

Well guys... I'm still lost/confused and don't have my one line of
code working yet.

I've Googled & read about bangs and dots untill my eyes are rolling
back in my head.

I wonder if I could ask once again for someone to read my original
question in this thread. I know my code is incorrect there.
Reading "my simple setup" may illustrate the question better...

I want to double click on a sub-form item and open another form...
which has fewer fields of the original form visable
and more fields of the double clicked sub form item visiable.

I just can't get the Where part of the DoCmd.OpenForm written
properly.

thanks for any help.

Mel

 

[Rick Brandt]

Well guys... I'm still lost/confused and don't have my one line of
code working yet.

I've Googled & read about bangs and dots untill my eyes are rolling
back in my head.

I wonder if I could ask once again for someone to read my original
question in this thread. I know my code is incorrect there.
Reading "my simple setup" may illustrate the question better...

I want to double click on a sub-form item and open another form...
which has fewer fields of the original form visable
and more fields of the double clicked sub form item visiable.

I just can't get the Where part of the DoCmd.OpenForm written
properly.

thanks for any help.

Mel

Okay I missed that you are double-clicking in the subform. That means "ME"
is now referring to the subform and something like this is what you need...

"[idB] = " & Me.idB & " AND [idA] = " & Me.Parent.idA

Now...this assumes that both of those fields exist in tableA. If only idA
exists in tableA then all you can do is filter the main form you are opening
on idA. The WHERE clause of the OpenForm method can only filter the form it
is opening. It cannot filter the subform of the form you are opening. In
that case all you would have is...

"[idA] = " & Me.Parent.idA

If you need to filter the subform on the idB that you double-clicked on then
you can pass that value in the OpenArgs argument (the final argument of the
OpenForm method). Then the form you are opening can use that value in the
code of its Open event to apply a filter to its subform. Get the main form
filter working first and then if you need help with the subform filter post
back.

 

[Mel]

Thanks Rick.

I'll work on this during the weekend and post back if I still have a
problem.

I've also posted on using MsgBox to help me with better understanding
Bangs & Dots... so maybe between all this I will get educated

Thanks again for the help.

Mel