By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Add record using VBA and SQL

P: n/a
I am trying to update a table using VBA and SQL. A form is filled
out
with user ID and PW, then these data are sent to global variables.
The global variables are used to create a record in tblUserActivity.
>From what I've read of previous posts, this should be pretty straight
forward.
Regarding the code below, everythings seems to be working
except when I get to the recordset.update. When execution occurs at
this point, my error routine fires. Is this a primary key field
issue? I have a primary key autonumber field defined for this
table.
------------------------------------------
Public Function WriteAuditTrail()
On Error GoTo Err_WATError

Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
strSQL = "SELECT * FROM tblUserActivity;"
Set rst = db.OpenRecordset(strSQL)

With rst
.AddNew
!PWUserName = gActiveUser
!PWDateTime = Now
!pwActivity = gUserActivity
!pwSecStatus = gSecStatus
.Update
End With

rst.Close

Exit_WriteAuditTrail:
Exit Function

Err_WATError:
MsgBox "There has been a error in recording UserInfo"
Resume Exit_WriteAuditTrail

Set rst = Nothing
Set db = Nothing

End Function
------------------------------------------
Thanks,
Don

Sep 13 '07 #1
Share this Question
Share on Google+
11 Replies


P: n/a
On Thu, 13 Sep 2007 03:19:40 -0000, Don Barton <Do********@gmail.com>
wrote:

The suspense is killing me. What is the Err.Number and Err.Description
when an error occurs?

-Tom.

>I am trying to update a table using VBA and SQL. A form is filled
out
with user ID and PW, then these data are sent to global variables.
The global variables are used to create a record in tblUserActivity.
>>From what I've read of previous posts, this should be pretty straight
forward.
Regarding the code below, everythings seems to be working
except when I get to the recordset.update. When execution occurs at
this point, my error routine fires. Is this a primary key field
issue? I have a primary key autonumber field defined for this
table.
------------------------------------------
Public Function WriteAuditTrail()
On Error GoTo Err_WATError

Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
strSQL = "SELECT * FROM tblUserActivity;"
Set rst = db.OpenRecordset(strSQL)

With rst
.AddNew
!PWUserName = gActiveUser
!PWDateTime = Now
!pwActivity = gUserActivity
!pwSecStatus = gSecStatus
.Update
End With

rst.Close

Exit_WriteAuditTrail:
Exit Function

Err_WATError:
MsgBox "There has been a error in recording UserInfo"
Resume Exit_WriteAuditTrail

Set rst = Nothing
Set db = Nothing

End Function
------------------------------------------
Thanks,
Don
Sep 13 '07 #2

P: n/a
Don Barton wrote:
I am trying to update a table using VBA and SQL. A form is filled
out
with user ID and PW, then these data are sent to global variables.
The global variables are used to create a record in tblUserActivity.
>>From what I've read of previous posts, this should be pretty straight
forward.
Regarding the code below, everythings seems to be working
except when I get to the recordset.update. When execution occurs at
this point, my error routine fires. Is this a primary key field
issue? I have a primary key autonumber field defined for this
table.
------------------------------------------
Public Function WriteAuditTrail()
On Error GoTo Err_WATError

Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
strSQL = "SELECT * FROM tblUserActivity;"
Set rst = db.OpenRecordset(strSQL)

With rst
.AddNew
!PWUserName = gActiveUser
!PWDateTime = Now
!pwActivity = gUserActivity
!pwSecStatus = gSecStatus
.Update
Perhaps one of these variables has a Null value and the tblUserActivity
doesn't allow that? Or maybe there is a Required field here?

gActiveUser, gUserActivity, gSecStatus

We don't know what type these variables where defined as.

One possible solution if this is the problem:

!PWUserName = Nz(gActiveUser, "<Unknown User>")
!PWDateTime = Now
!pwActivity = Nz(gUserActivity, "<Unknown Activity>")
!pwSecStatus = Nz(gSecStatus, "<Unknown Status>")

assuming that these are Text fields in your table. We don't know how you
defined those either.
--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft Access MVP
'--------------------------
Sep 13 '07 #3

P: n/a
One step in troubleshooting is to simply hand enter a row into that
table.
Any problems?
If no trouble, then put watches on those incoming values and hit a
breakpoint to inspect.
Certainly, err.description would help.

HTH
Ananda

Sep 13 '07 #4

P: n/a
On Sep 13, 12:22 am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Thu, 13 Sep 2007 03:19:40 -0000, Don Barton <DonaldB...@gmail.com>
wrote:

