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
99 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 38

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 38

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 38

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
 
LVL 38

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 38

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 38

Expert Comment

by:PatHartman
ID: 41855750
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 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