Link to home
Start Free TrialLog in
Avatar of Dustin Stanley
Dustin Stanley

asked on

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

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

Avatar of PatHartman
PatHartman
Flag of United States of America image

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

Avatar of Dustin Stanley
Dustin Stanley

ASKER

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
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.
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

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 ""
Yes it is a subform bounded to the table called ProductImages.
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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

Thank you Pat!
YOu're welcome.