473,320 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Add record using VBA and SQL

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
11 3306
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Skully Matjas | last post by:
I am using the following code (created by the wizard) to allow to bring my form to a particular entery. But when I edit the entery (ex: put new information into a blank cell), it puts that record...
5
by: Ilan Sebba | last post by:
When it comes to adding records in related tables, Access is really smart. But when I try to do the same using ADO, I am really stupid. Say I have two parent tables (eg Course, Student) and one...
2
by: aaj | last post by:
Hi all I have a small but rather annoying problem with continuos forms, and am wondering if anyone can suggest a method of getting over it. The front end is Access 2002 with the BE being SQL...
4
by: James P. | last post by:
Hello there, I have a bound-form using Navigator so that the user can move back and forth to update record in the form. Every time a record was modified and the user clicks the Navigator to...
5
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example...
1
by: Scott269 | last post by:
So I've got an old MS Works database I imported into Access. I needed a primary key so I created a record number field that was just the record number I manually inserted when I entered it in the...
22
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4....
2
by: Mark Reed | last post by:
Hi All, I have created a multi-user application at work which is working perfectly apart from a small problem which I believe to more a of a user issue (maybe some will set me straight on that...
10
by: MLH | last post by:
Suppose, in a multi-user environment, you have append query SQL in a VBA procedure that looks like INSERT INTO MyTable... and the next line reads MyVar=DMax("","MyTable... You can never be...
7
by: Neil | last post by:
Was working in A2003 and noticed that the Save Record item on the Records menu was not available when the record was not dirty. In A2000, Save Record was always available. (This is a problem for me...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.