Can't open form using listbox non-bound column

[Nick Mirro]

When a listbox (lstRecentPatients) row is clicked it opens another form
(based on option group selection) to the matching record using the code
below.


If option group "FormToOpen" is 1, the correct form opens, but the subform
record desired is not displayed.

If option group "FormToOpen" isn't 1, then it hangs on the ***OpenForm
command.


I created a command button to duplicate this "isn't 1" action and got this
error:

"Syntax error in string in query expression '[VisitID]=""



Private Sub lstRecentPatients_AfterUpdate()

If FormToOpen = 1 Then

Dim frm As Form

DoCmd.OpenForm "frmCollections", _
WhereCondition:="PatientID=" & Me![lstRecentPatients]

Set frm = Forms!frmCollections!sbfrmCollections.Form

With frm.RecordsetClone
.FindFirst "VisitID=" & Me!lstRecentPatients.Column(10)
If Not .nomatch Then
frm.Bookmark = .Bookmark
End If
End With

Set frm = Nothing

End If

If Not FormToOpen = 1 Then

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReceivePayments"

stLinkCriteria = "VisitID=" & "'" & Me!lstRecentPatients.Column(10)
& "'"
***DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End Sub



I would greatly appreciate being shown the error causing problems with this.

Nick

 

[Nick Mirro]

Forgot to refer to column as -1 the position. Now it works : )

 

[Emilia Maxim]

If option group "FormToOpen" is 1, the correct form opens, but the subform
record desired is not displayed.

If option group "FormToOpen" isn't 1, then it hangs on the ***OpenForm
command.

I created a command button to duplicate this "isn't 1" action and got this
error:

"Syntax error in string in query expression '[VisitID]=""

Private Sub lstRecentPatients_AfterUpdate()

If FormToOpen = 1 Then

--- snip ---
End If

If Not FormToOpen = 1 Then

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReceivePayments"

stLinkCriteria = "VisitID=" & "'" & Me!lstRecentPatients.Column(10)
& "'"
***DoCmd.OpenForm stDocName, , , stLinkCriteria

Nick,

as I see further above, the VisitID is probably numerical, so leave
out the quotes:

stLinkCriteria = "VisitID=" & Me!lstRecentPatients.Column(10)

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de