Move to a record with double click

[JR2913]

I can't get my head round this one today!

What's the syntax for double clicking on an item in a listbox to move to that record in the form?

Current procedure produces a syntax error

(missing operator) in query expression "[LocationID] = ' :



Private Sub List0_DblClick(Cancel As Integer)
DoCmd.OpenForm "subfrmServiceLocation", , , "[LocationID] = " & Me!List0

End Sub

The other issue is that this is a subform that is already open. I have used the OpenForm command successfully elsewhere to do this task but wonder if there is just a command to update the view with the selected record when the required item is double-clicked in the listbox?

Any help much appreciated.

John R

 

[LonnieJohnson]

There is a single tick mark at the end of your error message. Is [LocationID] a text or numeric field? If it is text, are you enclosing it in ticks on both sides correctly?

Just a thought.

lonniejohnson@prodev.us
ProDev, MS Access Applications B-) ,

http://www.prodev.us

May God bless you beyond your imagination.

 

[JR2913]

Lonnie

LocationID is an autonumber field, so it's a long integer.

Sorry, the error message has single quotes, viz:

(missing operator) in query expression '[LocationID] = '

John R

 

[JR2913]

Solved it.....

the list box was locked for the user....!


John R

 

[JR2913]

The only problem now is that the subform now opens up again and occupies the whole screen, rather than the portion of the main form in which it is located.

Any ideas how to resolve this one?

John R

 

[LonnieJohnson]

Open the subform in design view. Restore it to the size and position that you desire. Change the border style to dialog. Then save it.

Hope that helps.

lonniejohnson@prodev.us
ProDev, MS Access Applications B-) ,

http://www.prodev.us

May God bless you beyond your imagination.

 

[JR2913]

Tried that, but still reopens the subform maximized. Have checked that it is not movable, sizable, auto centering or modal.

John R

 

[LonnieJohnson]

Open the properties window for the subform try setting the "Pop Up" property to Yes on the 'Other' tab.



lonniejohnson@prodev.us
ProDev, MS Access Applications B-) ,

http://www.prodev.us

May God bless you beyond your imagination.

 

[JR2913]

But I didn't really want the subform to appear as a pop-up, I just wantd to refresh the current view with the selected record (to save having to scroll through them all).

I'm sure it's quite a straightforward problem....... really!


John R

 

[JR2913]

I eventually modified a routine I had used elsewhere in the project to solve the problem. Thus the procedure is now:

Private Sub List0_DblClick(Cancel As Integer)
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LOCATIONID] = " & str(Nz(Me![List0], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Now this works fine within the same form or subform. However, I have a listbox where I want the user to double click on an item and thus refresh the data visible in a subform. The subform is linked to the table storing the data I want displayed.

Can I use the code above, modified of course, to link to the table (tblServiceuse) in the subform (subfrmServiceSelector) within the main form (frmChildData) by double clicking on an item in the listbox ([Service Use])? If so, how?

John R