"Insert into" creating issues

[shankindc]

Hi,
I have a data entry form which opens default values each time the form is open. Requirement is that users can edit existing data in the form. When the form is closed, it shouldnt update the existing record but rather insert a new record.

On the Form-Beforeupdate function, I first check if the record already exists (Primary keys exist). Then I wrote a "Insert into" SQL that picks all values from the form and inserts into the backend table.

When I open the form and enter new Primary key values, I keep getting an error saying I'm trying to insert duplicate records. Funny part is, it inserts the new record in the table.

The logic I have is below:

Set dbMyDB = OpenDatabase("J:\SMG3\SMG3.mdb")
Set rsMyRS = dbMyDB.OpenRecordset("tblProject", dbOpenDynaset)


If Not rsMyRS.EOF Then rsMyRS.MoveFirst
        
     Do While Not rsMyRS.EOF

'Primary keys are ResourceName and Dateval

       If Me.ResourceName = rsMyRS!ResourceName And Me.Dateval = rsMyRS!Dateval Then
       Flag = "Y"
              End If
       rsMyRS.MoveNext
      Loop
    End If
    
    If Flag = "Y" Then
    CountVar = 1 'Not inserting the record if it exists. Just some Dummy variable
        Else
    DoCmd.RunSQL "INSERT INTO tblProject(ProjectName,....) VALUES (Forms!frmProjectEntry!ProjectName,....)"
    'Cancel = True
    'Me.Undo
    End If

Can someone help me with this please?

Thanks!

[Rabbit]

Hi,
I have a data entry form which opens default values each time the form is open. Requirement is that users can edit existing data in the form. When the form is closed, it shouldnt update the existing record but rather insert a new record.

On the Form-Beforeupdate function, I first check if the record already exists (Primary keys exist). Then I wrote a "Insert into" SQL that picks all values from the form and inserts into the backend table.

When I open the form and enter new Primary key values, I keep getting an error saying I'm trying to insert duplicate records. Funny part is, it inserts the new record in the table.

The logic I have is below:

Set dbMyDB = OpenDatabase("J:\SMG3\SMG3.mdb")
Set rsMyRS = dbMyDB.OpenRecordset("tblProject", dbOpenDynaset)
 
 
If Not rsMyRS.EOF Then rsMyRS.MoveFirst
 
Do While Not rsMyRS.EOF
 
'Primary keys are ResourceName and Dateval
 
If Me.ResourceName = rsMyRS!ResourceName And Me.Dateval = rsMyRS!Dateval Then
	Flag = "Y"
End If
rsMyRS.MoveNext
Loop
End If
 
If Flag = "Y" Then
CountVar = 1 'Not inserting the record if it exists. Just some Dummy variable
Else
DoCmd.RunSQL "INSERT INTO tblProject(ProjectName,....) VALUES (Forms!frmProjectEntry!ProjectName,....)"
'Cancel = True
'Me.Undo
End If

Can someone help me with this please?

Thanks!

1) You have an extra End If after your loop.
2) Flag is undefined.
3) If you're just trying to check if an ID exists, just use:

If DCount("NameOfIDField", "tblProject", "NameOfIDField = " & Me.NameOfIDControl.Value) > 0 then ...

[shankindc]

1) You have an extra End If after your loop.
2) Flag is undefined.
3) If you're just trying to check if an ID exists, just use:

If DCount("NameOfIDField", "tblProject", "NameOfIDField = " & Me.NameOfIDControl.Value) > 0 then ...

Hi,
I'm sorry. I had posted only a snapshot of my code. That extra End if was part of an If stmt I had not enclosed in my post. Flag was also initialized earlier. No issues with those.

I tried DCOUNT as follows, based on if a user presses "Yes" button in a popup screen

If lResponse = vbYes Then
  Cancel = False
  If DCount("ResourceName", "tblProject", "ResourceName = " & Me.ResourceName.Value) > 0 And DCount("Dateval", "tblProject", "Dateval = " & Me.Dateval.Value) > 0 Then
  DoCmd.RunSQL "INSERT INTO tblProject(ProjectName,...) VALUES (Forms!frmProjectEntry!ProjectName,...)"
  End If
End If

I keep getting an error saying "Run Time error 2001" You have cancelled the previous operation". When I press Debug, it takes me to the DCount line in my VBA. Am I doing something wrong in the syntax of DCount? I have a composite primary Key - ResourceName and Dateval.

Thanks!

[shankindc]

Hi,
I'm sorry. I had posted only a snapshot of my code. That extra End if was part of an If stmt I had not enclosed in my post. Flag was also initialized earlier. No issues with those.

I tried DCOUNT as follows, based on if a user presses "Yes" button in a popup screen

If lResponse = vbYes Then
Cancel = False
If DCount("Resourc eName", "tblProject ", "ResourceNa me = " & Me.ResourceName .Value) > 0 And DCount("Dateval ", "tblProject ", "Dateval = " & Me.Dateval.Valu e) > 0 Then
DoCmd.RunSQL "INSERT INTO tblProject(Proj ectName,...) VALUES (Forms!frmProje ctEntry!Project Name,...)"
End If
End If

