Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

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

0
Dustin Stanley
Asked:
Dustin Stanley
  • 7
  • 6
1 Solution
 
PatHartmanCommented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
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
 
PatHartmanCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Dustin StanleyEntrepreneurAuthor Commented:
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
 
PatHartmanCommented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
Yes it is a subform bounded to the table called ProductImages.
0
 
PatHartmanCommented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
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
 
PatHartmanCommented:
"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
 
Dustin StanleyEntrepreneurAuthor Commented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you Pat!
0
 
PatHartmanCommented:
YOu're welcome.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now