Unintentional AddNew record

[Schaap]

I made a fill in form, when the user clicks the save button the data of the fields will be saved into a new record with .AddNew and .Update. That's working OK. But when I use DoCmd.close at the end of the sub (or I close the form by hand) an empty record is been added to the database with index "0". How can that be ???

The Code :

Private Sub Knop82_Click()

Set Db = CurrentDb()
Set rs = Db.OpenRecordset("select * From Investmentplanning;")
NoInputStr = ""

If Me.NewInvestmentNaam = "" Then
NoInputStr = "Investment Name"
GoTo Else1
Else
With rs
.AddNew
!IndexNumber = IndexNR
!InvestDate = Datum
.Update
MsgBox ("Application Form is saved")
DoCmd.Close
End With
End If
Does anybody have a clou

 

[Crowley16]

hmm...

I'm not 100% sure, but it could be just a syntax problem...

usually I use:

.AddNew
.fields("idxNum") = tbxValue
.fields("...")...
.Update

if that doesn't work, then you might try using a SQL string to add the data to the table...

docmd.setwarnings false
docmd.runsql("INSERT INTO tblName(field1, ...) VALUES(tbxValue, ...);")
docmd.setwarnings true

p.s. what are you trying to do with the GoTo Else1 line?

Procrastinate Now!

 

[Schaap]

Crowley thanx for your reaction.

But what is the difference between the code I use and yours ?

The strange part of all of this is that it worked prevouisly, but I don't know what changed or did wrong !!!

What about the "Goto else1" line that's not important, when there's no input in that particulair field it had to go to line Else1 nad do something else. The code above is just a little piece out of a bigger one.

 

[Crowley16]

the difference between the code is the syntax, like I said, maybe it's just a syntax problem, i.e. you should try it a different way just in case...

the 2nd suggestion is quite different from your code...

as for the goto line, it's just that goto should only be used for error processing, but this is only a guideline...

Procrastinate Now!

 

[RoyVidar]

You sure NewInvestmentNaam contains "" when no new entry? Could it be Null or contain a space?

Try the following test:

[tt]if len(trim$(me("NewInvestmentNaam").value & vbnullstring))=0 then[/tt]

Roy-Vidar

 

[Schaap]

Crowley I used your SQL-string, and I put as field1, etc the fieldnames of the table and as tbxvalues the values that the user filled in on the form (= Me.NewInvestmentNaam, etc). I get inputboxes on the screen that i have to fill in. I filled all these boxes in but it didn't work, there will be no new record in de database !!!

What did I do wrong ?
And again by closing the form I get an empty new record with index "0

 

[Crowley16]

ahh, well, you see, when you pass an sql string to access, it needs to be a string, and if you are putting in string values, then you need to declare them as strings...

i.e. you need to have something similar to:

docmd.runsql("INSERT INTO tblName(field1, field2) VALUES('" & tbxValue1 & "', '" & tbxValue2 & "');"

your best bet is to save the sql into a string, and then msgbox that string to see if the formatting is correct...

Procrastinate Now!

 

[Mute101]

Hi,

While everything Crowley says has merit theres nothing wrong with the way you performed your recordset operation.

You should step through your code and see if you do actually make it into the ELSE part of the IF statement as Roys suggestion may well be the problem.

Sim

----------------------------------------
I was once asked if I was ignorant or just apathetic. I said, "I don't know, and I don't care."
----------------------------------------

 

[Crowley16]

since a new record is added, it suggests that the code does go into the else part...

however that doesn't mean the values of IndexNR is necessarily correct...

so, yes, step through your code and check your values at runtime...


Procrastinate Now!

 

[Mute101]

Ello,

After re-deading the initial question it seems that the recordset operation is actually working as intended. The problem lies in the fact that the form adds another row to the table when it closes...

Have you checked theres no code in the OnClose event?
Stepping throught your code to see if anything happens beyond the DoCmd.Close could help again...

Also if that is the full code you are not closing your recordset after the operation, this can lead to a number of unusual things.

Another point to bear in mind is you are opening the recordset before you actually know if you need it. Whilst not a huge problem on a small system this is bad programming practice.

The code would be more effective written;

Private Sub Knop82_Click()
Dim Db As DataBase
Dim Rs As Recordset
Dim NoInputStr As String

NoInputStr = ""

If Me.NewInvestmentNaam = "" Then
    NoInputStr = "Investment Name"
    GoTo Else1
Else
    Set Db = CurrentDb()
    Set Rs = Db.OpenRecordset("Investmentplanning")
    
    With Rs
        .AddNew
        !IndexNumber = Me.IndexNR
        !InvestDate = Me.Datum
        .Update
    End With
    
    Rs.Close
    Set Rs = Nothing
    MsgBox ("Application Form is saved")
    DoCmd.Close
End If

End Sub

Hopefully something in there may be of use

Simon

----------------------------------------
I was once asked if I was ignorant or just apathetic. I said, "I don't know, and I don't care."
----------------------------------------

 

[Schaap]

I wanna thanx everybody for all the info and suggestions !
But it wasn't the right solution.

The problem was that the standard value of the "Investmentplanning" - table was set to "0". I deleted this value and the problem was solved !!!
It's strange, I don't understand, that that standard value was the cause of adding a new extra record !

Thanx