Recordset updates crazy

[Billy B]

I am trying to work with recordsets and have the following code. I want it to
add a new record if there are no records in the table and add a new record if
there are existing records. I get duplicate records and can't figure it out.
Would appreciate help. Thank you.

Option Compare Database

Private Sub Form_Load()

Dim strAsk As String
strAsk = UCase(InputBox("Do you want to add a new record?", "Add New?",
"YES"))
DoCmd.OpenForm "frmPlants", acNormal
If strAsk = "YES" Then
DoCmd.GoToRecord , , acNewRec
Else
DoCmd.GoToRecord , , acFirst
End If

End Sub
Private Sub cmdAddNew_Click()

'declare variable and assign address to object variables
Dim Conn As ADODB.Connection, rsPlants As ADODB.Recordset
Dim strSql As String, strLoc As String
Set Conn = Application.CurrentProject.Connection
Set rsPlants = New ADODB.Recordset
strSql = "Select * from tblPlants"
strLoc = Me.lstLoc.Value

' 'open the recordset
rsPlants.Open strSql, Conn, adOpenForwardOnly, adLockPessimistic
'check to see if there is a record in the table
If Not rsPlants.BOF And Not rsPlants.EOF Then
With rsPlants
'if table contains data
' .AddNew
.Fields("PlantName").Value = Me.PlantName
.Fields("BotonName").Value = Me.BotonName
.Fields("PlantType").Value = Me.PlantType
.Fields("GrowthSize").Value = Me.GrowthSize
.Fields("PlantingLoc").Value = strLoc
.Fields("Description").Value = Me.Description
.Fields("Pic").Value = Me.Pic
.Update
End With
Else
'table does not contain data
With rsPlants
.AddNew
.Fields("PlantName").Value = Me.PlantName
.Fields("BotonName").Value = Me.BotonName
.Fields("PlantType").Value = Me.PlantType
.Fields("GrowthSize").Value = Me.GrowthSize
.Fields("PlantingLoc").Value = strLoc
.Fields("Description").Value = Me.Description
.Fields("Pic").Value = Me.Pic
.Update
End With
End If


'after new record, allow user to add new record
' rsPlants.AddNew

Set rsPlants = Nothing
DoCmd.GoToRecord , , acNewRec

End Sub

 

[J_Goddard via AccessMonster.com]

Hi -

First, check your code. If I read it correctly, you have an AddNew button on
your form. When clicked, if there are records in the table, it does not add
a new one right away - it updates whatever the first record happens to be -
the .AddNew is commented out.

But I don't understand your question - you said:

"... I want it to add a new record if there are no records in the table and
add a new record if
there are existing records..."

which means that you want to add a new record whether there are or are not
any existing ones.
So, why bother to check in your code?

Can you clarify this, please?

John