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

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


"Nick Mirro" <(E-Mail Removed)> wrote in message
news:u293GV%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote:

>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