runsql code problem

  • Thread starter Thread starter John Daily
  • Start date Start date
J

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
 
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
 
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
 
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...


Ruskin Hardie said:
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


John Daily said:
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
Click to expand...
Click to expand...
 
Back
Top