Stuck with code........

[JustMaybe]

Hi can anyone help me please???

I have a button that when pressed copies some fields from one table to another. However if when these details are changed on the form, if this button is clicked, i get an error saying that this record cannot be copied as it is a duplicate of a primary key value.

I want to write soem code so that the record will either be created or will be edited if that primary key value is already existent!

A VB programmer (not in access though) told me to use a if statement. Yet on these forums someone told me to try dlookup function.
I am really confused as to where to look to solve this problem.. can anyone give me some advice?

this is my present code.

Private Sub Command20_Click()

Dim rsLicense As Recordset
Dim rsOrderDetails As Recordset
Dim rsOrders As Recordset
'
Dim sDetailID As String
'
Set rsOrderDetails = Application.CurrentDb.OpenRecordset("order details", dbOpenDynaset, dbPessimistic)
Set rsLicense = Application.CurrentDb.OpenRecordset("License")
Set rsOrders = Application.CurrentDb.OpenRecordset("Orders")
'
rsOrderDetails.MoveLast
rsOrderDetails.MoveFirst
'
sDetailID = Me.DetailID
rsOrderDetails.FindFirst ("DetailID = " & sDetailID)
rsLicense.AddNew
'
rsLicense("DetailID") = rsOrderDetails("DetailID")
'
rsLicense("73 Serial No") = rsOrderDetails("SerialNO")
'
rsLicense("Product Name") = rsOrderDetails("ProductID")
'
rsLicense("AMP Renewal Date") = rsOrderDetails("RenewalDate")
'
rsLicense("End-User") = rsOrders("EndUser")
'
rsLicense("Reseller") = rsOrders("Re-Seller")
'
rsLicense("Location") = rsOrders("ShipCity")
'
rsLicense.Update
'
rsOrderDetails.CLOSE
rsLicense.CLOSE
'
End Sub

any ideas would be greatly appreciated!

thanks
Sarah

 

[The_Doc_Man]

What worries me is that you don't seem comfortable with code, though that is the only way I know to handle this problem. I don't want to make your problem worse.

Having said that, the theory is, in outline form,....

1. Don't allow the form to be bound to a recordset.

2. When you are ready to save the data from the form, use a command button to activate your code.

3. In that code, start with a DCount of the number of records that match the primary key's value.

4. Prepare a recordset to the table that you want to either update or append.

4. If the DCount result was zero, you are entering this record for the first time. Just do a recordset.AddNew, then start defining the contents of the fields.

5. If the DCount result was one, you have to do something a bit more complex. You need to know the name of the primary key of the table. Select the primary key index using the recordset.Index property, then execute the .Seek method to find the existing record. (Look at both of those methods in the Help files.) When you have found the right record, do a recordset.Edit on it.

6. If the DCount result was greater than one or less than zero, you are screwed badly because you have a duplicate entry among your Primary Key (=NoDuplicates).

7. Once you are done with the record, do a .Update on it. Don't forget to eventually close the recordset.

That should do it for you.

 

[sambo]

Hey Doc,

Some time ago I stumbled upon the method you described above. It has always worked, so I have always used it.
The only flaw I found has been in efficiency (or, I should say, lack thereof). Working with unbound forms kind of takes away from one of Access' main functionalities.

But... On the same token, one of Access' major drawbacks is its inability to update,or edit, data on forms that are bound to Multi-Table queries.

Because I find that most of the forms that I build ARE bound to multiple tables, I find myself more and more doing away with recordsets all together.

In the end I am left with a bunch of unbound forms. The upside is, through the use of some clever code, I can do whatever I want with the data entered on the form, and the user doesn't know the difference.

I just wanted to get someone else's take on using unbound forms to combat what would be very complicated recordsets.

Thanks..