By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,361 Members | 3,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,361 IT Pros & Developers. It's quick & easy.

"Insert into" creating issues

P: 12
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:

Expand|Select|Wrap|Line Numbers
  1. Set dbMyDB = OpenDatabase("J:\SMG3\SMG3.mdb")
  2. Set rsMyRS = dbMyDB.OpenRecordset("tblProject", dbOpenDynaset)
  3.  
  4.  
  5. If Not rsMyRS.EOF Then rsMyRS.MoveFirst
  6.  
  7.      Do While Not rsMyRS.EOF
  8.  
  9. 'Primary keys are ResourceName and Dateval
  10.  
  11.        If Me.ResourceName = rsMyRS!ResourceName And Me.Dateval = rsMyRS!Dateval Then
  12.        Flag = "Y"
  13.               End If
  14.        rsMyRS.MoveNext
  15.       Loop
  16.     End If
  17.  
  18.     If Flag = "Y" Then
  19.     CountVar = 1 'Not inserting the record if it exists. Just some Dummy variable
  20.         Else
  21.     DoCmd.RunSQL "INSERT INTO tblProject(ProjectName,....) VALUES (Forms!frmProjectEntry!ProjectName,....)"
  22.     'Cancel = True
  23.     'Me.Undo
  24.     End If

Can someone help me with this please?

Thanks!
Feb 13 '07 #1
Share this Question
Share on Google+
13 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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:

Expand|Select|Wrap|Line Numbers
  1. Set dbMyDB = OpenDatabase("J:\SMG3\SMG3.mdb")
  2. Set rsMyRS = dbMyDB.OpenRecordset("tblProject", dbOpenDynaset)
  3.  
  4.  
  5. If Not rsMyRS.EOF Then rsMyRS.MoveFirst
  6.  
  7. Do While Not rsMyRS.EOF
  8.  
  9. 'Primary keys are ResourceName and Dateval
  10.  
  11. If Me.ResourceName = rsMyRS!ResourceName And Me.Dateval = rsMyRS!Dateval Then
  12.     Flag = "Y"
  13. End If
  14. rsMyRS.MoveNext
  15. Loop
  16. End If
  17.  
  18. If Flag = "Y" Then
  19. CountVar = 1 'Not inserting the record if it exists. Just some Dummy variable
  20. Else
  21. DoCmd.RunSQL "INSERT INTO tblProject(ProjectName,....) VALUES (Forms!frmProjectEntry!ProjectName,....)"
  22. 'Cancel = True
  23. 'Me.Undo
  24. End If
  25.  
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:
Expand|Select|Wrap|Line Numbers
  1. If DCount("NameOfIDField", "tblProject", "NameOfIDField = " & Me.NameOfIDControl.Value) > 0 then ... 
Feb 13 '07 #2

P: 12
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:
Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. If lResponse = vbYes Then
  2.   Cancel = False
  3.   If DCount("ResourceName", "tblProject", "ResourceName = " & Me.ResourceName.Value) > 0 And DCount("Dateval", "tblProject", "Dateval = " & Me.Dateval.Value) > 0 Then
  4.   DoCmd.RunSQL "INSERT INTO tblProject(ProjectName,...) VALUES (Forms!frmProjectEntry!ProjectName,...)"
  5.   End If
  6. 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!
Feb 13 '07 #3

P: 12
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!
---------------------------------------------------------------------------------------------------

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?
Feb 13 '07 #4

Rabbit
Expert Mod 10K+
P: 12,366
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.
Feb 13 '07 #5

P: 12
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
Feb 13 '07 #6

Rabbit
Expert Mod 10K+
P: 12,366
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.
Feb 13 '07 #7

P: 12
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
Feb 13 '07 #8

Rabbit
Expert Mod 10K+
P: 12,366
What about
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 13 '07 #9

P: 12
What about
Expand|Select|Wrap|Line Numbers
  1. 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
Feb 14 '07 #10

Rabbit
Expert Mod 10K+
P: 12,366
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.
Feb 14 '07 #11

P: 12
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!!
Feb 14 '07 #12

NeoPa
Expert Mod 15k+
P: 31,494
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?
Feb 14 '07 #13

NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 14 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.