update & record

R

RipperT

Original question: If the value in a form field will be
periodically changed(updated), how can I record the new
value, along with the date and time of the update, in
another table? I'm using Access 2002.

Someone suggested the following:

Use VBA DAO (or ADO) code to write a new Record to the
other Table.

The code would be something like this (air) code:

Dim db as DAO.Database
Dim rs as DAO.RecordSet
Set db = CurrentDB()
Set rs = db.OpenRecordset("anotherTable")
rs.AddNew
rs("FirstFld") = Me!txtFirstFld
rs("UpdatedDateTime") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

I used this suggested code,and tweaked it for my
application. I don't know code (clearly) but I'm not an
idiot. I can make this work with you guys' help.

Where:

"seg" is the name of the database
"Lock changes" is the name of the table I want to write to
"Lock" is the field in "Lock changes" I want to update
from a (separate) form field
"Date" is the field in "Lock changes" I want the date
(Now) to show up in each time the value in the form field
is changed

Private Sub Lock_Exit(Cancel As Integer)
Dim db As DAO.seg
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Lock changes")
rs.AddNew
rs("Lock") = Me!txtLock
rs("Date") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

I get a compile error each time I leave the field. How do
I fix this code?

Thanx!
Rip
 
A

ABL

RipperT said:
Original question: If the value in a form field will be
periodically changed(updated), how can I record the new
value, along with the date and time of the update, in
another table? I'm using Access 2002.

Someone suggested the following:

Use VBA DAO (or ADO) code to write a new Record to the
other Table.

The code would be something like this (air) code:

Dim db as DAO.Database
Dim rs as DAO.RecordSet
Set db = CurrentDB()
Set rs = db.OpenRecordset("anotherTable")
rs.AddNew
rs("FirstFld") = Me!txtFirstFld
rs("UpdatedDateTime") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

I used this suggested code,and tweaked it for my
application. I don't know code (clearly) but I'm not an
idiot. I can make this work with you guys' help.

Where:

"seg" is the name of the database
"Lock changes" is the name of the table I want to write to
"Lock" is the field in "Lock changes" I want to update
from a (separate) form field
"Date" is the field in "Lock changes" I want the date
(Now) to show up in each time the value in the form field
is changed

Private Sub Lock_Exit(Cancel As Integer)
Dim db As DAO.seg
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Lock changes")
rs.AddNew
rs("Lock") = Me!txtLock
rs("Date") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

I get a compile error each time I leave the field. How do
I fix this code?

Thanx!
Rip
Click to expand...
The errors I can see (off the top of my head) are:
Dim db as DAO.Database NOT DAO.seg
Assuming you are working within the same db then
set db= currentDB() is okay
rs("Lock") should be rs!Lock or rs![Lock]
rs("Date") sould be rs!Date or rs![Date]

If it still doesn't work, try
set rs = db.OpenRecordset([Lock changes]) , although I think it's
correct the way it is.

Good luck!

--Alden

PLEASE do not reply directly to me. This email address is for trapping
unsolicited emails.
 
R

RipperT

Thank-you, I've tried each of your suggestions and with
each I continue to get:

"Compile error - user-defined type not defined"

With this, the following line in the code is highlighted:

Dim db As DAO.Database

What am I missing?
Thanx!
Rip

-----Original Message-----
RipperT said:
Original question: If the value in a form field will be
periodically changed(updated), how can I record the new
value, along with the date and time of the update, in
another table? I'm using Access 2002.

Someone suggested the following:

Use VBA DAO (or ADO) code to write a new Record to the
other Table.

The code would be something like this (air) code:

Dim db as DAO.Database
Dim rs as DAO.RecordSet
Set db = CurrentDB()
Set rs = db.OpenRecordset("anotherTable")
rs.AddNew
rs("FirstFld") = Me!txtFirstFld
rs("UpdatedDateTime") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

I used this suggested code,and tweaked it for my
application. I don't know code (clearly) but I'm not an
idiot. I can make this work with you guys' help.

Where:

"seg" is the name of the database
"Lock changes" is the name of the table I want to write to
"Lock" is the field in "Lock changes" I want to update
from a (separate) form field
"Date" is the field in "Lock changes" I want the date
(Now) to show up in each time the value in the form field
is changed

Private Sub Lock_Exit(Cancel As Integer)
Dim db As DAO.seg
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Lock changes")
rs.AddNew
rs("Lock") = Me!txtLock
rs("Date") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

I get a compile error each time I leave the field. How do
I fix this code?

Thanx!
Rip
Click to expand...
The errors I can see (off the top of my head) are:
Dim db as DAO.Database NOT DAO.seg
Assuming you are working within the same db then
set db= currentDB() is okay
rs("Lock") should be rs!Lock or rs![Lock]
rs("Date") sould be rs!Date or rs![Date]

If it still doesn't work, try
set rs = db.OpenRecordset([Lock changes]) , although I think it's
correct the way it is.

Good luck!

--Alden

PLEASE do not reply directly to me. This email address is for trapping
unsolicited emails.
.
Click to expand...
 
A

ABL

