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

[Kosmos]

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'v e 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 DaysToCompletio n() As Long

'Opening tblContracts as recSet1 and recSet2
Dim con1 As ADODB.Connectio n
Dim con2 As ADODB.Connectio n
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 "tblContrac ts", 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
'"tblWhateverYo urTableNameIs", con1 which means
'connection1 and then to open a field in that recordset you type
'recSet1.Fields ("fieldname" ))
x = 0

recSet1.MoveFir st
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.MoveNex t
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

[MMcCarthy]

Try this ...

      recSet2.Edit
      recSet2.Fields("DateDifference") = UntilCompletion
      recSet2.Update
 

Mary

[Kosmos]

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

[Kosmos]

this has been my solution so far

Do While FigureOut=False
SlamHeadOnDesk
Loop

[Kosmos]

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"

[Kosmos]

Ahh finally I figured it out
Here is the code...I figure it may be useful in some way or another...espec ially considering this line "recSet2.Op en "tbldtdiff" , con2, adOpenKeyset, adLockOptimisti c" is very confusing...the re 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 DaysToCompletio n() As Long

'Opening tblContracts as recSet1
Dim con1 As ADODB.Connectio n
Dim con2 As ADODB.Connectio n
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 "tblContrac ts", con1
recSet2.Open "tbldtdiff" , con2, adOpenKeyset, adLockOptimisti c

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
'"tblWhateverYo urTableNameIs", con1 which means
'connection1 and then to open a field in that recordset you type
'recSet1.Fields ("fieldname" ))
x = 0

recSet1.MoveFir st
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.MoveNex t
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

[NeoPa]

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 DaysToCompletio n() defined?
You declare a reference to it but it doesn't seem to be defined anywhere.

[NeoPa]

what the heck does lock optimistic mean? lol

LockTypeEnum
Specifies the type of lock placed on records during editing.

Constant Value Description
adLockBatchOpti mistic 4 Indicates optimistic batch updates. Required for batch update mode.
adLockOptimisti c 3 Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.
adLockPessimist ic 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.
adLockUnspecifi ed -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.

[Kosmos]

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!!!!

[NeoPa]

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.