Solved

MS Access Delete Record If Is Null and Dirty After VBA Insert And Unique Index On. Duplication Error.

Posted on 2016-10-21
13
38 Views
Last Modified: 2016-10-22
II use a FileDialog to browse to a file and insert it into a control. If I was to click the command button and let the FileDialog open and then click cancel this makes my record Null and Dirty. If I was then to click outside of that control into another control Access gives the PopUp Message Error Cause Duplicate entrys in table ProductImages.   I have a Unique Index on the field ProductImageLocalPath.

I have tried several different events with no success.

Here is the code below I last used.
Thanks!

Private Sub ProductImageLocalPath_LostFocus()
    If IsNull(Me.ProductImageLocalPath) Then
      If Me.Dirty Then
        Me.Undo
      Else
        CurrentDb.Execute ("Delete * from ProductImages Where [ProductImageID]=" & Me.ProductImageID)
      End If
    End If
End Sub

Open in new window

0
Comment
Question by:Dustin Stanley
  • 7
  • 6
13 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41854288
Unless you have code somewhere that is forcing a save, this should work.  This event only fires if the control is dirty so you don't need to test for dirty.

Private Sub ProductImageLocalPath_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.ProductImageLocalPath) Then
        Cancel = True
        Me.Undo
    End If
End Sub

Open in new window

0
 

Author Comment

by:Dustin Stanley
ID: 41854355
Changing data in a control by using Visual Basic or a macro containing the SetValue action doesn't trigger these events for the control. However, if you then move to another record or save the record, the form's BeforeUpdate event does occur.

Which I thought this might work once I click to another control but it gives same error message " The changes you requested to the table were not successful they would create duplicates yadda yadda!"

Isn't using the filedialog VBA
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41854361
If the control is unbound, then it isn't dirtying the form.  You must have code in some other event that is dirtying the form and causing the issue.
0
 

Author Comment

by:Dustin Stanley
ID: 41854366
Yes True. I didn't think about that. I have on my Add New Image Button:

Private Sub btnAddImage_Click()
  With Me!sbfrmProductImages.Form!ProductImageLocalPath
  sbfrmProductImages.SetFocus
 RunCommand acCmdRecordsGoToNew
Me!sbfrmProductImages.Form!ProductImageLocalPath = GetFile_Browse
End With
     End Sub

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41854388
Is this an unbound form?  If it is, why not go with the flow and do it the Access way with a bound form?  You'll need code in the Form's BeforeUpdate event to cancel the event if the required field is null or ""
0
 

Author Comment

by:Dustin Stanley
ID: 41854392
Yes it is a subform bounded to the table called ProductImages.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 34

Expert Comment

by:PatHartman
ID: 41854403
Dustin.  Your answer contradicts itself.

The question is "Is it an unbound form" and your answer is yes it is bound.

Either - yes it is unbound
OR no it is bound

Would be clear.

1. If the form is bound, why is the control not bound?
2. Why are you dirtying the form in your add button code?  It is always a bad idea for program code to dirty a form.  It is far better to put any code you have that automatically fills fields into the form's BeforeInsert event.  That event runs ONCE immediately after the user types the first character into any control thereby dirtying the form.  

When YOU dirty the form, you run into many problems having to undo what you did if the user cancels out of the save.
0
 

Author Comment

by:Dustin Stanley
ID: 41854423
Sorry. It is a bounded subform to the ProductImages Table. Control is bounded to a field called ProductImageLocalPath in the ProductImages Table.


I see what you are saying but that was the only option I could find that would work for my add image button. The button is returning the image path and inserting the path into the control on the subform from the main form.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41854499
"Bound" is both present and past tense as it is used in this sense.  "Bounded" isn't a word unless you're talking about how a dog crosses a big field.

If you want to control the code using a button on a different form, then put the code into the click event of the button.  Under no condition should code like this be placed in a LostFocus event.  The LostFocus event has nothing to do with anything except that somehow focus was placed in a control and now focus has moved to another control.  The user might have been simply tabbing through the controls or just clicked into a control.  This type of action should not initiate an update.
0
 

Author Comment

by:Dustin Stanley
ID: 41854715
I am well known for made up words. Can't express myself correctly! I see what you are saying and I agree about the lost focus. I was just thinking of getting it to work more then the later effect which I would have reverted later. I am going to work on this tomorrow. Thank you for your input. I have been trying to code something extra into the Add image button but with my lack of knowledge this is a little tricky. I have made some steps forward but coding it in the correct sequence is the key and tricky to figure out.
0
 

Author Comment

by:Dustin Stanley
ID: 41855428
Ok I finally figured it out!

In my main form with the add image button I put:
Private Sub btnAddImage_Click()
    Forms!frmSKUsEntry!sbfrmProductImages.Form.AddLocalImagePath
End Sub

Open in new window


Which called to the subform with this code in it:
Public Function AddLocalImagePath() As Variant
If Me.NewRecord Then
   Me!ProductImageLocalPath = GetFile_Browse
  DoCmd.GoToControl "sbfrmProductImages"
 Else
   DoCmd.GoToControl "sbfrmProductImages"
    DoCmd.GoToRecord , , acNewRec
   Me!ProductImageLocalPath = GetFile_Browse
 End If
End Function

Open in new window

0
 

Author Closing Comment

by:Dustin Stanley
ID: 41855429
Thank you Pat!
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41855750
YOu're welcome.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now