runsql code problem

[John Daily]

I am having a problem with this code, especially the
runsql. On the form the user will click a check box
(Completed) and then will click on the save button. This
should update the Patient Information table for a specific
acct # and will make the check box true if marked true.
The form is bound to that Patient Information table and
the check box is bound to that same table. All other
textbox's are unbound and get their info from a list box.
Here is the code for the button:
-------------

Private Sub cmdSaveRecord_Click()
Dim strSQL As String
Dim strwhere As String
Dim varItem As Variant
For Each varItem In Me.lstPatientDenials.ItemsSelected
strwhere = strwhere & "[Patient Information].acct_num
= " & Me.lstPatientDenials.Column(0)
Next varItem
RunSQL "UPDATE [Patient Information] SET [Patient
Information].Completed = " & Me.Completed & " WHERE (" &
strwhere & ");"

lstPatientDenials.Requery

End Sub


TIA,

John

 

[Ron Weiner]

Looks like the Problem is your where clause needs to be surrounded by
quotes. change to :

" WHERE ('" & strwhere & "');"

Note the Single quote just after the opening ( and just before the closing )

If ther is a chance that strwhere might have an embedded Single Quote in it
you will need to double up on the single quoutes. In Acc 2K and latter you
can use:

strwhere = Replace(strwhere,"'","''")

to handle this condition.

Ron W

 

[Ruskin Hardie]

Not sure if Ron has it right, as the strWhere is not a field value, but
instead the entire where criteria.... Think the issue, is if there are
multiple varItem parts in the Me.lstPatientDenials.Selected set... The
reason, is that there is no AND between the criteria. Hence, if you have
multiple records selected, in the list box, the where condition could look
like;
[Patient Information].acct_num=1[Patient Information].acct_num=2[Patien....
etc...

What you might need to do, is try;

FirstTime = True
For Each varItem In Me.lstPatientDenials.ItemsSelected
If FirstTime Then
strWhere = "[PatientInformation].acct_num=" &
Me.lstPatientDetails.Column(0)
FirstTime = False
Else
strWhere = strWhere & " and [PatientInformation].acct_num=" &
Me.lstPatientDetails.Column(0)
End If
End With

 

[Ruskin Hardie]

Ooopss... It may have to be an 'OR' statement not an 'AND' statement...
Also, another option (probably a better one), is to put the RunSQL in the
For/With loop...

Not sure if Ron has it right, as the strWhere is not a field value, but
instead the entire where criteria.... Think the issue, is if there are
multiple varItem parts in the Me.lstPatientDenials.Selected set... The
reason, is that there is no AND between the criteria. Hence, if you have
multiple records selected, in the list box, the where condition could look
like;
[Patient Information].acct_num=1[Patient Information].acct_num=2[Patien....
etc...

What you might need to do, is try;

FirstTime = True
For Each varItem In Me.lstPatientDenials.ItemsSelected
If FirstTime Then
strWhere = "[PatientInformation].acct_num=" &
Me.lstPatientDetails.Column(0)
FirstTime = False
Else
strWhere = strWhere & " and [PatientInformation].acct_num=" &
Me.lstPatientDetails.Column(0)
End If
End With

I am having a problem with this code, especially the
runsql. On the form the user will click a check box
(Completed) and then will click on the save button. This
should update the Patient Information table for a specific
acct # and will make the check box true if marked true.
The form is bound to that Patient Information table and
the check box is bound to that same table. All other
textbox's are unbound and get their info from a list box.
Here is the code for the button:
-------------

Private Sub cmdSaveRecord_Click()
Dim strSQL As String
Dim strwhere As String
Dim varItem As Variant
For Each varItem In Me.lstPatientDenials.ItemsSelected
strwhere = strwhere & "[Patient Information].acct_num
= " & Me.lstPatientDenials.Column(0)
Next varItem
RunSQL "UPDATE [Patient Information] SET [Patient
Information].Completed = " & Me.Completed & " WHERE (" &
strwhere & ");"

lstPatientDenials.Requery

End Sub


TIA,

John