Dbl Click event on a list generating error please help

[campanellisj]

Morning and thanks for any help in advance.

I have a form called frmPMR that has a list box (SearchResults) in it that I am trying to configuare with the dbl click event. I would like the user to be able to dbl click an item in the list and have a smaller form (frmPmrPop) open displaying more information about the item selected.

I have the following code assigned to the dbl click event of the list:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmPmrPop"

stLinkCriteria = "[ID]=" & Me![SearchResults]
DoCmd.OpenForm stDocName, , , stLinkCriteria

I get an error when executing that highlights the last line in yellow. The information in the list is from a query that selects every field from a table (tblBenning).

What am I doing wrong?

 

[pbaldy]

What's the error? If ID is text, a different syntax is required:

http://www.baldyweb.com/wherecondition.htm

 

[campanellisj]

I attached a copy of the forms and queries in the DB here.

The ID field is an ID field, that is autonumber.

 

[SOS]

Your problem is that you aren't getting the ID value from the listbox. You are getting the FY10_CLS_SSP_Title, so if you double click on "Golf" you are trying to find a record based on Golf, not the ID. You need to add the ID to the listbox's rowsource and then reference the applicable column instead of the bound field.

 

[campanellisj]

Thank you for your response....so the code is right but the query is wrong? When I look at the Query supporting the list the ID is there. Are you saying the criteria needs to be attached to the ID field and not the CLS_SSP*** Field?

or change the code to look for that CLS_SSP*** field?
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmPmrPop"

stLinkCriteria = "[FY10_CLS_SSP_Title]=" & Me![SearchResults]
DoCmd.OpenForm stDocName, , , stLinkCriteria

that resulted in the same error.

 

[pbaldy]

You can do either, though typically you use a key field. To look for a text field try:

stLinkCriteria = "[FY10_CLS_SSP_Title]='" & Me![SearchResults] & "'"

 

[campanellisj]

Holy crap your the man! That dam thing was making my day horrible! Thank you again, as you can tell I am still learning VBA lol

I hate to ask this but can you break down what it was I did wrong. I mean looking at that string you sent the only difference was the:

& ""

you added to the string what did it do or tell access? If you don't have time to explain I understand...but thank you again

 

[pbaldy]

You're concatenating a string for the wherecondition argument of OpenForm. Your original effort using the key field would have evalutated to something like:

[ID]= 123

which is fine for a numeric field, but a text field requires quotes around it (short version). It would have to look like:

FieldName = 'Paul'

So what I did was to add a single quote both before and after (you may not have noticed the one before). The one after was surrounded by double quotes to add it to the string being built. I added what's in red (the code tags make it easier to spot the single quotes):

stLinkCriteria = "[FY10_CLS_SSP_Title]=[COLOR="Red"]'[/COLOR]" & Me![SearchResults] [COLOR="red"]& "'"[/COLOR]

 

[campanellisj]

I would have never caught that again thank you, and to any moderator looking her, I love this site

 

[pbaldy]

No problemo; I have a page here that shows the 3 different basic syntax requirements:

http://www.baldyweb.com/wherecondition.htm