Opening form with Filter using VBA

[benkingery]

I'm trying to code some VB code on a form that will open a second form for reference. It gets a little complicated because the procedure will actually be placed on a control within a subform on the original form. Here is what I have:

Private Sub OS_Label_DblClick(Cancel As Integer)
DoCmd.OpenForm "TransactionView_OS", acNormal, , "OS_Child=Forms!Master_Parent_QBF!Master_Child subform_Master_Parent.Form!OS_Child", acFormReadOnly, acWindowNormal
End Sub

So I'm trying to open a form called TransactionView_OS using a control called OS_Label. The control OS_Label is located within a subform (Master_Child subform_Master_Parent) and that subform is located on a main form called Master_Parent_QBF.

The sub I have above is giving me an error message that states:

Syntax error (missing operator) in qury expression 'OS_Child=Forms!Master_Parent_QBF!Master_Child subform_Master_Parent.Form!OS_Child'

I am comfortable getting the form to open but when I used this WhereCondition to filter the results is when I'm obviously having the issue.

Can anyone offer some help?

 

[SOS]

If the code is on the subform you can just refer to the subform as ME.

What value on the subform is going to be the filter?

 

[benkingery]

Thanks for the reply. I must be the syntax dummy, but I can't remember if Me is Me. or Me!

Here is what I've tried and neither seem to work.

Private Sub OS_Label_DblClick(Cancel As Integer)
DoCmd.OpenForm "TransactionView_OS", acNormal, , "[OS_Parent]=Me!OS_Parent", acFormEdit, acWindowNormal
End Sub


Private Sub OS_Label_DblClick(Cancel As Integer)
DoCmd.OpenForm "TransactionView_OS", acNormal, , "[OS_Parent]=Me.OS_Parent", acFormEdit, acWindowNormal
End Sub

Both of these pull up a parameter box asking me to define either Me!OS_Parent or Me.OS_Parent

Thanks again for any help you are able to offer.

 

[benkingery]

I realized I was calling the wrong control. Its actually supposed to be "OS_Child" in both instances.

I changed this ,though, and its still not working, so it obviously wasn't the problem.

 

[SOS]

I guess I am still confused as to what you want the form filtered by. What is the field? Is the field in the new form called OS_Parent? What is OS_Parent and what kind of data does it store?

 

[benkingery]

Thanks for the reply. Sorry, I haven't been too clear.

"OS_Child" is a product ID in our inventory management database. From the main form, "OS_Child" lists the item along with the current available onhand of that item. I'm trying to use OS_Label as the control from which to open TransactionView_OS which is the transaction log for this very same item. However, when TransactionView_OS is opened without a filter, all the products in our system show beginning with the first item. Applying the filter so that the current value in OS_Child from the subform on the main form is equal to the value of "OS_Child" on the form TransactionView_OS. This will save a lot of Find/search time.

The data types of both fields are text, 50 characters.

I hope that helps.

 

[SOS]

I think it helps -

So it should be:

DoCmd.OpenForm "TransactionView_OS", acNormal, , "[OS_Parent]=" & Chr(34) & Me!OS_Parent & Chr(34)

 

[benkingery]

Still no luck. I'm not sure if you saw my last post. I indicated that the control that both forms actually have in common is OS_Child, not OS_Parent.

That being the case, I just switched them out so I have this:

DoCmd.OpenForm "TransactionView_OS", acNormal, , "[OS_Child]=" & Chr(34) & Me!OS_Child & Chr(34)

But it still doesn't work. Its asking me for a parameter value again.

Any ideas?

 

[SOS]

Still no luck. I'm not sure if you saw my last post. I indicated that the control that both forms actually have in common is OS_Child, not OS_Parent.

That being the case, I just switched them out so I have this:

DoCmd.OpenForm "TransactionView_OS", acNormal, , "[OS_Child]=" & Chr(34) & Me!OS_Child & Chr(34)

But it still doesn't work. Its asking me for a parameter value again.

Any ideas?

So, Is OS_Child really have the underscore in the name or is it a space?

 

[benkingery]

Sorry its taken a bit to reply to this. I trust you'll still be out there.

OS_Child is the exact name of the control. OS_Child is ALSO the name of the data source in both instance. Not sure if this changes anything.

 

[SOS]

So, I would change the control name to something like txtOS_Child or cboOS_Child so that it is different than the field. You can then use the control name.

Also, what is the error message (exactly)?

 

[benkingery]

Okay, so I renamed the controls to ctrl_OS_Child on the original form, and OS_Child_ctrl on the form that is supposed to pop up. Here is the code that I now have:

Private Sub OS_Label_DblClick(Cancel As Integer)
DoCmd.OpenForm "TransactionView_OS", acNormal, , "[OS_Child_ctrl] = ' & Chr(34) & Me!ctrl_OS_Child & Chr(34)'"
End Sub

Still no luck. With this code, I'm now getting a prompt that pops up asking me to enter a Parameter Value. The parameter that is filled in is OS_Child_ctrl

I'm at a loss. Any suggestions?

 

[SOS]

You have quotes where you shouldn't and wrong quotes where you need others.

This:
"[OS_Child_ctrl] = ' & Chr(34) & Me!ctrl_OS_Child & Chr(34)'"

Should be:

"[OS_Child_ctrl] = " & Chr(34) & Me!ctrl_OS_Child & Chr(34)

 

[benkingery]

This is exactly what I have now:

Private Sub OS_Label_DblClick(Cancel As Integer)
DoCmd.OpenForm "TransactionView_OS", acNormal, , "[OS_Child_ctrl] = " & Chr(34) & Me!ctrl_OS_Child & Chr(34), acFormEdit, acWindowNormal
End Sub

Its still asking me for a parameter value for OS_Child_ctrl which is the name of the control on the pop-up form. If I add no value to the box and just click "OK" then it asks me for a parameter value for Master_Child.Master_Parent which is a table reference to table Master_Child column Master_Parent. I have no idea why it asks me this. Again, if I click "OK", that's when I get the error message saying:

Run-time error '3146':
ODBC--call failed.

I guess I should not that I'm using Access as the front end and all my tables are actual SQL server tables that I access through ODBC. I can't see why this would have anything to do with the problem.

I have attached screenshots so you can see in detail what the form and control names are. perhaps I have something mislabeled??? One screenshot shows the pop-up form while the other shows the original form. Please note that the original form has a subform from which the code is call upon double clicking 'OS' button in RED.

 

[SOS]

Based on your screenshots, you've got the name incorrect. It should be:

DoCmd.OpenForm "TransactionView_OS", acNormal, , "[OS_Child_ctrl] = " & Chr(34) & Me!OS_Child_crtl & Chr(34), acFormEdit, acWindowNormal

 

[benkingery]

Are you sure? When I make that change I get the error message:

Run-time error '2465':
Microsoft Office Access can't find teh field "OS_Child_ctrl" referred to in your expression

 

[SOS]

Sorry, use the DOT (.) and not the BANG (!)

Me.OS_Child_crtl