AddNew works in 97 but not 2000

[Achmed]

Hello everybody,

I've been trying to convert a database from Access 97 to 2000. Everything works except I get a Run-time error (3058) when the AddNew method is run. "Index or primary key cannot contain a Null value." When I click on Debug, it highlights the " rs.Update " line.
I'm new with using VBA, but I can't understand why this works in 97 and not 2000! Any ideas?

 

[balor]

I had a problem like this when I converted from 97 to 2000. The problem was that Access 2000 thought that my connections and recordsets was of the type ADODB and not DAO. I had to add "DAO." everywere in the code.
Example:

Dim cnConnection As DAO.Connection
Dim rsRecordset As DAO.Recordset

I don't know if thats your problem but try it.

 

[AccessSQLUser]

A much simpler option is that instead of adding dao everywhere in the code, make sure that in your references DAO is before ADO.

 

[Achmed]

ADO isn't even checked off in the References list. I added it, and made sure it was after DAO but I still have the problem.

 

[Rohdem]

Can you post your code?

Usually I would say you have a primary key or index field in your table that HAS to have a value assigned to it and before you can create a record, for example:

primary key is OrderID

rst.AddNew
rst!item = "Bike"
rst!price = 225.50
rst!update

Since I did not assign a value to 'OrderId', I would get the error.

However, this doesn't explain why it would work in 97 and not 2000. If the problem is truely with the upgrade to 2000, then I would guess that it has something to do with ADO versus DAO.
Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[Achmed]

Here's the code. Some parts may not be of concern, but i didn't want to leave anything out. Thanks for everyone's help.

***Code Start***
Private Sub cboMasterID_AfterUpdate()

Dim rs As Recordset
Dim response As Variant

Me.RecordsetClone.FindFirst "[Master ID] = " & Chr(34) & Me.cboMasterID & Chr(34)
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
response = MsgBox("Case # not Found" & vbCrLf & "Add New Case?", vbYesNo + vbDefaultButton1, "New Case Addition&quot

If response = vbYes Then
Set rs = Me.RecordsetClone
rs.AddNew
rs![MasterID] = Me.cboMasterID
rs![EmploymentID] = Me.cboMasterID
rs![InsurerID] = Me.cboMasterID
rs.Update

Call cboMasterID_AfterUpdate
End If
End If
End Sub
***Code End***

 

[Rohdem]

What is the structure of your table, primary keys, data types, etc? Double check that DAO is above ADO in the references Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[Achmed]

The Table that stores MasterID (ie "AB 123&quot stores only that, and it's the primary key. The other tables each have that ID as the primary and they are linked one-to-one to the MasterID table.

ADO is not checked off, but DAO is and it's above ADO in the list.

Does this help?

 

[Rohdem]

Try this and see if it works:

Set rs = currentdb.openrecordset("maintable&quot
rs.AddNew
rs![MasterID] = Me.cboMasterID
rs.update
Set rs = currentdb.openrecordset("secondtable&quot
rs.AddNew
rs![EmploymentID] = Me.cboMasterID
rs.update
Set rs = currentdb.openrecordset("thirdtable&quot
rs.AddNew
rs![InsurerID] = Me.cboMasterID
rs.Update
rs.close
set rs = nothing Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[Redcatcher]

Look, I haven't seen your programming, but I can tell you this much. When Microsoft developed Access 2000 they changed the entire protocol whereby reference libraries are accessed for the running of VBA. Many commands that worked under 97 cannot be automatically selected by Access when you convert to Access 2000.

You could try opening your mdb file, then opening a form in design mode. Then click the "code" icon on the tool bar. Then select the drop-down menu "tools" and then select "references". Now you will see the "References" window. There will be a checked off list of "available references". See if one of the reference titles that has been checked begins with the word "MISSING". If it does, you will have to go through the entire library below and find a logical substitute library.

That's how I do it and it works. But your case might be different. Good luck.

 

[Achmed]

Mike,

It works! I can now add new records, so it's off to 2000. The only problem is that I have to close and reopen the form each time I want to add a record. If I add two without first closing the form, when I add the second one, it asks twice if I want to add a new case. I say yes, and I get the error that I'm trying to create duplicate values in the index, primary key or relationship. Clicking on Debug, it highlights the first "rs.Update" line.
The way this form is intended to be used, this won't create a problem if I limit it to one, but I'm still curious.

Thanks for everyone's help! This is really great.

-Alan

 

[Achmed]

I should mention that the cases are still added, but I get the error. Seems like it's trying to add the same record twice.

-Alan

 

[Rohdem]

Sounds like you need to requery the form after you add data to it:

Me.requery

I think what is happening is you are adding a record and then trying to find the record in the forms recordsource using 'recordsetclone'. However, the form's data has not been updated with the new record that you just added, so when it searches for it, it doesn't find it and trys to add it again (this is why you get the messagebox twice). I believe a requery after adding data to the table should fix this:


Private Sub cboMasterID_AfterUpdate()

Dim rs As Recordset
Dim response As Variant

Me.RecordsetClone.FindFirst "[Master ID] = " & Chr(34) & Me.cboMasterID & Chr(34)
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
response = MsgBox("Case # not Found" & vbCrLf & "Add New Case?", vbYesNo + vbDefaultButton1, "New Case Addition&quot

If response = vbYes Then
Set rs = currentdb.openrecordset("maintable&quot
rs.AddNew
rs![MasterID] = Me.cboMasterID
rs.update
Set rs = currentdb.openrecordset("secondtable&quot
rs.AddNew
rs![EmploymentID] = Me.cboMasterID
rs.update
Set rs = currentdb.openrecordset("thirdtable&quot
rs.AddNew
rs![InsurerID] = Me.cboMasterID
rs.Update
rs.close
set rs = nothing
Me.requery

Call cboMasterID_AfterUpdate
End If
End If
End Sub
Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[Achmed]

Well... that did it! Thanks again for your help.

-Alan