Suddenly unable to .AddNew to Access Table 2

[Sorwen]

This code does not work. It runs without errors, but no record is ever added to the table. Any ideas what is going on. It looks right to me and is just like code I use in other places, but it doesn't work.

Function addrecfun()
    Dim Con As ADODB.Connection
    Dim rsTable As ADODB.Recordset
    Dim strSQL As String
    Dim preOut As String

    Set Con = CurrentProject.Connection
    Set rsTable = New ADODB.Recordset

    strSQL = ""
    strSQL = strSQL & "Select *"
    strSQL = strSQL & " From zTestTable2"
    strSQL = strSQL & " Where (TotalType = 'Data1Total');"
        
    With rsTable
        .Open strSQL, Con, adOpenDynamic, adLockBatchOptimistic
        
        If .BOF = False And .EOF = False Then
            .MoveLast
            preOut = .Fields("CurrentData").Value
        Else
            preOut = 0
        End If
        .AddNew
        .Fields("Location").Value = "1"
        .Fields("TotalType").Value = "Data1Total"
        .Fields("Date").Value = "4"
        .Fields("PreviousData").Value = preOut
        .Update
        .Close
    End With
    
End Function

-I hate Microsoft!
-Forever and always forward.

 

[abaldwin]

Is your field DATE a date/time field or some other type of field?


Just for kicks try replacing this

.AddNew
.Fields("Location").Value = "1"
.Fields("TotalType").Value = "Data1Total"
.Fields("Date").Value = "4"
.Fields("PreviousData").Value = preOut
.Update
.Close

with this
strSql = "INSERT INTO zTestTable2 (Location, TotalType, [Date], PreviousData) VALUES ('1', 'Data1Total', '4', '" & preout & "');"

con.execute strsql

Or you could just add the [] brackets around your Date Field i.e. "[Date]" in your original code. Date is a reserved word and is not good for field names. This may be causing your problem. No errors returned with your code?

Also try removing the .value from after the field names.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Sorwen]

Sorry the old one was curDate. I tried recreating it just to see and it still didn't work. Removed .Value and that didn't help. I've also used !Location, !TotalType, etc with no luck. I wanted to, and have done, using only the one sql statement. What I really need to know is why the .AddNew isn't working all of the sudden. On all of stuff I've written before today the .AddNew still works on it. For some reason it is only on what I write today. I even put the code in a fresh database and still it doesn't work.

-I hate Microsoft!
-Forever and always forward.

 

[abaldwin]

Are your other applications using .addnew working, is it only this db that is not?

What versions of Access?


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Sorwen]

No, it is the other application too. The functions in it that I wrote before today work, but nothing I write today does.

-I hate Microsoft!
-Forever and always forward.

 

[abaldwin]

Not that this should really matter but try moving the .close outside the with statement.

I.e.

End with
rstables.close


I have no clue as to why it suddenly stopped working. This has me stumped as well. I was just looking through some of my old code and found that for some reason I always closed my recordset after I closed it outside the with. Now I always use sql statmenets and the execute.

Andy


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[SuryaF]

Is it working if you try adding it manually?

 

[Sorwen]

Are you talking about if I go to the table and type it in, then yes. It seems to only have a problem with any new .AddNew.

-I hate Microsoft!
-Forever and always forward.

 

[abaldwin]

Just grasping at straws now.

What are the field types in the database design for the fields
Location
TotalType
Date
PreviousData



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[SuryaF]

Try creating a Query with your SQL phrase "Select * From zTestTable2 Where (TotalType = 'Data1Total')". Open the query and try adding a new line to it with exactly the values you have in your procedure.
Is it working?

 

[Sorwen]

abaldwin
Location (Number)
TotalType (Text)
Date (Date)
PreviousData (Decimal)

The original, before I tried just writing to the test one, has an autonumber field, but the zTestTable2 does not. So that isn't a problem, but could become one if we ever hit on why I can't add to the zTestTable2 either.


SuryaF
Yes I can type data fine. Not an issue with the tables as far as I can tell. They accept data from me, from insert queries, and old code that has .AddNew fine. That's why I thought surely I wasn't typing something right and just couldn't see what it was. It makes absolutly no sense.


-I hate Microsoft!
-Forever and always forward.

 

[abaldwin]

The date field in your original code is being given a value of "4".

This would be invalid data for this field.

Try changing your code from "4" to "4/16/06" and see what happens.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Sorwen]

Sorry, I didn't realize I changed them all to text fields for the second test (zTestTable2) so that I wouldn't reduce posible problems. Really sorry and thanks for all the help.

-I hate Microsoft!
-Forever and always forward.

 

[abaldwin]

Do you have an ftp site or some way of transferring this db to me. I guess I am out of ideas and obviously everyone else is stumped as well.

My only option is to
1. Try this on another machine with the same version of Office
or
2. Get the db to me and let me see if it runs for me.

To sum this up so far.

Code that is reasonably formed is not running on this machine.
Code that was previously working on this machine is no longer running on this machine.
The fields are now ALL text fields.

Record is not being added after the code above runs error free.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[abaldwin]

BTW, how are you calling this function. Please copy and paste your call statement.

thans


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Sorwen]

The last test I run just from the code. It is now the only code in a totally new database.

-I hate Microsoft!
-Forever and always forward.

 

[abaldwin]

Think I hunted down the problem

Function addrecfun()
Dim Con As ADODB.Connection
Dim rsTable As ADODB.Recordset
Dim strSQL As String
Dim preOut As String

Set Con = CurrentProject.Connection
Set rsTable = New ADODB.Recordset

strSQL = ""
strSQL = strSQL & "Select *"
strSQL = strSQL & " From zTestTable2"
strSQL = strSQL & " Where (TotalType = 'Data1Total');"

With rsTable
.Open strSQL, Con, adOpenDynamic, adLockOptimistic


If .BOF = False And .EOF = False Then
.MoveLast
preOut = .Fields("CurrentData").Value
Else
preOut = 0
End If
.AddNew
.Fields("Location").Value = "1"
.Fields("TotalType").Value = "Data1Total"
.Fields("Currentdata").Value = "4"
.Fields("PreviousData").Value = preOut
.Update
.Close
End With


Notice the item in the code iin bold. Also in my code here I changed the field names to match the table I created. You may need to mess with them.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Sorwen]

If it is that I'll shoot myself. I usually use that adLockOptimistic. I'll check.

-I hate Microsoft!
-Forever and always forward.

 

[Sorwen]

*BANG* Thank you so much that was it. I knew it had to be something totally stupid I did. THANKS!!!

-I hate Microsoft!
-Forever and always forward.

 

[abaldwin]

Sorry it took so long to figure out. Just passed over it on you one record insert.

Glad to put this on to rest though.

Andy Baldwin

"Testing is the most overlooked programming language on the books!