How to apply filter when opening a form

[philqw78]

Hi all. I am trying to apply a filter in VBA to open a form. The filter fires on double click on a field value. I have got so far

DoCmd.OpenForm "TrackerEditFmDSFrm", 
acNormal, , "AR_ID =" & AR_ID

This opens a message box displaying the AR_ID selected. But the user then needs to input the AR_ID. Can it be done so the filter runs using the selected value missing out the message box?

[NeoPa]

philqw78: ... using the selected value missing out the message box?

What selected value are you referring to?

It can certainly be done, but helping you would require some understanding of what you're working with.

[philqw78]

Sorry. I double click a value in the AR_ID field in a datasheet form view, say AR152. I want this value to filter the AR_ID in the form that is opened on doubleclick. What happens though is a message box appears, "Input Parameter", and the field value "AR152" is above the text box where it wants the parameter to be input.

[NeoPa]

philqw78: I double click a value in the AR_ID field in a datasheet form view

This is a form you're talking about then I suppose. If you double-click a control on your form then there must surely be some code behind this. To have any idea of what is happening we would need to see the code.

[philqw78]

The code for the double click on the form field is

DoCmd.OpenForm "TrackerEditFmDSFrm", acNormal, , "AR_ID =" & AR_ID

[NeoPa]

We seem to be getting nowhere fast here. Try to avoid replying with the least information you could imagine being required. Give it some thought before posting. I'm not after the same code you've already posted. I'm looking for something with the information included that will save me asking you loads of questions about information you probably should have included with the question, but were understandably unaware of, as you don't have the experience that we have in knowing what is required.

I'm trying to make it easier for you, but you need to respond after first considering your reply.

1. Please post the whole procedure that the code is within.
2. Please explain what AR152 is. Bear in mind forms have controls and recordsets have fields.
3. I assume the recordset (probably table) that your form [TrackerEditFmDS Frm] is bound to has a field called [AR_ID]. Please confirm.
4. Is there actually a control on your form [TrackerEditFmDS Frm] called [AR_ID]?

If there is anything here you don't understand, don't be afraid to say so. Communication is the key. Good communication makes for much easier progress. The reverse is also true of course.

[philqw78]

1. Please post the whole procedure that the code is within.
2. Please explain what AR152 is. Bear in mind forms have controls and recordsets have fields.
3. I assume the recordset (probably table) that your form [TrackerEditFmDS Frm] is bound to has a field called [AR_ID]. Please confirm.
4. Is there actually a control on your form [TrackerEditFmDS Frm] called [AR_ID]?

If there is anything here you don't understand, don't be afraid to say so. Communication is the key. Good communication makes for much easier progress. The reverse is also true of course.

Sorry Neo. I am probably trying to run before I can even crawl here.

1. The whole procedure is
   

   Private Sub AR_ID_DblClick(Cancel As Integer)
   
   DoCmd.OpenForm "TrackerEditFmDSFrm", acNormal, , "AR_ID =" & AR_ID
   
   End Sub

2. AR152 is the value in the "AR_ID" field/control of a single record.
3. Yes the field AR_ID is in the "Tracker" table. The form "TrackerDSf rm" looks at this table . (And some bound fields from another table, these are bound on a lookup combo for AR_ID). This is the form that I use the double click in. (Hoping that the double click would open "TrackerEditFmD SFrm" at the selected double clicked record)
4. I didn't consciously make one, unless a field put in a form is a control. The field(?) "AR_ID" is in the form, and that is what I am hoping to filter on.

There are lots of things I don't understand. I'm slowly trying to fight my way through them. What is your definition of a control. To me they are the buttons I can put on a form and add some action or macro to them. This is where my answers above may be wrong.
Hopefully this is a bit clearer.

[NeoPa]

That is certainly much better Phil. There may be understandable mistakes in your answers but, as I say, they are understandable and not related to lack of effort on your part.

First let me say what I think is the solution, then I will try to answer any outstanding queries you may have.

Try instead :

Private Sub AR_ID_DblClick(Cancel As Integer)

    DoCmd.OpenForm "TrackerEditFmDSFrm", _
                   acNormal, , _
                   "AR_ID='" & AR_ID & "'"

End Sub

If a value for [AR_ID] can be non-numeric ("AR152" for example), then the field in the table is almost certainly a string field. String literals should be enclosed in quotes (Quotes (') and Double-Quotes (") - Where and When to use them). I've shown the code over three lines for clarity and visibility. It is not necessary to have it that way in your code, though it'll do no harm of course.

philqw78: I didn't consciously make one, unless a field put in a form is a control. The field(?) "AR_ID" is in the form, and that is what I am hoping to filter on.

Controls come in various shapes and sizes on a form. When fields are added to a form though, they are typically created as TextBox or other similar control, but importantly they are bound to an underlying field from the recordsource. Now, often these controls have the same name as the related (bound) field, but this is not necessarily so. The filter you apply when opening the form relates to the field, not the control. It is quite likely though, that this will not be obvious as the control could have the same name as the field.

Does that make things a little clearer for you?

[philqw78]

Thanks Neo. Thats excellent. And today wasn't wasted as I learnt something.

[NeoPa]

Good for you :)

A day's never wasted if you learn something.