DoCmd.OpenForm - Criteria not passing to form (1 Viewer)

[jmurph420A]

Good day all, I am working on a VERY simple onClick Event in a report, then when clicked, should open a form to a specific record.
(first time poster LONG TIME learner)

Code is:

Private Sub Company_Click()

    stcriteria = Me.Control.Value
    DoCmd.OpenForm "Clients", , , "[Control]='" & Me.Control.Value & "'"


End Sub

This again, should open the Clients form, and filter on the value passed from the Control value on the report, to the Control value on the Clients form.

The form allows edits and data entry, however, regardless of what item I click on the report, it will open the Clients form without passing the Control filter.

The Clients form data source is a filtered query based on a different field.

I did verify the value of Control I (attempt to)pass to the clients form is in the correct format (string) and does exist in the table.

All assistance appreciated

 

[MajP]

I am surprised this even compiles. I would have thought Me.Control would crash since control is an object in vba. At a minimum I would change that to Me!control or change the name of the control that the field "Control" is bound to something like txtBxControl.

 

[Isaac]

Does this work?

Private Sub Company_Click()

    stcriteria = Me.Company.Value
    DoCmd.OpenForm "Clients", , , "[Control]='" & Me.Company.Value & "'"


End Sub

But please specify the actual names of the control they are needing stcriteria from, and the Clients control name

 

[jmurph420A]

I am surprised this even compiles. I would have thought Me.Control would crash since control is an object in vba. At a minimum I would change that to Me!control or change the name of the control that the field "Control" is bound to something like txtBxControl.

s

Does this work?

Private Sub Company_Click()

    stcriteria = Me.Company.Value
    DoCmd.OpenForm "Clients", , , "[Control]='" & Me.Company.Value & "'"


End Sub

But please specify the actual names of the control they are needing stcriteria from, and the Clients control name

So, I didn't develop the DB, I would not have used Control as a field name or object name anywhere in the DB.



Control is the field name in the DB table, as such is the literally the pre-defined name of the "control" on the report I open, as well as the form I'm trying to open and filter on. This has me thinking though, I'm going to alias the field on the report, and see if it will pass the value properly that way.

 

[Ranman256]

do not use the VALUE property. Just use the control name.
it should work if :
[control] is the name of the field
control is a string.

If its numeric:
DoCmd.OpenForm "Clients", , , "[Control]=" & Me.Company

 

[Isaac]

Yeah generally speaking the name of a control on a Report or Form should never be identical to the name of the underlying bound column - that's just WAY too confusing and in some cases, makes it actually difficult to write code, since Me.NameOfBoundFieldUnderlying vs. Me.ControlName will actually DO different things - depending on what you're doing.

I would fix all the names even just for your own sanity, much less for asking questions - which it sounds like you are doing, and seems like a great next step.

 

[jmurph420A]

do not use the VALUE property. Just use the control name.
it should work if :
[control] is the name of the field
control is a string.

If its numeric:
DoCmd.OpenForm "Clients", , , "[Control]=" & Me.Company

Thanks. I tried that also. The value is a text string, e.g. SJ134, so I pass it in single quotes.

I surmise it may be a result of the field name of Control.

 

[jmurph420A]

Yeah generally speaking the name of a control on a Report or Form should never be identical to the name of the underlying bound column - that's just WAY too confusing and in some cases, makes it actually difficult to write code, since Me.NameOfBoundFieldUnderlying vs. Me.ControlName will actually DO different things - depending on what you're doing.

I would fix all the names even just for your own sanity, much less for asking questions - which it sounds like you are doing, and seems like a great next step.

I completely agree. Unfortunately, I don't have the ability to change the field names, as I'm a consultant on the project. I am however, thinking of another workaround based on the feedback here.

 

[MajP]

Did you try what I suggested Me!Control? Of me.controls("control") will too.

 

[jmurph420A]

Did you try what I suggested Me!Control? Of me.controls("control") will too.

I did. With the same result as before. I duplicated all the forms/subforms, and aliased the word 'Control' and it magically worked, indicating that the issue in fact tied directly to the name of the field/control.