I keep getting an error saying "Run Time error 2001" You have cancelled the previous operation". When I press Debug, it takes me to the DCount line in my VBA. Am I doing something wrong in the syntax of DCount? I have a composite primary Key - ResourceName and Dateval.

Thanks!

---------------------------------------------------------------------------------------------------

I think I had the logic incorrect in my previous post. If the DCOUNT is > 0, it shouldnt insert a row.
That aside, I cannot use DCOUNT here because my primary Key is a composite key and hence DCOUNT of each of the keys can definitely be > 0, but the composite key count has to be = 0 for me to insert a new row.

I feel the issue in the first place is because I'm trying to insert a row and when I close the form, Access form_onClose default event is also trying to insert this row which creates a conflict. Can I override the Form_Close event to not insert or update my record?

[Rabbit]

You can use DCount by doing If DCount = 0 AND DCount = 0 then Insert.

However, if it's bound to the table. Why use an insert at all? If your table is set up so that you can't have duplicate key values, then let the form take care of updating the table. It will bring up its own errors if there's a duplicate key value.

[shankindc]

You can use DCount by doing If DCount = 0 AND DCount = 0 then Insert.

However, if it's bound to the table. Why use an insert at all? If your table is set up so that you can't have duplicate key values, then let the form take care of updating the table. It will bring up its own errors if there's a duplicate key value.

I dont think I could use DCount = 0 AND DCount = 0 as I had explained earlier, there can be multiple occurences of the key fields independently, in my table. But as a composite key, there could be only one instance in my table.

eg: Name Date
John 01/22/07
Peter 01/22/07
John 02/10/08

In this case, if I try to enter a new row say, Peter 02/10/08

DCOUNT (Peter) > 0,
DCOUNT (02/10/08) > 0
Hence I'll not be able to insert a record even though
DCOUNT(Peter AND 02/10/08) = 0

My form should update a record if the record already exists or should insert a new record if the record doesnt exist. hence I need to use some function to perform this

[Rabbit]

Then you can use DCount() = 0 OR DCount() = 0 which would make sure at least one of them is unique.

As far as updating or inserting goes, can't they just scroll to the record that they want to update and scroll to a new record if they want to insert?

If this is not how you want it then don't bind the form to the table.

[shankindc]

Then you can use DCount() = 0 OR DCount() = 0 which would make sure at least one of them is unique.

As far as updating or inserting goes, can't they just scroll to the record that they want to update and scroll to a new record if they want to insert?

If this is not how you want it then don't bind the form to the table.

DCount() = 0 OR DCount() = 0 will not help because it can never be true since I can and will have multiple entries of each key in the table.

Idea behind editing existing fields (and not scrolling to a new record) in the form is bcos a lot of the fields are repetitive over a period of time and so that users need not type all the information from scratch.

Thanks

[Rabbit]

What about

DCount("*", "Table Name", "Name = '" & Me.NameControl & "' AND Date = " & Me.DateControl)

As for the second issue, then you're going to have to unbind the form and have code decide whether to update or insert a record. Although this will require that they retype things from scratch. The problem is that using a bound form will update that record with whatever changes the user makes, even if they meant to insert it as a new record.

With an unbound form, you could load the information from an old record and then they can change it and on the click of a button it would either update or insert a record.

[shankindc]

What about

DCount("*", "Table Name", "Name = '" & Me.NameControl & "' AND Date = " & Me.DateControl)

As for the second issue, then you're going to have to unbind the form and have code decide whether to update or insert a record. Although this will require that they retype things from scratch. The problem is that using a bound form will update that record with whatever changes the user makes, even if they meant to insert it as a new record.

With an unbound form, you could load the information from an old record and then they can change it and on the click of a button it would either update or insert a record.

The logic might work. But thats not my issue in the first place.

If you look at my first thread/post, the suggestion you gave for the second issue is what am trying to do on a form_before update function. But each time I Insert the new record, it gives a PK violation error but inserts the record into the table w/o any issue. I did not have any issues with updating existing records. Hence if I can somehow override the Access form _onClose function to not Insert this record (which i believe is the case) I can get over the duplicate record error popup message.

Thanks

[Rabbit]

I suppose you could just use an On Error Resume Next to just bypass the error from whichever Sub the error is originating from. As long as the error doesn't actually signify an error you have to worry about.

[shankindc]

I suppose you could just use an On Error Resume Next to just bypass the error from whichever Sub the error is originating from. As long as the error doesn't actually signify an error you have to worry about.

My error cannot be done an error trap as it isnt a runtime error (which has a debug/End button) but system error which says duplicate records cannot be inserted.

The one thats frustrating is this "ADD Record" functionality in a form. it updates an old existing record rather than inserting it, when I modify an existing record entry in a form... I'm going to start over this project from scratch to find an alternative. Thanks so much for your time and efforts!!

[NeoPa]

Do you want to have a form that allows the operator to enter details, then determines if a matching record exists and, if it does, then updates it, but if it doesn't, will create a new record?

[NeoPa]

If that is the logic you're trying to follow then :

1. Create a bound form.
2. In the AfterUpdate procedure of each of the composite PK fields :
   1. Call a function which searches for a matching record.
   2. If it exists then select it.
   3. Otherwise leave it in Create New mode
3. Allow the operator to continue entering the data.