DLookup is Duplicating Values in Table

[steve21nj]

I am running into an issue when I run an AfterUpdate event on my subform, which is a continuous form (ugh - i know, but couldn't think of an alternative way to do what i wanted). After the event and moving to the next row, it duplicates the table values. Is there a way to prevent this?

What I am attempting to do is scan a bag, then scan another, and another, and..... I need to update certain aspects to the bag as to where it is going.

In the end, I'd like to print a report with all the scanned bags.

Option Compare Database
Option Explicit
 
Private Sub BagNum_AfterUpdate()
'Control Source = Nz(DLookup("Control Source", "my table", "[Control Source after update value] = _
''" & [Control Source after upate value] & "'"))
 
Me.DateIn = Nz(DLookup("DateIn", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.PropertyTypeID = Nz(DLookup("PropertyTypeID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.Notes = Nz(DLookup("Notes", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.PropertyDestroyed = Nz(DLookup("PropertyDestroyed", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.DetentionID = Nz(DLookup("DetentionID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.PropertyID = Nz(DLookup("PropertyID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))

I have attached my sample database with a few pictures of what is happening.

 

[Rx_]

Can you give the steps and identify the duplicate at a step number?
Looking at the Detainee Review subform. Not sure that I can replicate the problem.
I did go through and add the same thing, and sure enough your autocounter table did create a duplicate.
Yes (No Duplicates) On the index for your autocounter- the no duplicates should probably be set. That is going to cause an error if there are already duplicates. I can't make that change due to existing records.

Have not had time to go through all the details, but basically this should be an autocounter with no duplicates. It is just another record. Don't have all the interactive answers from that point. Will let you remove any RI links with duplicates and get back to us.

 

[steve21nj]

I edited the DB on my first post to remove the duplicated values in the table. On 2.jpg, step 4 is when I have entered the bag number and tabbed my way to the next row. When you click or tab to the next row, it adds the duplicate value in the table with the new Property Manifest Number on the duplicated value that is linked from the main frm to sub frm.

 

[Rx_]

After removing the duplicates - did you set the table properties for the autocounter to:
Yes (No Duplicates) On the index for your autocounter

 

[steve21nj]

I had no duplicates originally, later taking it out, and now putting it back. The index now reads:

Indexed – Yes (No Duplicates).

The main issue is that after moving to the next row, it attempts to add the record from the continuous form to the table, rather than updating the record.

I receive the error “The changes you requested to the table were not successful because they would create duplicate values in the index…..” which is understandable.

How do I change it to update the values rather than trying to add it as a new record?

 

[jdraw]

Can you provide some direction?
What are we suppose to look at specifically?
Which forms, what condition????

What exact steps have you taken in trying to debug your "duplication issue"?

Do you have a set of test data to work through a given process to follow each step and look for the "culprit"??

You might want to review the message here regarding posts and questions
http://www.access-programmers.co.uk/forums/showthread.php?t=223418

Good luck with your project

 

[steve21nj]

Jdraw,

From my 1st post where I attached screen shots to duplicate the issue

I am running into an issue when I run an AfterUpdate event on my subform, which is a continuous form (ugh - i know, but couldn't think of an alternative way to do what i wanted). After the event and moving to the next row, it tries to duplicates the table values. Is there a way to prevent this?

What I am attempting to do is scan a bag, then scan another, and another, and..... I need to update certain aspects to the bag as to where it is going.

The idea here is to release the property bag to whomever, and track who it was released to and when. This form is to scan many property bags out at one time, 25+. From here, I will open a report that logs all the property released on that particular event.

Can you provide some direction? sure, the issue is with the frmPropertyTransfer form. After adding text in the field TOT (Name and ID), enter the bag # field of the subform [A1726537]. It will run a Dlookup, after update for this text box. (A1726537 - is a bag number assigned to a test subject).

What are we suppose to look at specifically? when going to the next row, it creates an error about duplicating values. I am looking to only update the values instead of adding. suggestions?

Which forms, what condition???? frmPropertyTransfer, and the subform when entering data after update on the bag #.

What exact steps have you taken in trying to debug your "duplication issue"? I am not sure how to prevent the continuous form from adding the record rather than updating.

Do you have a set of test data to work through a given process to follow each step and look for the "culprit"?? yes, there is sample data inside. the main table where that property data is held is tblPropertyDetails. The form source is tblPropertyManifest.

 

[JHB]

Only a thought:
What about an Update Query instead, and then a refresh on the form?

 

[jdraw]

?? I'm still not clear on what is suppose to happen nor why.
When you're filling in textboxes, it may be more useful to do checks in the BeforeUpdate event. That way you can check things before the Update occurs, and if you find something amiss , you either cancel what you were doing, give a message or message then set the focus somewhere else.

I guess Property Transfer and Turnover are synonymous??