Need Help with Where arguement in DoCmd.OpenForm 1

[ThinWhiteDude]

Using Access 2003:
I am trying to open a form from a listbox and having the devil of a time with the where condition.

This is in the OnClick for the listbox, which is one of many listboxes on the form. They all need the same code (thus the ActiveControl usage) –basically to open a report that matches the number clicked on in the box.

The NumID field is the only one I have in the list, built straight from the table.

The NumID field on the form I want to open is a text field.

Here’s what I have :

DoCmd.OpenForm "frmEquipmentMalfunctionReport", acNormal, , "NumID=" & ActiveControl.Column(0)

Any help you can give, I would greatly appreciate.

TWD

 

[Remou]

How about:

[tt]DoCmd.OpenForm "frmEquipmentMalfunctionReport", acNormal, , "NumID=" & Screen.ActiveControl[/tt]

 

[randy700]

The NumID field on the form I want to open is a text field

How about:

DoCmd.OpenForm "frmEquipmentMalfunctionReport", acNormal, , "NumID=[COLOR=red] '[/color] " & ActiveControl.Column(0) [COLOR=red]& " ' "[/color]

Randy

 

[ThinWhiteDude]

Remou & Randy,

I tried both of your suggestions, but neither worked. I think there is something about using the "where" condition on a form that I'm getting wrong.

I switched tactics and decided to work with the filter arguement instead, with this code:

DoCmd.OpenForm "frmEquipmentMalfunctionReport", acNormal
    
'Filter for selected EDR:
Forms!frmEquipmentMalfunctionReport.Filter = "NumID = '" & ActiveControl.Column(0) & " ' "
Forms!frmEquipmentMalfunctionReport.FilterOn = True

And it worked. I will probably use this, but wish I knew what I was doing wrong on the where . . .

Thanks to both of you for your quick responses.

TWD

 

[Remou]

You would be best to use OpenArgs with the code illustrated above, where OpenArgs is set to the value of the active control and then used to filter. If you are using the code shown in your original post, it seems likely that randy700's suggestion will work.

 

[ThinWhiteDude]

Remou,

Would love to do that, but I copied the entire line of Randy's code and pasted it in, and came up with no record.
TWD

 

[Remou]

So, have you stepped through to find what value is being passed via ActiveControl? Is NumID a text field?

 

[PHV]

What about this ?

DoCmd.OpenForm "frmEquipmentMalfunctionReport", acNormal, , "NumID='" & ActiveControl.Column(0) & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ThinWhiteDude]

PHV--

That did it --I should have caught those spaces in the literal.

Thanx &
Have a star