The suspense is killing me. What is the Err.Number and Err.Description
when an error occurs?

-Tom.
I am trying to update a table using VBA and SQL. A form is filled
out
with user ID and PW, then these data are sent to global variables.
The global variables are used to create a record in tblUserActivity.
>From what I've read of previous posts, this should be pretty straight
forward.
Regarding the code below, everythings seems to be working
except when I get to the recordset.update. When execution occurs at
this point, my error routine fires. Is this a primary key field
issue? I have a primary key autonumber field defined for this
table.
------------------------------------------
Public Function WriteAuditTrail()
On Error GoTo Err_WATError
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
strSQL = "SELECT * FROM tblUserActivity;"
Set rst = db.OpenRecordset(strSQL)
With rst
.AddNew
!PWUserName = gActiveUser
!PWDateTime = Now
!pwActivity = gUserActivity
!pwSecStatus = gSecStatus
.Update
End With
rst.Close
Exit_WriteAuditTrail:
Exit Function
Err_WATError:
MsgBox "There has been a error in recording UserInfo"
Resume Exit_WriteAuditTrail
Set rst = Nothing
Set db = Nothing
End Function
------------------------------------------
Thanks,
Don
I suppose there's a reason why you would open a recordset and run off
a dozen lines or so rather than run one line (air code)?

