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

Updating a table from a module - Why won't this work?

100+
P: 153
Hey guys...the following code doesn't seem to work refering to the line

recSet2.Fields("DateDifference") = UntilCompletion
It says the field is not updatable...I've never updated a table from a module before so I'm not sure if I am doing this right but the code is pasted below (the variable 'x' has nothing to do with anything as of now, it's something I will need later)(also no need to read the commentary it's more for my own purposes since this is still a learning experience for me):

Public Function DaysToCompletion() As Long

'Opening tblContracts as recSet1 and recSet2
Dim con1 As ADODB.Connection
Dim con2 As ADODB.Connection
Dim recSet1 As ADODB.Recordset
Dim recSet2 As ADODB.Recordset
Set con1 = CurrentProject.Connection
Set con2 = CurrentProject.Connection
Set recSet1 = New ADODB.Recordset
Set recSet2 = New ADODB.Recordset
recSet1.Open "tblContracts", con1

recSet2.Open "tbldtdiff", con2
Dim x As Long

'Declaring UntilCompletion as the amount of days until completion
Dim UntilCompletion As Long

'Looping until EOF (until the last record for EndDate in tblContracts...so
'someone else would have declared recSet1.Open
'"tblWhateverYourTableNameIs", con1 which means
'connection1 and then to open a field in that recordset you type
'recSet1.Fields("fieldname"))
x = 0

recSet1.MoveFirst
Do Until recSet1.EOF
' End Date must be in quotes or will not work
UntilCompletion = DateDiff("d", Date, recSet1.Fields("EndDate"))
Debug.Print UntilCompletion
recSet2.Fields("DateDifference") = UntilCompletion
recSet1.MoveNext
x = (x + 1)
Loop

'Clearing recSet1 and Con1 (Connection1)
recSet1.Close
recSet2.Close
con1.Close
con2.Close
Set con1 = Nothing
Set con2 = Nothing
Set recSet1 = Nothing
Set recSet2 = Nothing

End Function
Dec 18 '06 #1
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1.       recSet2.Edit
  2.       recSet2.Fields("DateDifference") = UntilCompletion
  3.       recSet2.Update
  4.  
Mary
Dec 19 '06 #2

100+
P: 153
Thanks...I have tried this but it has not worked for me...do I need a special library to run this code? It says it's out of range of, at least, my declared public variable?

Well actually this is the exact error message:
Method or data member not found (Error 461)
but it refers to that first line of code that declares the public variable...this is something that only happens once I enter this code
Dec 19 '06 #3

100+
P: 153
this has been my solution so far

Do While FigureOut=False
SlamHeadOnDesk
Loop
Dec 19 '06 #4

100+
P: 153
Furthermore I guess I don't have the option of .edit but rather .editmode and when I try that I get the error "Invalid Use of Property"
Dec 19 '06 #5

100+
P: 153
Ahh finally I figured it out
Here is the code...I figure it may be useful in some way or another...especially considering this line "recSet2.Open "tbldtdiff", con2, adOpenKeyset, adLockOptimistic" is very confusing...there aren't enough places that explain these options when opening a recordset...I just went through all the options and it worked finally....what the heck does lock optimistic mean? lol

Option Compare Database

Public Function DaysToCompletion() As Long

'Opening tblContracts as recSet1
Dim con1 As ADODB.Connection
Dim con2 As ADODB.Connection
Dim recSet1 As ADODB.Recordset
Dim recSet2 As ADODB.Recordset
Set con1 = CurrentProject.Connection
Set con2 = CurrentProject.Connection
Set recSet1 = New ADODB.Recordset
Set recSet2 = New ADODB.Recordset
recSet1.Open "tblContracts", con1
recSet2.Open "tbldtdiff", con2, adOpenKeyset, adLockOptimistic

Dim x As Long

'Declaring UntilCompletion as the amount of days until completion
Dim UntilCompletion As Long

'Looping until EOF (until the last record for EndDate in tblContracts...so
'someone else would have declared recSet1.Open
'"tblWhateverYourTableNameIs", con1 which means
'connection1 and then to open a field in that recordset you type
'recSet1.Fields("fieldname"))
x = 0

recSet1.MoveFirst
Do Until recSet1.EOF
' End Date must be in quotes or will not work
UntilCompletion = DateDiff("d", Date, recSet1.Fields("EndDate"))
Debug.Print UntilCompletion
recSet2.AddNew
recSet2.Fields("DateDifference") = UntilCompletion
recSet2.Update
recSet1.MoveNext
x = (x + 1)
Loop

'Clearing recSet1 and Con1 (Connection1) and recSet 2 and Con1
recSet1.Close
recSet2.Close
con1.Close
con2.Close
Set con1 = Nothing
Set con2 = Nothing
Set recSet1 = Nothing
Set recSet2 = Nothing

End Function
Dec 19 '06 #6

NeoPa
Expert Mod 15k+
P: 31,489
Thanks...I have tried this but it has not worked for me...do I need a special library to run this code? It says it's out of range of, at least, my declared public variable?

Well actually this is the exact error message:


but it refers to that first line of code that declares the public variable...this is something that only happens once I enter this code
Where is DaysToCompletion() defined?
You declare a reference to it but it doesn't seem to be defined anywhere.
Dec 20 '06 #7

NeoPa
Expert Mod 15k+
P: 31,489
what the heck does lock optimistic mean? lol
LockTypeEnum
Specifies the type of lock placed on records during editing.

Constant Value Description
adLockBatchOptimistic 4 Indicates optimistic batch updates. Required for batch update mode.

adLockOptimistic 3 Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.
adLockPessimistic 2 Indicates pessimistic locking, record by record. The provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately after editing.
adLockReadOnly 1 Indicates read-only records. You cannot alter the data.
adLockUnspecified -1 Does not specify a type of lock. For clones, the clone is created with the same lock type as the original.


This should help explain that.
Dec 20 '06 #8

100+
P: 153
Yep, thanks...I posted the entire module here: http://www.thescripts.com/forum/thread579007.html

thought someone could use it in the future

once I figured that lock stuff out I just had one of those moments when everything came together and code started spewing out of my fingers and sweat glands....heck I even peed the longest do while loop ever ....I feel empowered!!!!
Dec 20 '06 #9

NeoPa
Expert Mod 15k+
P: 31,489
Yep, thanks...I posted the entire module here: http://www.thescripts.com/forum/thread579007.html

thought someone could use it in the future

once I figured that lock stuff out I just had one of those moments when everything came together and code started spewing out of my fingers and sweat glands....heck I even peed the longest do while loop ever ....I feel empowered!!!!
lol

Good for you Kosmos.
Dec 27 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.