Preventing Duplicating Values With Dlookup

[steve21nj]

Happy Friday All!

My issue: I have a DLookup-After Update event on a continuous subform that will not allow me to edit the values and continue to the next row. I can edit the record persay but when attempting to move on to the next row in the subform to pull another record, it tries to save the current record as a new record to the table (duplicating the value) rather than simply updating the existing information. It then generates an error about duplicate values.

What the form does: The main form act as a manifest. Each row of my subform has a property item, to which belong to separate individuals. The manifest has a date indicating what date the items were sent out along with other individuals property. For example

Main Form Data:
Date: 1/4/13
Sent By : Steve

Subform Data:
BagNum: 77878, DateIn 1/2/13, PropertyTypeID 1, DetentionID 28999, PropertyID 33
BagNum: 77879, DateIn 1/1/13, PropertyTypeID 2, DetentionID 32998, PropertyID 34
BagNum: 77881, DateIn 12/1/12, PropertyTypeID 1, DetentionID 31959, PropertyID 35
BagNum: 77876, DateIn 1/1/13, PropertyTypeID 1, DetentionID 38919, PropertyID 39

*bold indicates text boxes

I have a main form [frmPropertyTransfer] with record source from a table [tblPropertyManifest] with a continuous subform [SubfrmPropertyTransfer] with a record source from a select query [qryPropertyTransfer]. The main form is a manifest for items being shipped out where the Parent to Child relationship is:

frmPropertyTransfer – PropertyManifestID
SubfrmProeprtyTransfer – PropertyManifest_fk

On my subform, I have an After Update event:

