double click listbox openform 2501 error

[antonyx]

the user searches records and they are filtered..

http://www.londonheathrowcars.com/searchform.jpg
http://www.londonheathrowcars.com/searchquery.jpg

and when they double click i want the record to open in this form..

http://www.londonheathrowcars.com/showform.jpg
http://www.londonheathrowcars.com/showquery.jpg

trying to put this code on my listbox but gettin the openform cancelled 2501 error on the bold line.. anyone see why?

Private Sub QuickSearch_DblClick(Cancel As Integer)
Forms("searchform").SetFocus
Forms("searchform").RecordSource = vbNullString

[b]DoCmd.OpenForm "showform", , , "[jobref] = " & Me![QuickSearch].Column(0)[/b]
End Sub

note:

on the search form.. the form has no record source.. the listbox has the record source of the searchquery..

also on the show form.. the form has the record source of the showquery.

 

[antonyx]

a simple sample of my problem

 

[pbaldy]

jobref is a text value, not a number.

 

[antonyx]

ok.. well i have never been too sure about the commas and the syntax of this statement so i might aswell ask now...

do i have to lay this sentence out differently if the value is a text?

DoCmd.OpenForm "showform", , , "[jobref] = " & Me![QuickSearch].Column(0)

 

[pbaldy]

Maybe the same way you did it successfully here:

http://www.access-programmers.co.uk/forums/showthread.php?t=119588

 

[antonyx]

Dim stLinkCriteria As String
stLinkCriteria = "[jobref]=" & "'" & Me![QuickSearch].Column(0) & "'"
DoCmd.OpenForm "all", , , stLinkCriteria

this worked..i thought about using the linkcriteria... i really did..

thank you for pouring that bucket of cold water around my face..

 

[pbaldy]

Using the variable was not relevant. The single quotes were. This would have worked as well:

DoCmd.OpenForm "showform", , , "[jobref] = '" & Me![QuickSearch].Column(0) & "'"

 

[antonyx]

lol... oh ant.. where would you be without these forums.. tut tut tut.. i should stick to websites..

 

[antonyx]

can i just say i actually know a lot about database design.. dependancies.. normalization...

the conceptual and logical design stages.. decomposition, subtypes..

i studied advanced database module for 3 years.. not once did i use access.. its just the vb i need to get to grips with.