Open a record using a value from unbound list box

[Eric the Viking]

Dear All

I have an unbound list box which is populated from an unbound text box. I wish to use the OnClick event of the list box to open a form with the full record of the row selected in the list box. The code I have so far is:

For the unbound TextBox:

Private Sub TextSurname_AfterUpdate()

If IsNumeric(Me.TextSurname) Then
ListPts.RowSource = "Select [tbl-patient details].[Patient number],[tbl-patient details].Surname,[tbl-patient details].[First name 1]," & _
"[tbl-patient details].[Date of Birth],[tbl-patient details].[NHS number] " & _
"FROM [tbl-patient details] " & _
"WHERE [tbl-patient details].[Patient number] = " & TextSurname.Value & _
" ORDER BY [tbl-patient details].[First name 1];"
Else
ListPts.RowSource = "Select [tbl-patient details].[Patient number],[tbl-patient details].Surname,[tbl-patient details].[First name 1]," & _
"[tbl-patient details].[Date of Birth],[tbl-patient details].[NHS number] " & _
"FROM [tbl-patient details] " & _
"WHERE [tbl-patient details].Surname = '" & TextSurname.Value & "' " & _
"ORDER BY [tbl-patient details].[First name 1];"
End If
Dim getListPtsCount As Integer
Dim setHeight As Integer
getListPtsCount = Me.ListPts.ListCount
setHeight = 275
Me.ListPts.Height = getListPtsCount * setHeight
TextSurname = ""

End Sub

For the OnClick event of the ListBox I have:
Private Sub ListPts_Click()
On Error GoTo Err_ListPts_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm-Update patient details"

stLinkCriteria = "[Patient number]=" & Me![[ListPts].Column(0)]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ListPts_Click:
Exit Sub

Err_ListPts_Click:
MsgBox Err.Description
Resume Exit_ListPts_Click

End Sub

But access returns an error message saying "Database cant find the field [ListPts].Column(0) referred to in your expression"

I am a real beginner at this and any help would be gratefully received.

cheers

Eric

 

[John Big Booty]

Change this line of code;

stLinkCriteria = "[Patient number]=" & Me![[ListPts].Column(0)]

to;

stLinkCriteria = "[Patient number]=" & Me![ListPts]

You do not need to explicitly reference Column(0) as this is the default.

 

[Eric the Viking]

Many thanks for your help your advice worked perfectly.

All the best

Eric