Help with code

[cybermonkey]

I have the following code:

Dim RecordId As Integer
Dim asFilter As String
asFilter = Me.RCA_Number
MsgBox (asFilter)


DoCmd.OpenForm "Action Items", acNormal, asFilter, acFormEdit, acWindowNormal
Forms("Action Items").Requery

What this is supposed to do it open a form with only showing record filtered by the field that was double clicked.
It's working for the most part, asFilter is getting set to be the proper value, but the form being opened to display the data is not showing any data. I am most likely missing something on the DoCmd line, but not seeing it, any other set of eyes to see what I am missing would be appreciated.
Thanks.

[Bob Fitz]

Perhaps:
asFilter = "[NameOfFieldToFilter] = " & Me.RCA_Number
DoCmd.OpenForm "Action Items", acNormal, , asFilter, acFormEdit, acWindowNormal

[Welshgasman]

Think about it. How is Access meant to know which field you are comparing the value to?
Just because you clicked on it?
If you actually wanted to do that, you would have to identify which field it is and set the filter accordingly.

[cybermonkey]

That code ends up setting asFilter to equal "[RCA_Number] RCA0003" (depending on which line I double click"
Then it also prompts to enter parameters, but but still shows no data

[June7]

Filter expression created needs to follow syntax of:

[somefieldname]=somevalue

And if value is text, need apostrophes:

[somefieldname]='somevalue'

or date

[somefieldname]=#somevalue#

Review http://allenbrowne.com/ser-62.html

[cybermonkey]

Think about it. How is Access meant to know which field you are comparing the value to?
Just because you clicked on it?
If you actually wanted to do that, you would have to identify which field it is and set the filter accordingly.

I understand that, but that is the part I am having issues with in the code.
Using the code somebody posted in this thread. When double clicking on the field in the first form, it pops up with two prompts, and labeled "RCA_Number" and the second prompt is labeled with the value of the field double clicked.
If entering nothing at either prompt no data is displayed on the new opened form.
If entering a value of what was double clicked at both prompts, all the data is shown unfiltered.
If entering the desired value to filter in the first prompt and nothing in the second prompt; no data shown on the new form.
If entering nothing at the first prompt and desired filtered value at the second prompt; no data shown on the new form.

[cybermonkey]

Let me try to explain a bit better in what ultimately id desired.
There is the list of different RCANumbers. When one is double clicked, then the new form shows all the records that have that value as the RCANumber double clicked, without the need of a pop up prompt.

[Welshgasman]

Let me try to explain a bit better in what ultimately id desired.
There is the list of different RCANumbers. When one is double clicked, then the new form shows all the records that have that value as the RCANumber double clicked, without the need of a pop up prompt.

Yes, we know what you are trying to do and you have been advised by two different members on how to go about it.
A filter is
Which field you are searching on
The = sign
What value you are searching for.

[cybermonkey]

Yes, we know what you are trying to do and you have been advised by two different members on how to go about it.
A filter is
Which field you are searching on
The = sign
What value you are searching for.

And unfortunately, what they suggested did not work.

Below is the code, that was able to make it work.


Dim RCANum As String
RCANum = Me.RCA_Number
DoCmd.OpenForm "Action Items"
DoCmd.ApplyFilter , "[RCA_Number] = '" & RCANum & "'"

[Welshgasman]

And unfortunately, what they suggested did not work.

Below is the code, that was able to make it work.


Dim RCANum As String
RCANum = Me.RCA_Number
DoCmd.OpenForm "Action Items"
DoCmd.ApplyFilter , "[RCA_Number] = '" & RCANum & "'"

what they suggested did not work

So what do you think this is?

"[RCA_Number] = '" & RCANum & "'"

Which field you are searching on [RCA_Number]
The = sign
What value you are searching for. RCANum

That would work equally as well as the filter argument for OpenForm.

I would always qualify with Me.RCANum

[cybermonkey]

So what do you think this is?

"[RCA_Number] = '" & RCANum & "'"

Which field you are searching on [RCA_Number]
The = sign
What value you are searching for. RCANum

That would work equally as well as the filter argument for OpenForm.

I would always qualify with Me.RCANum

What I think this is, is the way I was able to get it to work.
The code was just not working from the suggestions, not saying anything negative about anybody, but sometimes it is what is. For whatever reason, trying the initial method and suggestions was not working, so started from scratch to approve it slightly differently, and that is the method that was able to do what I needed/wanted it to.