Error on AddNew to an empty recordset

[kowalski]

Hi,
I have the following code that is returning a "Runtime error 3058, Index or primary key cannot contain a Null value". Error happens on the Update.

Set db = CurrentDb
Dim rstWattage As Recordset
Set rstWattage = db.OpenRecordset("SELECT [Circuit Operating Load] FROM tblLineID where tblLineID.[Line ID] = '" & strLineID & "'")


If rstWattage.RecordCount = 0 Then
With rstWattage
.AddNew
![Circuit Operating Load] = "0"
.Update
End With
End If

Any ideas?

Thanks in advance!

[pbaldy]

It sounds like there's a required field that isn't being populated.

[kowalski]

Right ... but the recordset is being created from
SELECT [Circuit Operating Load] FROM tblLineID where tblLineID.[Line ID] = '" & strLineID & "
so should it not just have that 1 field?

[pbaldy]

It needs to include whatever fields are required in the table, other than an autonumber field.

[kowalski]

It needs to include whatever fields are required in the table, other than an autonumber field.

OK, to be clearer, I don't want to affect any change on the underlying table. i.e. I want to create a recordset based on some values returned from a query and then manipulate that recordset (AddNew) without updating the table that the initial query was run on.

So the way I see it, the recordset should only have 1 field (Circuit Operating Load). I think you're right in that Access is complaining because my AddNew is actually trying to create a new row in tblLineID but I don't want it to touch tblLineID at all. I just want to add a new row to the recordset.

Cheers.

[rzw0wr]

Set rstWattage = db.OpenRecordset("SELECT [Circuit Operating Load] FROM  tblLineID where tblLineID.[Line ID] = '" & strLineID & "'")

Just open the table without a where clause.

Set tstWattage = db.OpenRecordset("tblLineID")

You are AddNew and it will add it to the end of the recordset.



Dale

[pbaldy]

I've only used AddNew to add records to the table, and to my knowledge that's its purpose. Dale seems to think it will work so I'll get out of the way.

[rzw0wr]

Did it again. I missed this post.

OK, to be clearer, I don't want to affect any change on the underlying table. i.e. I want to create a recordset based on some values returned from a query and then manipulate that recordset (AddNew) without updating the table that the initial query was run on.

So the way I see it, the recordset should only have 1 field (Circuit Operating Load). I think you're right in that Access is complaining because my AddNew is actually trying to create a new row in tblLineID but I don't want it to touch tblLineID at all. I just want to add a new row to the recordset.

Now I hope I understand. You want to look at the field, use the value for something BUT NOT store it in the table.

Open the recordset with the where clause to find the record you want.
Leave out both edit and AddNew AND do not use the Update.

Close the recordset as usual.

Paul, Don't leave me. It scares me when you do.

Dale