DbEngine(0)(0).Execute("INSERT INTO tblUserActivity (PWUserName,
PWDateTime, pwActivity,pwSecStatus)
VALUES
('" & gActiveUser & "', " & Now() & ", " & gUserActivity & ", " &
gSecStatus & ")"

Sep 13 '07 #5

P: n/a
On Sep 13, 7:01 am, lyle <lyle.fairfi...@gmail.comwrote:
On Sep 13, 12:22 am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Thu, 13 Sep 2007 03:19:40 -0000, Don Barton <DonaldB...@gmail.com>
wrote:
The suspense is killing me. What is the Err.Number and Err.Description
when an error occurs?
-Tom.
>I am trying to update a table using VBA and SQL. A form is filled
>out
>with user ID and PW, then these data are sent to global variables.
>The global variables are used to create a record in tblUserActivity.
>>From what I've read of previous posts, this should be pretty straight
>forward.
>Regarding the code below, everythings seems to be working
>except when I get to the recordset.update. When execution occurs at
>this point, my error routine fires. Is this a primary key field
>issue? I have a primary key autonumber field defined for this
>table.
>------------------------------------------
>Public Function WriteAuditTrail()
On Error GoTo Err_WATError
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
strSQL = "SELECT * FROM tblUserActivity;"
Set rst = db.OpenRecordset(strSQL)
With rst
.AddNew
!PWUserName = gActiveUser
!PWDateTime = Now
!pwActivity = gUserActivity
!pwSecStatus = gSecStatus
.Update
End With
>rst.Close
>Exit_WriteAuditTrail:
>Exit Function
>Err_WATError:
>MsgBox "There has been a error in recording UserInfo"
>Resume Exit_WriteAuditTrail
>Set rst = Nothing
>Set db = Nothing
>End Function
>------------------------------------------
>Thanks,
>Don

I suppose there's a reason why you would open a recordset and run off
a dozen lines or so rather than run one line (air code)?

DbEngine(0)(0).Execute("INSERT INTO tblUserActivity (PWUserName,
PWDateTime, pwActivity,pwSecStatus)
VALUES
('" & gActiveUser & "', " & Now() & ", " & gUserActivity & ", " &
gSecStatus & ")"
I intended this to be a reply to the original post, not to Tom.

Sep 13 '07 #6

P: n/a
On Sep 12, 10:22 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Thu, 13 Sep 2007 03:19:40 -0000, Don Barton <DonaldB...@gmail.com>
wrote:

The suspense is killing me. What is the Err.Number and Err.Description
when an error occurs?

-Tom.
Tom,'

Hey Tom,

Why I forgot to add these functions to my Error routine is beyond me.
Had I added them from the beginning, I would have saved myself a
couple of hours. It is a mistake I will not make again. Adding
Err.Number, and Err.Description found the problem immediately. See my
response to John Mshefske below.

Don

Sep 14 '07 #7

P: n/a
On Sep 13, 3:20 am, AnandaSim <Ananda...@gmail.comwrote:
One step in troubleshooting is to simply hand enter a row into that
table.
Any problems?
If no trouble, then put watches on those incoming values and hit a
breakpoint to inspect.
Certainly, err.description would help.

HTH
Ananda
Hi Ananda,

I tried that, and I was able to add a record. The problem stemmed
from not adding the one field that was required, hence I was getting
an error. See my response to John M. above.

Thanks
Don

Sep 14 '07 #8

P: n/a
Don
>
I suppose there's a reason why you would open a recordset and run off
a dozen lines or so rather than run one line (air code)?

DbEngine(0)(0).Execute("INSERT INTO tblUserActivity (PWUserName,
PWDateTime, pwActivity,pwSecStatus)
VALUES
('" & gActiveUser & "', " & Now() & ", " & gUserActivity & ", " &
gSecStatus & ")"- Hide quoted text -

- Show quoted text -
Hi Lyle,

You ask why "run one line (air code)?". Good question. The reason
why I am doing this is to record user activities during their time
spent in the database. Think of it as a poor man's audit trail. For
now, I want to record user Logon, Logoff, and changes to a record (not
field changes). Eventually I will incorporate the routine to include
field changes. By putting the user info in a global variable, then
when ever the user does any of the activities listed above, a record
is created and added to the tblUserActivity. Eventually I will
incorporate the routine to include field changes. From the way you
answered my question, I thought of two questions.
1) Is adding a single record at a time placing alot of overhead that
could slow down the database?
2) Could I possible set up a temp table, and save all the changes to
it, then when the user closes the database all the records in the temp
table are written all to the tblUserActivity? I've never done this
before, but I enjoy what I do, so learning is half the fun.

See my response to John Meshefske above for how I resolved it.

Also, what is air code?

Thanks again for your help

Don

Sep 14 '07 #9

P: n/a
One possible solution if this is the problem:
>
!PWUserName = Nz(gActiveUser, "<Unknown User>")
!PWDateTime = Now
!pwActivity = Nz(gUserActivity, "<Unknown Activity>")
!pwSecStatus = Nz(gSecStatus, "<Unknown Status>")

assuming that these are Text fields in your table. We don't know how you
defined those either.

--
'--------------------------
' John Mishefske
' UtterAccess Editor
John,

Your assumption was correct. I left out recording of the password -
on purpose - because I didn't want to save it in the tblUserActivity.
The problem was I had set it as a required field. Hence it was
throwing an error when I tried to perform rst.Update. Tom's
suggestion to use Err.Description and Err.Number was extremely
helpful. I will not forget to use these in the future. A rookie
mistake for a 50 year old programmer want to be, but I enjoy it just
the same. I also think very highly of UtterAccess. It is definiltey
one of the more helpful sites for Access developers. I also
appreciate you taking on my question and pointing me the right
direction.

Thanks,

Don

Sep 14 '07 #10

P: n/a
Perhaps one of these variables has a Null value and the tblUserActivity
doesn't allow that? Or maybe there is a Required field here?
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft Access MVP
Hi John,
Your assumption about a missing required field was correct. I had
purposesly chosen to not include the UserPassword in the record being
built and written to the tblUserActivity for security reasons.
Unfortunately, I had made the the password field required for this
table. Using Err.Description as Tom suggested pointed me right to
it. Thanks again for all your help and for UtterAccess - an excellent
support site for Access Developers.

Also, as a point of protocol for this group, I will answer everyone in
one posting next time.

Thanks again,

Don
Sep 14 '07 #11

P: n/a
Don Barton wrote:
John,

Your assumption was correct. I left out recording of the password -
on purpose - because I didn't want to save it in the tblUserActivity.
The problem was I had set it as a required field. Hence it was
throwing an error when I tried to perform rst.Update. Tom's
suggestion to use Err.Description and Err.Number was extremely
helpful. I will not forget to use these in the future. A rookie
mistake for a 50 year old programmer want to be, but I enjoy it just
the same. I also think very highly of UtterAccess. It is definiltey
one of the more helpful sites for Access developers. I also
appreciate you taking on my question and pointing me the right
direction.
Don, glad I could assist. The comp.databases.ms-access newsgroup is an
incredible resource with some high-power talent but also a fair bit of
"noise".

UtterAccess, as a moderated forum avoids that "noise" and but some folks
also feel "restricted" in that type of environment. UA has a fair number
of Microsofties and MVPs among the members. I'm glad it is in your list
of resources.

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft Access MVP
'--------------------------
Sep 15 '07 #12

This discussion thread is closed

Replies have been disabled for this discussion.