List Box Problem

[Ally]

I have a list box that I put some code on the DoubleClick event to open the related record clicked, in another form. I've done it before and it works but for some reason this one is not working and I can't understand why. It opens the correct form, but opens a new record and not the one selected in the list box. (I just wanted to have a list box instead of a subform as it's slightly neater).

 Private Sub lstEpisode_DblClick(Cancel As Integer)
On Error GoTo Err_lstEpisode_DblClick


    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmEpisode"
    
    stLinkCriteria = "[ITURegNo]=" & "'" & Me![lstEpisode] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_lstEpisode_DblClick:
    Exit Sub

Err_lstEpisode_DblClick:
    msgbox Err.Description
    Resume Exit_lstEpisode_DblClick

End Sub

(I originally put a command button on a subform, then copied the code across - just changed the bit:

stLinkCriteria ... ... "'" & Me![lstEpisode] & "'"

from

stLinkCriteria ... ... "'" & Me![ITURegNo] & "'"

I hope this makes sense. Does anyone have any ideas please?

 

[wh00t]

Let me see if I can explain this in an understandable way

here's some code

Private Sub cmdEdit_Click()
On Error GoTo Err_cmdEdit_Click

Dim stDocName As String
Dim rst As Recordset

stDocName = "frmEpisode"

DoCmd.OpenForm stDocName

Set rst = Forms!stDocName.RecordsetClone

rst.FindFirst "*frmEpisodeID* = " & *List0*

Forms!stDocName.Bookmark = rst.Bookmark

Exit_cmdEdit_Click:
    Exit Sub

Err_cmdEdit_Click:
    msgbox Err.Description
    Resume Exit_cmdEdit_Click
    
End Sub

this is the method that I use to go to a certain record on another form.
*frmEpisodeID* (take the stars out) - in here put the unique field name of the record that you wish to find.

*List0* (take the stars out) - where List0 is whatever the name of the list box is. - when you set up the list box (use the wizard), I presume your using the same source as the form to display the data, set the unique field the same as *frmEpisodeID*.

this should do it


HTH

 

[Rich]

Is episode a string, or numeric value?

 

[Ally]

Pat: the form is not set to Data Entry.

Rich: There isn't a field called Episode but on tblEpisode and frmEpisode, ITURegNo is the unique ID and it's a numeric field.

wh00t: I used your code thank you, but unfortunately I get an error message:

Critical Care Follow-up cannot find the form 'stDocName' referred to in a macro expression or Visual Basic code.

* The form you referenced may be closed or may not exist in this database.
* Critical ... may have encountered a compile error

It does open the form but opens at the beginning of the recordset and shows all records.

 

[Rich]

Unless my eyesight is failing your first example is using the form you're trying to open as the criteria
Dim strFormName As String

strFormName = "frmEpisode"

DoCmd.OpenForm strFormName, , , "[ITUregno] = " & Me!lstFound

 

[Ally]

Oops sorry - I realized that the original code I posted (in my first post) was wrong but have amended it.

Pat what you posted

stLinkCriteria = "[ITURegNo]=" & "'" & Me![lstEpisode] & "'"

is what I had already and this is what opens up the form but as a new record. Can't understand this as the form's not data entry, there no ... acFormAdd.

 

[joeyreyma]

check the following properties of your lstEpisode:

1. Rowsource
2. Column Count
3. Bound Column

the Bound Column of list box should be relevant to ITURegNo.

 

[Ally]

That's it - thank you joeyreyma - can't believe I'd missed that. I had the bound column as UnitNo not ITURegNo.

Thank you everyone for your help.