double click

[Roland87]

I have pasted a code below which used by John Big Booty in a tutorial to open another form and populate its fields from a list box in another form.

After creating a simple form with fields, I have pasted this code in the double click event for the list box, but on double click, access gives me "missing operator in query expression '[RecordID]=name of the selected client in the list box'. The list box holds the RecordID (primary key), and two other fields.

I can't get the double click to function, what should be done?

Here's the code: (listbox name is SearchResults)


Private Sub SearchResults_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "newform"

stLinkCriteria = "[RecordID]=" & Me![SearchResults]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[AlexHedley]

What is the source of your listbox?

Does it have the first field as RecordID?

You could msgbox the value to see what it says depending on which row you double click.

 

[Roland87]

The source is a query with a criteria;
http://www.access-programmers.co.uk/forums/showthread.php?t=188663
The first field isn't RecordID, but even if it was, it doesn't work.

 

[AlexHedley]

The first column in the searchbox listbox needs to return a number since your criteria is asking to match [RecordID]= 'said number'

Do you have another form in your database called "newform" that contains a Field called RecordID?

What did the msgbox return?

 

[Roland87]

The first column (column number 0) is the RecordID, I have created a form (newform in the code above) with a RecordID field, but somehow it gives me this whenever I double click a record in the list box:

Syntax error (missing operator) in query expression '[RecordID]=name of the selected client in the list box'

 

[AlexHedley]

If you rem out the DoCmd and add msgbox stLinkCriteria what does it return?

 

[Roland87]

Okay, it gives [RecordID]=ClientName. (ClientName is another field in the list box). This isn't correct.

However,

I tried putting ClientName instead of RecordID on stLinkCriteria in the code, but it still does not work. same error.

Strange...

 

[AlexHedley]

So if you double click on on line in the listbox is it saying clientname?

Even if you change it to clientname your criteria would be clientname=clientname which would be wrong

You need the msgbox to say RecordID=1 etc

Can you just open the Query that the listbox is based from and see what values it has.
You might have incorrect values there

 

[Roland87]

I have these 3 fields in the query in the following order in design view: ClientName, RecordID, FatherName. The criteria for ClientName is:

Like "*" & [forms]![Form1]![SrchText] & "*"

I then skip a line on "or" in RecordID:

Like "*" & [forms]![Form1]![SrchText] & "*"

And no such thing on the third field.

 

[Roland87]

Okay, eummm... this code does not work.. I did this instead:

Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "newform", acNormal, , "[RecordID] = " & Me.SearchResults.Column(0)
End Sub

Now, it works

Many thanks

 

[speakers_86]

Since the first field in the list box is ClientName, Me![SearchResults] will return the client name. You need to specify the column you want returned using column property.

I think this might do it for you: Me![SearchResults].Column(1)

MsgBox that to see if it returns a number.

Side note: the reason for the syntax error described in the first post is that you were saying [RecordID]=ClientName. If you really did want to use ClientName, then it would have needed quotes (chr(34)) around it: "ClientName". Numbers on the other hand do not.