Make sure that the "o" in DAO is an oh, not a zero. Also, go to
Tools-->References and make sure that "Microsoft DAO 3.6 Object Library" (or
something similar) is checked. If those two items don't fix it, I'm
stumped!

Good Luck,
Alden

RipperT said:
Thank-you, I've tried each of your suggestions and with
each I continue to get:

"Compile error - user-defined type not defined"

With this, the following line in the code is highlighted:

Dim db As DAO.Database

What am I missing?
Thanx!
Rip

-----Original Message-----
RipperT said:
Original question: If the value in a form field will be
periodically changed(updated), how can I record the new
value, along with the date and time of the update, in
another table? I'm using Access 2002.

Someone suggested the following:

Use VBA DAO (or ADO) code to write a new Record to the
other Table.

The code would be something like this (air) code:

Dim db as DAO.Database
Dim rs as DAO.RecordSet
Set db = CurrentDB()
Set rs = db.OpenRecordset("anotherTable")
rs.AddNew
rs("FirstFld") = Me!txtFirstFld
rs("UpdatedDateTime") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

I used this suggested code,and tweaked it for my
application. I don't know code (clearly) but I'm not an
idiot. I can make this work with you guys' help.

Where:

"seg" is the name of the database
"Lock changes" is the name of the table I want to write to
"Lock" is the field in "Lock changes" I want to update
from a (separate) form field
"Date" is the field in "Lock changes" I want the date
(Now) to show up in each time the value in the form field
is changed

Private Sub Lock_Exit(Cancel As Integer)
Dim db As DAO.seg
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Lock changes")
rs.AddNew
rs("Lock") = Me!txtLock
rs("Date") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

I get a compile error each time I leave the field. How do
I fix this code?

Thanx!
Rip
Click to expand...
The errors I can see (off the top of my head) are:
Dim db as DAO.Database NOT DAO.seg
Assuming you are working within the same db then
set db= currentDB() is okay
rs("Lock") should be rs!Lock or rs![Lock]
rs("Date") sould be rs!Date or rs![Date]

If it still doesn't work, try
set rs = db.OpenRecordset([Lock changes]) , although I think it's
correct the way it is.

Good luck!

--Alden

PLEASE do not reply directly to me. This email address is for trapping
unsolicited emails.
.
Click to expand...
Click to expand...
 
R

RipperT

Referencing the object library did it. Thanx for your
help!

Ripper

-----Original Message-----
Make sure that the "o" in DAO is an oh, not a zero. Also, go to
Tools-->References and make sure that "Microsoft DAO 3.6 Object Library" (or
something similar) is checked. If those two items don't fix it, I'm
stumped!

Good Luck,
Alden

Thank-you, I've tried each of your suggestions and with
each I continue to get:

"Compile error - user-defined type not defined"

With this, the following line in the code is highlighted:

Dim db As DAO.Database

What am I missing?
Thanx!
Rip

-----Original Message-----
RipperT wrote:

Original question: If the value in a form field will be
periodically changed(updated), how can I record the new
value, along with the date and time of the update, in
another table? I'm using Access 2002.

Someone suggested the following:

Use VBA DAO (or ADO) code to write a new Record to the
other Table.

The code would be something like this (air) code:

Dim db as DAO.Database
Dim rs as DAO.RecordSet
Set db = CurrentDB()
Set rs = db.OpenRecordset("anotherTable")
rs.AddNew
rs("FirstFld") = Me!txtFirstFld
rs("UpdatedDateTime") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

I used this suggested code,and tweaked it for my
application. I don't know code (clearly) but I'm not an
idiot. I can make this work with you guys' help.

Where:

"seg" is the name of the database
"Lock changes" is the name of the table I want to write to
"Lock" is the field in "Lock changes" I want to update
from a (separate) form field
"Date" is the field in "Lock changes" I want the date
(Now) to show up in each time the value in the form field
is changed

Private Sub Lock_Exit(Cancel As Integer)
Dim db As DAO.seg
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Lock changes")
rs.AddNew
rs("Lock") = Me!txtLock
rs("Date") = Now
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

I get a compile error each time I leave the field.
Click to expand...
How
do
I fix this code?

Thanx!
Rip

The errors I can see (off the top of my head) are:
Dim db as DAO.Database NOT DAO.seg
Assuming you are working within the same db then
set db= currentDB() is okay
rs("Lock") should be rs!Lock or rs![Lock]
rs("Date") sould be rs!Date or rs![Date]

If it still doesn't work, try
set rs = db.OpenRecordset([Lock changes]) , although I think it's
correct the way it is.

Good luck!

--Alden

PLEASE do not reply directly to me. This email
Click to expand...
address
is for trapping
unsolicited emails.
.
Click to expand...
Click to expand...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.736 / Virus Database: 490 - Release Date: 8/9/04


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NotInList not firing ? 1
Assigning work to team 1 and team 2 5
Getting message when modifying 2
forms Coding 6
Multi Select List Box in Access 0
Syntax error (missing operator) 4
error referencing a subform control 2
What is wrong? Can you interpret this for me? 5

Top