Update Table Code Included

[Guest]

I have the following code and it's not working. Any suggestions?

Sub UpdateDB(numRecs As Integer)
Dim i As Integer
Dim sSQL As String
For i = 0 To numRecs - 1
With anesthRecs(i)
sSQL = "Update Anesth Set fldConcur = .fldConcur, " _
& "fldConcurNum = .fldConcurNum " _
& "WHERE .auto = auto"
DoCmd.RunSQL sSQL
End With
Next i
End Sub

 

[Brendan Reynolds]

I can't be sure that this is the only problem with that code, because you
haven't said what 'anesthRecs' is or where and how it is declared and
initialized, or what the data types of the fields are. With that caveat, one
problem with the code is that you have the references to the properties
inside the quotes when they need to be outside the quotes. Something like
....

sSQL = "Update Anesth Set fldConcur = " & .fldConcur & ", " _
& "fldConcurNum = " & .fldConcurNum & " " _
& "WHERE auto = " & .auto

 

[Guest]

Sorry. I'm fairly new to this and a bit confused. I've written to other
tables without the the & and additonal quotes. I've included that too for
your input. Additional information on this problem is as follows:

AnesthRecs is an array, so I'm basically trying to update my table with
information derrived from my array.
fldConcur = Boolean in table txt
fldConcurNum = Integer in table number
auto = Integer in table number

Here's the other Update that I wrote that works.....

Dim strSQL As String
strSQL = "UPDATE Anesth SET [ip_anesth1] = Trim$([anesth1]), " & _
"[ip_opdate] = [operation-start-date], " & _
"[ip_pttype] = [patient type], " & _
"[ip_svtype] = [service-types], " & _
"[ip_ptmrn] = [pat-mrn], " & _
"[ip_lastname] = [lastname], " & _
"[ip_firstname] = [firstname], " & _
"[ip_Age1] = [Age1], " & _
"[ip_Age] = [Age], " & _
"[ip_ansstart] = [ans-start], " & _
"[ip_ansstop] = [ans-stop], " & _
"[ip_AN1] = Forms!FrmS23.ANESTHESIOLOGIST![AN1], " & _
"[ip_anstype] = Forms!FrmS23.AnesType![ans_antnum], " & _
"[ip_SUR1] = Forms!FrmS23.SURGEON![SUR1], " & _
"[ip_surgeon1] = Forms!FrmS23.SURGEON![surgeon1], " & _
"[ip_rescrna] = Forms!FrmS23.RESCRNA![RESCRNA], " & _
"[ip_rescrnanum] = Forms!FrmS23.RESCRNA![emp_empnum], " & _
"[ip_ansasa] = Forms!FrmS23.AnesType![ans_attempts], " & _
"[ip_preop] = Forms!FrmS23.PROCEDURES![pimem_line], " & _
"[ip_surgdesc] = Forms!FrmS23.SURGDESC![pimem_line] " & _
"WHERE Anesth.[fldpatacct] = [pat-acct];"
DoCmd.RunSQL strSQL

 

[Brendan Reynolds]

OK, that answers one question. All of the data types are numeric, so you
don't need any text or date/time delimiters in the SQL statement. The fact
that AnesthRecs is an array, however, doesn't really tell me much, because I
don't know what is stored in the array or where and how it is declared and
initialized. The use of "With" and "." in the code indicates that what is
stored in the array is not just simple scalar values, but some kind of
object. Could be a custom object, could be an ADO Record, could be something
else - there's no way for me to know.

I understand that if you're new to this stuff, you may not understand all of
the above. I'll list a couple of VBA help topics below that should help to
throw some light on the subject. But it may not be necessary to understand
all of it in order to fix your immediate problem. Try the changes I
suggested. Does the code work with those changes? If not, what happens when
you try?

Here are those help topic titles ...

Understanding the Lifetime of Variables

Understanding Scope and Visibility

Build SQL Statements That Include Variables and Controls

These are Access 2003 VBA help topic titles. If you're using an earlier
version of Access you may not have exactly the same help topics, but you can
use these titles as a source of keywords to search for. If you can't find
what you're looking for in your on-disk help files, try Microsoft's
Assistance web site ...

http://office.microsoft.com/en-us/assistance/CH790018001033.aspx

--
Brendan Reynolds
Access MVP

Sorry. I'm fairly new to this and a bit confused. I've written to other
tables without the the & and additonal quotes. I've included that too for
your input. Additional information on this problem is as follows:

AnesthRecs is an array, so I'm basically trying to update my table with
information derrived from my array.
fldConcur = Boolean in table txt
fldConcurNum = Integer in table number
auto = Integer in table number

Here's the other Update that I wrote that works.....

Dim strSQL As String
strSQL = "UPDATE Anesth SET [ip_anesth1] = Trim$([anesth1]), " & _
"[ip_opdate] = [operation-start-date], " & _
"[ip_pttype] = [patient type], " & _
"[ip_svtype] = [service-types], " & _
"[ip_ptmrn] = [pat-mrn], " & _
"[ip_lastname] = [lastname], " & _
"[ip_firstname] = [firstname], " & _
"[ip_Age1] = [Age1], " & _
"[ip_Age] = [Age], " & _
"[ip_ansstart] = [ans-start], " & _
"[ip_ansstop] = [ans-stop], " & _
"[ip_AN1] = Forms!FrmS23.ANESTHESIOLOGIST![AN1], " & _
"[ip_anstype] = Forms!FrmS23.AnesType![ans_antnum], " & _
"[ip_SUR1] = Forms!FrmS23.SURGEON![SUR1], " & _
"[ip_surgeon1] = Forms!FrmS23.SURGEON![surgeon1], " & _
"[ip_rescrna] = Forms!FrmS23.RESCRNA![RESCRNA], " & _
"[ip_rescrnanum] = Forms!FrmS23.RESCRNA![emp_empnum], " & _
"[ip_ansasa] = Forms!FrmS23.AnesType![ans_attempts], " & _
"[ip_preop] = Forms!FrmS23.PROCEDURES![pimem_line], " & _
"[ip_surgdesc] = Forms!FrmS23.SURGDESC![pimem_line] " & _
"WHERE Anesth.[fldpatacct] = [pat-acct];"
DoCmd.RunSQL strSQL