[SIZE=3][FONT=Arial]Private Sub BagNum_AfterUpdate()[/FONT][/SIZE]
[SIZE=3][FONT=Arial]'Control Source = Nz(DLookup("Control Source", "my table", "[Control Source after update value] = _[/FONT][/SIZE]
[SIZE=3][FONT=Arial]''" & [Control Source after upate value] & "'"))[/FONT][/SIZE]
[SIZE=3][FONT=Arial]Me.DateIn = Nz(DLookup("DateIn", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))[/FONT][/SIZE]
[SIZE=3][FONT=Arial]Me.PropertyTypeID = Nz(DLookup("PropertyTypeID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))[/FONT][/SIZE]
[SIZE=3][FONT=Arial]Me.DetentionID = Nz(DLookup("DetentionID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))[/FONT][/SIZE]
[SIZE=3][FONT=Arial]Me.PropertyID = Nz(DLookup("PropertyID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))[/FONT][/SIZE]
[SIZE=3][FONT=Arial]End Sub[/FONT][/SIZE]

I haven’t had luck with a previous post last month and attempting to explain the situation again.

Thank you in advance!

 

[JHB]

[FONT=&quot]If you could convert the database to a MS-Access 2000 version.
[/FONT]

 

[steve21nj]

JHB,
Attached is a 2000 and 2003 version.

Pat,
Maybe that is why I am having trouble, but I thought foreign keys were the only duplicate values / relationships i have among my tables. I am stumped as to what is needed to be changed.

When opening the database, I have arrows indicating the steps. Basically my table [tblPropertyDetails] will be updated from my manifest in [tblPropertyManifeset]. I need the ability to edit several records in the subform to show that the property has been released. I thought my query [qryPropertyTransfer] did what you are suggesting.

Any help would be glady appreciated.

 

[JHB]

You are trying to update/set a value for an autonumber field, (Me.PropertyID), therefore you are getting error.

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.PropertyStatus = Nz(DLookup("PropertyStatus", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.DetentionID = Nz(DLookup("DetentionID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
'Remove this line below
'Me.PropertyID = Nz(DLookup("PropertyID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))

End Sub

 

[steve21nj]

Thank you for the reply. I tried that earlier but it still writes the data to the table as a new record. I only want to update the exisiting record. I would need to edit the TurnoverTo, DateOut, and PropertyStatus of each record in the subform.

For example on my attachment, the PropertyID is 36 for BagNum A1726537. Once I remove the PropertyID from the lookup, it assigns another ID. So now I have two identical bag numbers with different PropertyID's, where I do not want duplicates of neither the ID or Bag.

Am I going about this wrong?

In the sample database above, I have four records in the [tblPropertyDetails]. Number 30, 31, 32, and 36.
If i wanted to assign a manifest (meaning release all the property back to the owners right now), I would open my form [frmPropertyTransfer] and scan each bag to show that all four bags were released at this time on this day. They would all have the same PropertyManifest_fk.

I have also tried indexing the bagnum with the PropertyID removed from the afterupdate event. This also creates an error of duplicating information.

Now four property bags might not be a big deal. I could easily go in and update one by one. But eventually I will have 50 bags at one time to scan out. So my information will show on this date at this time, these 50 bags were released.

I hope that helps. And thank you again.

 

[JHB]

Sorry I've misunderstood what you want.
You can't do it on that way, because as soon you start putting value in, you are creating a new record in the detail table.
I've made a solution for you, try if it fit's you request.

 

[steve21nj]

I will need more time to test. Can you explain what you did, and the idea of the dummie table to help me better understand the flow!
As of now, it works pretty darn good!

 

[JHB]

The idea of ​​Dummy table is to get a table where you can enter Bag# and then the other values ​​are automatically inserted without being created a record in the detail table.
The problem with the way you have the intent was that as soon as you start typing Bag# then creates a new record in the detail table.

With help of Bag# in Dummy table, you can then find the corresponding Bag# value ​​in the detail table and update the values ​​that have changed, and insert the value of "PropertyManifestID" so you have a link back to the responsible record in "tblPropertyManifest" table.
And the update was what you intended, it just can not be done in the way you intended it, because of creating the new record.

 

[steve21nj]

So far everything works great. One question, would it be possible for me to move:

Private Sub Form_AfterUpdate()
  Dim dbs As Database
 
  Set dbs = CurrentDb
 
    dbs.Execute ("UPDATE tblPropertyDetails INNER JOIN tblPropertyDetailsDummy ON tblPropertyDetails.PropertyID = tblPropertyDetailsDummy.PropertyID SET tblPropertyDetails.DateIn = [tblPropertyDetailsDummy].[DateIn], " _
  & "tblPropertyDetails.PropertyTypeID = [tblPropertyDetailsDummy].[PropertyTypeID], tblPropertyDetails.DateOut = [tblPropertyDetailsDummy].[DateOut], tblPropertyDetails.PropertyStatus = [tblPropertyDetailsDummy].[PropertyStatus], tblPropertyDetails.TransferSite = [tblPropertyDetailsDummy].[TransferSite], " _
  & "tblPropertyDetails.TurnoverTo = [tblPropertyDetailsDummy].[TurnoverTo], tblPropertyDetails.DeleteRow = [tblPropertyDetailsDummy].[DeleteRow], tblPropertyDetails.PropertyManifest_fk = [tblPropertyDetailsDummy].[PropertyManifest_fk] " _
  & "WHERE tblPropertyDetailsDummy.PropertyID=" & "" & Me.PropertyID & "")
End Sub

To the click and save command:

Private Sub Command8_Click()
'insert dbs.Execute here
 
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
End Sub

I ask because if a user scans a bag by accident, there is no way to remove the bag before changes are permanent.

I have a after click event on the delete where I could still remove the line in the dummy table before it becomes permanent to the table. Thoughts?

Private Sub Check265_Click()
RunCommand acCmdSaveRecord
If MsgBox("Delete selected Property?", vbQuestion + vbYesNo, "Delete Selected Records") = vbNo Then Exit Sub
CurrentDb.Execute "DELETE * FROM tblPropertyDetailsDummy WHERE DeleteRow = True"
Forms!frmPropertyTransfer!SubfrmPropertyTransfer.Form.Requery
Forms!frmPropertyTransfer!SubfrmPropertyTransfer.Form.BagNum.SetFocus
 
End Sub

 

[JHB]

Try it, if it gives you problem, then tell/show what it is!

 

[steve21nj]

I got a compile error: Method or Data member not found?

Any suggestions

Private Sub Command8_Click()
Dim dbs As Database
 
  Set dbs = CurrentDb
 
    dbs.Execute ("UPDATE tblPropertyDetails INNER JOIN tblPropertyDetailsDummy ON tblPropertyDetails.PropertyID = tblPropertyDetailsDummy.PropertyID SET tblPropertyDetails.DateIn = [tblPropertyDetailsDummy].[DateIn], " _
  & "tblPropertyDetails.PropertyTypeID = [tblPropertyDetailsDummy].[PropertyTypeID], tblPropertyDetails.DateOut = [tblPropertyDetailsDummy].[DateOut], tblPropertyDetails.PropertyStatus = [tblPropertyDetailsDummy].[PropertyStatus], tblPropertyDetails.TransferSite = [tblPropertyDetailsDummy].[TransferSite], " _
  & "tblPropertyDetails.TurnoverTo = [tblPropertyDetailsDummy].[TurnoverTo], tblPropertyDetails.DeleteRow = [tblPropertyDetailsDummy].[DeleteRow], tblPropertyDetails.PropertyManifest_fk = [tblPropertyDetailsDummy].[PropertyManifest_fk] " _
  & "WHERE tblPropertyDetailsDummy.PropertyID=" & "" & Me.PropertyID & "")
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
End Sub

 

[JHB]

Yes I know what it is Me.PropertyID is unknown from the form where the button is placed on.
So it has to be:

Me.SubfrmPropertyTransfer.Form.PropertyID

 

[steve21nj]

That fixed it!

I noticed when I scan a second bag, it enters the information into the table [tblPropertyDetailsDummy] with no issue, but it will not update both records in the table [tblPropertyDetails], it will only update the last record scanned, leaving the previous unchanged even though the changes are located in [tblPropertyDetailsDummy]. Any idea why it would change the last record scanned but not the first? I attempted to scan 4 bags, all four went to the dummy table but only the last bag scanned was changed in the main table.

 

[JHB]

Yes it is clear, because you change when you want to update the table from each time a bag is scanned till now, update after all bags are scanned.
So write your query so that the table is updated for all bags that is in the Dummy table

 

[steve21nj]

Do I need to rewrite the query or VBA? I tried doing the following vba but received an error: Object Variable or With Block variable not set.

Private Sub Command8_Click()
Dim dbs As Database
Dim rst As Recordset
 
  Set rst = dbs.OpenRecordset("UpdateProperty")
  rst.MoveFirst
  Do Until rst.EOF
 
    dbs.Execute ("UPDATE tblPropertyDetails INNER JOIN tblPropertyDetailsDummy ON tblPropertyDetails.PropertyID = tblPropertyDetailsDummy.PropertyID SET tblPropertyDetails.DateIn = [tblPropertyDetailsDummy].[DateIn], " _
  & "tblPropertyDetails.PropertyTypeID = [tblPropertyDetailsDummy].[PropertyTypeID], tblPropertyDetails.DateOut = [tblPropertyDetailsDummy].[DateOut], tblPropertyDetails.PropertyStatus = [tblPropertyDetailsDummy].[PropertyStatus], tblPropertyDetails.TransferSite = [tblPropertyDetailsDummy].[TransferSite], " _
  & "tblPropertyDetails.TurnoverTo = [tblPropertyDetailsDummy].[TurnoverTo], tblPropertyDetails.DeleteRow = [tblPropertyDetailsDummy].[DeleteRow], tblPropertyDetails.PropertyManifest_fk = [tblPropertyDetailsDummy].[PropertyManifest_fk] " _
  & "WHERE tblPropertyDetailsDummy.PropertyID=" & "" & Me.SubfrmPropertyTransfer.Form.PropertyID & "")
 
  rst.MoveNext
 
  Loop
  dbs.Close
  rst.Close
 
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
End Sub

I renamed the update query that you created because it showed a strange name when I opened it. I titled it UpdateProperty.

Any thoughts?

 

[JHB]

You've removed the line:

Set dbs = CurrentDb

I don't know what you now are doing, but if you want to update the table with all the records from the Dummy table, then ... , (and remove the rst:.... from the code)

UPDATE tblPropertyDetails INNER JOIN tblPropertyDetailsDummy ON (tblPropertyDetails.BagNum = tblPropertyDetailsDummy.BagNum) AND (tblPropertyDetails.PropertyID = tblPropertyDetailsDummy.PropertyID) SET tblPropertyDetails.DateIn = [tblPropertyDetailsDummy].[DateIn], tblPropertyDetails.PropertyTypeID = [tblPropertyDetailsDummy].[PropertyTypeID], tblPropertyDetails.DateOut = [tblPropertyDetailsDummy].[DateOut], tblPropertyDetails.TransferSite = [tblPropertyDetailsDummy].[TransferSite], tblPropertyDetails.TurnoverTo = [tblPropertyDetailsDummy].[TurnoverTo], tblPropertyDetails.DeleteRow = [tblPropertyDetailsDummy].[DeleteRow], tblPropertyDetails.PropertyManifest_fk = [tblPropertyDetailsDummy].[PropertyManifest_fk];

 

[steve21nj]

It still doesn't loop the record. It will only take the last entry.

UPDATE tblPropertyDetails INNER JOIN tblPropertyDetailsDummy ON
 (tblPropertyDetails.PropertyID = tblPropertyDetailsDummy.PropertyID) AND
 (tblPropertyDetails.BagNum = tblPropertyDetailsDummy.BagNum) SET
 tblPropertyDetails.DateIn = [tblPropertyDetailsDummy].[DateIn],
 tblPropertyDetails.PropertyTypeID = [tblPropertyDetailsDummy].
[PropertyTypeID], tblPropertyDetails.DateOut = [tblPropertyDetailsDummy].
[DateOut], tblPropertyDetails.TransferSite = [tblPropertyDetailsDummy].
[TransferSite], tblPropertyDetails.TurnoverTo = [tblPropertyDetailsDummy].[TurnoverTo], tblPropertyDetails.DeleteRow = [tblPropertyDetailsDummy].
[DeleteRow], tblPropertyDetails.PropertyManifest_fk = 
[tblPropertyDetailsDummy].[PropertyManifest_fk];

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
    Dim dbs As Database
 
    Set dbs = CurrentDb
 
    dbs.Execute ("UPDATE tblPropertyDetails INNER JOIN tblPropertyDetailsDummy ON tblPropertyDetails.PropertyID = tblPropertyDetailsDummy.PropertyID SET tblPropertyDetails.DateIn = [tblPropertyDetailsDummy].[DateIn], " _
  & "tblPropertyDetails.PropertyTypeID = [tblPropertyDetailsDummy].[PropertyTypeID], tblPropertyDetails.DateOut = [tblPropertyDetailsDummy].[DateOut], tblPropertyDetails.PropertyStatus = [tblPropertyDetailsDummy].[PropertyStatus], tblPropertyDetails.TransferSite = [tblPropertyDetailsDummy].[TransferSite], " _
  & "tblPropertyDetails.TurnoverTo = [tblPropertyDetailsDummy].[TurnoverTo], tblPropertyDetails.DeleteRow = [tblPropertyDetailsDummy].[DeleteRow], tblPropertyDetails.PropertyManifest_fk = [tblPropertyDetailsDummy].[PropertyManifest_fk] " _
  & "WHERE tblPropertyDetailsDummy.PropertyID=" & "" & Me.SubfrmPropertyTransfer.Form.PropertyID & "")
 
 
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
Exit_Command8_Click:
    Exit Sub
Err_Command8_Click:
    MsgBox "This action cannot be completed at this time, please try again."
    DoCmd.Close
    Resume Exit_Command8_Click
End Sub

Did i miss something?

 

[JHB]

Where did you put the query? It should replace the update query in you code! Remember to set all the necessary " and & and _ + dbs.Execute (" and at last ")"

 

[steve21nj]

I am still runing into the issue of it only updating one line rather than several. Any other suggestions?

Private Sub Command8_Click()
    Dim dbs As Database
    Set dbs = CurrentDb
 
dbs.Execute ("UPDATE tblPropertyDetails INNER JOIN tblPropertyDetailsDummy ON (tblPropertyDetails.PropertyID = tblPropertyDetailsDummy.PropertyID) AND (tblPropertyDetails.BagNum = tblPropertyDetailsDummy.BagNum) SET tblPropertyDetails.DateIn = [tblPropertyDetailsDummy].[DateIn], " _
& "tblPropertyDetails.PropertyTypeID = [tblPropertyDetailsDummy].[PropertyTypeID], tblPropertyDetails.DateOut = [tblPropertyDetailsDummy].[DateOut], tblPropertyDetails.PropertyStatus = [tblPropertyDetailsDummy].[PropertyStatus], tblPropertyDetails.TransferSite = [tblPropertyDetailsDummy].[TransferSite], " _
& "tblPropertyDetails.TurnoverTo = [tblPropertyDetailsDummy].[TurnoverTo],tblPropertyDetails.DeleteRow = [tblPropertyDetailsDummy].[DeleteRow], tblPropertyDetails.PropertyManifest_fk =[tblPropertyDetailsDummy].[PropertyManifest_fk] " _
& "WHERE tblPropertyDetailsDummy.PropertyID=" & "" & Me.SubfrmPropertyTransfer.Form.PropertyID & "")
 
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
End Sub

 

[JHB]

You are not using the this:

UPDATE tblPropertyDetails INNER JOIN tblPropertyDetailsDummy ON
(tblPropertyDetails.PropertyID = tblPropertyDetailsDummy.PropertyID) AND
(tblPropertyDetails.BagNum = tblPropertyDetailsDummy.BagNum) SET
tblPropertyDetails.DateIn = [tblPropertyDetailsDummy].[DateIn],
tblPropertyDetails.PropertyTypeID = [tblPropertyDetailsDummy].
[PropertyTypeID], tblPropertyDetails.DateOut = [tblPropertyDetailsDummy].
[DateOut], tblPropertyDetails.TransferSite = [tblPropertyDetailsDummy].
[TransferSite], tblPropertyDetails.TurnoverTo = [tblPropertyDetailsDummy].[TurnoverTo], tblPropertyDetails.DeleteRow = [tblPropertyDetailsDummy].
[DeleteRow], tblPropertyDetails.PropertyManifest_fk =
[tblPropertyDetailsDummy].[PropertyManifest_fk];

As i wrote: It should replace the update query in you code! Remember to set all the necessary " and & and _ + dbs.Execute (" and at last ")"
The above update query will update them all.