Duplicate record after using Find first...

[Flippertje]

It's absolutely staring me in the face... but i'm clueless (perhaps not a good day to be coding??)

I receive forms with a number, emailadress and an amount. I enter this data in a table 'Tab_Forms' (fields: Indexnr, Emailadress, Amount). The field Indexnr is indexed and no duplicates are allowed to prevent the same form from being entered twice.

I have build a form that has this table as recordset. On Form_Load i use Me.Recordset.Addnew so that it opens blank.
On the form a have a listbox that lists all the forms in the table. On doubleclick on a record in the listbox, i've coded

me.recordset.findfirst "[Indexnr]=" & me.listbox1.value

This places the record in the form. At that point i can start filling fields in the subform tha's also in the form.

After filling the subform i have a button called 'Engage' (Startrekfan). That has a code

me.indexnr.setfocus
me.requery
me.recordset.addnew
me.listbox1.requery

When i click this button i get an error with number 3022 (translated from ducth: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.) The code stopped at Me.Requery


Anyone around that can help me out this (i'm pretty sure) simple problem?

Many tHanks!!!

 

[MajP]

I do not understand your code. What are you attempting with the requery? Why do you never update after the addnew? Makes no sense to me.

 

[Flippertje]

Me.recordset.Requery: an effort to get the recordset to update... which afterwards looks kind of silly .... i deleted the entry
Me.recordset.Addnew: The addnew is just a trick to get the form to be blank again. So an update event is not needed.

I think what's happening is that: the addnew creates a new blank entry. after the doubleclick the formfields get filled with the found fields. After clicking the button it's trying to add the record to the table rather than editing it...

 

[Flippertje]

It WAS staring me in the face.... pfff... better stop coding today... The code was bigger than i reported here. This morning i put an test indexnr like Indexnr=5656866 in the code... forgot to delete it. It caused the form overwrite the current record an create a duplicate record...

Don't know if you get all this.. the outcome is simple... there are days one better can't code

 

[MajP]

I have build a form that has this table as recordset. On Form_Load i use Me.Recordset.Addnew so that it opens blank.
On the form a have a listbox that lists all the forms in the table. On doubleclick on a record in the listbox, i've coded
me.recordset.findfirst "[Indexnr]=" & me.listbox1.value

The add new "trick" is not going to work. You have a bound form, but you are manipulating the recordset behind the scenes. I think you want this effect. You are trying to get an unbound form effect using a bound form. This is not going to work.
1) When the form opens you see the fields, but the form is not set for adding or editing.
2) The user has to navigate to a record for editing
3) not sure how you want to handle adding.

This is how I would do it. Either go unbound all the way or do something like this.

Private Sub cmdSave_Click()
  UnbindControls
End Sub

Private Sub DemoSearch_Click()
  Me.Recordset.FindFirst "OrderID = 10248"
  RebindControls
End Sub

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  UnbindControls
  Set rs = CurrentDb.OpenRecordset("Select * from Orders")
  Set Me.Recordset = rs
End Sub

Public Sub UnbindControls()
  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
     If ctrl.Tag <> "" Then
       ctrl.ControlSource = ""
       ctrl.Enabled = False
     End If
   Next ctrl
End Sub

Public Sub RebindControls()
  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
     If ctrl.Tag <> "" Then
       ctrl.ControlSource = ctrl.Tag
       ctrl.Enabled = True
     End If
   Next ctrl
End Sub

The form opens it is blank and locked for editing (the controls are unbound but the form is bound). When you navigate to a record it rebinds the controls and you can edit.

Anyways for what reason are you binding a recordset to a form? I have never understood this. This is the worst of both worlds. Does not have the flexibility of an unbound form, and has unnecessary overhead.

 

[MajP]

Still your design makes no sense. If you want a blank record that is editable, why not movelast of the reocordset object.