I can't be sure that this is the only problem with that code, because you
haven't said what 'anesthRecs' is or where and how it is declared and
initialized, or what the data types of the fields are. With that caveat,
one
problem with the code is that you have the references to the properties
inside the quotes when they need to be outside the quotes. Something like
....

sSQL = "Update Anesth Set fldConcur = " & .fldConcur & ", " _
& "fldConcurNum = " & .fldConcurNum & " " _
& "WHERE auto = " & .auto

 

[Guest]

Brendan,
Thanks a lot. I tried your changes and it works great!!!
Sash

OK, that answers one question. All of the data types are numeric, so you
don't need any text or date/time delimiters in the SQL statement. The fact
that AnesthRecs is an array, however, doesn't really tell me much, because I
don't know what is stored in the array or where and how it is declared and
initialized. The use of "With" and "." in the code indicates that what is
stored in the array is not just simple scalar values, but some kind of
object. Could be a custom object, could be an ADO Record, could be something
else - there's no way for me to know.

I understand that if you're new to this stuff, you may not understand all of
the above. I'll list a couple of VBA help topics below that should help to
throw some light on the subject. But it may not be necessary to understand
all of it in order to fix your immediate problem. Try the changes I
suggested. Does the code work with those changes? If not, what happens when
you try?

Here are those help topic titles ...

Understanding the Lifetime of Variables

Understanding Scope and Visibility

Build SQL Statements That Include Variables and Controls

These are Access 2003 VBA help topic titles. If you're using an earlier
version of Access you may not have exactly the same help topics, but you can
use these titles as a source of keywords to search for. If you can't find
what you're looking for in your on-disk help files, try Microsoft's
Assistance web site ...

http://office.microsoft.com/en-us/assistance/CH790018001033.aspx

--
Brendan Reynolds
Access MVP

Sorry. I'm fairly new to this and a bit confused. I've written to other
tables without the the & and additonal quotes. I've included that too for
your input. Additional information on this problem is as follows:

AnesthRecs is an array, so I'm basically trying to update my table with
information derrived from my array.
fldConcur = Boolean in table txt
fldConcurNum = Integer in table number
auto = Integer in table number

Here's the other Update that I wrote that works.....

Dim strSQL As String
strSQL = "UPDATE Anesth SET [ip_anesth1] = Trim$([anesth1]), " & _
"[ip_opdate] = [operation-start-date], " & _
"[ip_pttype] = [patient type], " & _
"[ip_svtype] = [service-types], " & _
"[ip_ptmrn] = [pat-mrn], " & _
"[ip_lastname] = [lastname], " & _
"[ip_firstname] = [firstname], " & _
"[ip_Age1] = [Age1], " & _
"[ip_Age] = [Age], " & _
"[ip_ansstart] = [ans-start], " & _
"[ip_ansstop] = [ans-stop], " & _
"[ip_AN1] = Forms!FrmS23.ANESTHESIOLOGIST![AN1], " & _
"[ip_anstype] = Forms!FrmS23.AnesType![ans_antnum], " & _
"[ip_SUR1] = Forms!FrmS23.SURGEON![SUR1], " & _
"[ip_surgeon1] = Forms!FrmS23.SURGEON![surgeon1], " & _
"[ip_rescrna] = Forms!FrmS23.RESCRNA![RESCRNA], " & _
"[ip_rescrnanum] = Forms!FrmS23.RESCRNA![emp_empnum], " & _
"[ip_ansasa] = Forms!FrmS23.AnesType![ans_attempts], " & _
"[ip_preop] = Forms!FrmS23.PROCEDURES![pimem_line], " & _
"[ip_surgdesc] = Forms!FrmS23.SURGDESC![pimem_line] " & _
"WHERE Anesth.[fldpatacct] = [pat-acct];"
DoCmd.RunSQL strSQL

I can't be sure that this is the only problem with that code, because you
haven't said what 'anesthRecs' is or where and how it is declared and
initialized, or what the data types of the fields are. With that caveat,
one
problem with the code is that you have the references to the properties
inside the quotes when they need to be outside the quotes. Something like
....

sSQL = "Update Anesth Set fldConcur = " & .fldConcur & ", " _
& "fldConcurNum = " & .fldConcurNum & " " _
& "WHERE auto = " & .auto

--
Brendan Reynolds
Access MVP


I have the following code and it's not working. Any suggestions?

Sub UpdateDB(numRecs As Integer)
Dim i As Integer
Dim sSQL As String
For i = 0 To numRecs - 1
With anesthRecs(i)
sSQL = "Update Anesth Set fldConcur = .fldConcur, " _
& "fldConcurNum = .fldConcurNum " _
& "WHERE .auto = auto"
DoCmd.RunSQL sSQL
End With
Next i
End Sub