Open form by Double clicking on a Listbox

[cailo]

I have currently created a few search fields where the results appear in a listbox. What I am hoping to do is to double click on a specific record in the listbox and have it open up to a form with all the details in it. Currently my code for the listbox is:

Private Sub lstMSDNInfo_DblClick(Cancel As Integer)
'Open frmMSDN based on the ID from lstMSDNInfo listbox

DoCmd.OpenForm "frmMSDN", , , "ID = " & Me!lstMSDNInfo, , acDialog

End Sub

When I run it, it just gives me a syntax error The OpenForm action was cancelled. When i debug it it takes me to:

DoCmd.OpenForm "frmMSDN", , , "ID = " & Me!lstMSDNInfo, , acDialog

Any ideas of what I am doing wrong? I know it's something really simple that I'm missing but I've been over it several hundred times and I can't find anything wrong with it. Any help would be greatly appreciated

Cheers

 

[pbaldy]

A syntax error usually occurs when comparing a number to a string or something similar. Is ID a number? Your structure is set up for it to be.

 

[cailo]

Yes, my ID is a number. Does it matter that there may be decimal points in some of the ID's?

 

[pbaldy]

Well, I wouldn't think so, though it seems unusual to have an ID field with a decimal. You don't mean multiple decimals do you? That would indicate a text field. I don't suppose you could post a demo db?

 

[cailo]

Your right! I just checked my table again and the ID field is text, so where do I change that in my code?

 

[pbaldy]

Try this:

DoCmd.OpenForm "frmMSDN", , , "ID = '" & Me!lstMSDNInfo & "'", , acDialog

 

[cailo]

Thanks heaps Paul,
Worked like a charm. Thanks again for all your help.

 

[pbaldy]

Happy to help. You'll find that in many (most?) instances, text, numeric and date values each have to be treated differently, and this was one of those. You had the correct structure for a numeric value; we just had to change it for a text value.

Good luck!

 

[cailo]

Thanks again,

I'm kinda just getting back into all this database stuff. I'm just going off what I remember from school.

 

[NachoMan]

Good man

Thanks, Paul, for once again saving my evening and my liver.