Can't open form using listbox non-bound column

  • Thread starter Thread starter Nick Mirro
  • Start date Start date
N

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 said:
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.
Click to expand...
I created a command button to duplicate this "isn't 1" action and got this
error:

"Syntax error in string in query expression '[VisitID]=""
Click to expand...
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
Click to expand...

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
 
Back
Top