My table, "ItemMaster" has a unique index "ItemDescription". It has
other keys, including an autonumber Primary Key. Table's been around
for awhile.
In vba, I open up rstItemMaster as a DAO recordset on the Item Master
as a dynaset. First, I check to see if my (excel-based) transaction
has a duplicate item description. If it does not, I decide whether to
Add or Update the new transaction base on the presence or absence of
other keys. In all cases, though, I rely on the fact that I've already
checked to see if there is an existing record in the table with that
descripiton (see code below):
The problem I'm having is that the initial
"rstItemMaster.FindFirst...." action is not finding a match, even
though there is absolutely a matching description already resident in
the table. As a result, when I go to update a different (but already
existing) record with this description, I get an error indicating I'm
trying to add a duplicate record.
I've copied and pasted the exact same criteria from the
rstItemMaster.FindFirst method to a "DLookup" in the immediate window
(to maintain the exact same context...), and the DLookup FINDS the
duplicate record. I will admit to struggling a little with the
preponderance of double quotes in my descriptions, but the fact that I
can copy and paste it exactly into the DLookup suggests the quotes
aren't the issue. (FYI, I'm also cursed with lots of descriptions
containing single quotes as well....)
Ideas? Resources?
*************Code**************
Dim rstItemMaster As DAO.Recordset
Set rstItemMaster = CurrentDb.OpenRecordset("tblItemMaster",
dbOpenDynaset)
With xlSh
While lngConsecutiveBlankRows < 4
If Len(RTrim(LTrim(.Cells(lngCurrentRowNbr, 5)))) = 0
Then 'ACC# in Column 5 (E) and must not be blank
lngConsecutiveBlankRows = lngConsecutiveBlankRows + 1
Else
Debug.Print "Row " & lngCurrentRowNbr
.Cells(lngCurrentRowNbr, PropQty) = 1
.Cells(lngCurrentRowNbr, UnitColumn) = 1
If IsNumeric(.Cells(lngCurrentRowNbr,
ExtendedCostColumn)) Then
If Len(RTrim(LTrim(.Cells(lngCurrentRowNbr, 4))))
= 0 Then
.Cells(lngCurrentRowNbr, CommentColumn) =
"Blank Descriptions not allowed -- rejected"
Else
rstItemMaster.MoveFirst
rstItemMaster.FindFirst "ItemDescription=""" &
Replace(.Cells(lngCurrentRowNbr, 4), Quote, Quote & Quote) & Quote
If rstItemMaster.NoMatch Then
rstItemMaster.Filter =
"ItemThirdPartyVendor=3"
rstItemMaster.MoveFirst
rstItemMaster.FindFirst
"ItemThirdPartyVendorPartNbr='" & .Cells(lngCurrentRowNbr, 5) & "'"
If rstItemMaster.NoMatch
Then 'Must be an Add
'******************ADD******************************
rstItemMaster.AddNew
rstItemMaster!ItemDescription
= .Cells(lngCurrentRowNbr, 4)
rstItemMaster!ItemUOM
= .Cells(lngCurrentRowNbr, 3)
rstItemMaster!ItemEUOM = rstItemMaster!
ItemUOM
rstItemMaster!ItemEUOMFactor = 1
rstItemMaster!ItemMaterialStdUnitCost
= Round(.Cells(lngCurrentRowNbr, ExtendedCostColumn), 2)
rstItemMaster!ItemLaborStdUnitHours =
0
rstItemMaster!ItemVendor
= .Cells(lngCurrentRowNbr, 9)
rstItemMaster!ItemVendorPartNbr
= .Cells(lngCurrentRowNbr, 10)
rstItemMaster!ItemTab = "MCDean"
rstItemMaster!ItemThirdPartyVendor = 3
rstItemMaster!
ItemThirdPartyVendorPartNbr = .Cells(lngCurrentRowNbr, 5) 'ACC#
rstItemMaster.Update
rstItemMaster.Bookmark =
rstItemMaster.LastModified
.Cells(lngCurrentRowNbr,
CommentColumn) = "Added ID=" & rstItemMaster!ItemID
Else
'*****************Update*****************************
.Cells(lngCurrentRowNbr,
CommentColumn) = "Updating ID=" & rstItemMaster!ItemID
rstItemMaster.Edit
rstItemMaster!ItemDescription
= .Cells(lngCurrentRowNbr, 4)
rstItemMaster!ItemUOM
= .Cells(lngCurrentRowNbr, 3)
rstItemMaster!ItemEUOM = rstItemMaster!
ItemUOM
rstItemMaster!ItemEUOMFactor = 1
rstItemMaster!ItemMaterialStdUnitCost
= Round(.Cells(lngCurrentRowNbr, ExtendedCostColumn), 2)
rstItemMaster!ItemLaborStdUnitHours =
0
rstItemMaster!ItemVendor
= .Cells(lngCurrentRowNbr, 9)
rstItemMaster!ItemVendorPartNbr
= .Cells(lngCurrentRowNbr, 10)
rstItemMaster!ItemTab = "MCDean"
rstItemMaster!ItemThirdPartyVendor = 3
rstItemMaster!
ItemThirdPartyVendorPartNbr = .Cells(lngCurrentRowNbr, 5) 'ACC#
rstItemMaster.Update
rstItemMaster.Bookmark =
rstItemMaster.LastUpdated
.Cells(lngCurrentRowNbr,
CommentColumn) = "Updating ID=" & rstItemMaster!ItemID
End If
Else
If rstItemMaster!ItemThirdPartyVendor = 3
And rstItemMaster!ItemThirdPartyVendorPartNbr
= .Cells(lngCurrentRowNbr, 5) Then
'*****************Update*****************************
rstItemMaster.Edit
rstItemMaster!ItemDescription
= .Cells(lngCurrentRowNbr, 4)
rstItemMaster!ItemUOM
= .Cells(lngCurrentRowNbr, 3)
rstItemMaster!ItemEUOM = rstItemMaster!
ItemUOM
rstItemMaster!ItemEUOMFactor = 1
rstItemMaster!ItemMaterialStdUnitCost
= Round(.Cells(lngCurrentRowNbr, ExtendedCostColumn), 2)
rstItemMaster!ItemLaborStdUnitHours =
0
rstItemMaster!ItemVendor
= .Cells(lngCurrentRowNbr, 9)
rstItemMaster!ItemVendorPartNbr
= .Cells(lngCurrentRowNbr, 10)
rstItemMaster!ItemTab = "MCDean"
rstItemMaster!ItemThirdPartyVendor = 3
rstItemMaster!
ItemThirdPartyVendorPartNbr = .Cells(lngCurrentRowNbr, 5) 'ACC#
rstItemMaster.Update
.Cells(lngCurrentRowNbr,
CommentColumn) = "Updating Item ID=" & rstItemMaster!ItemID
Else
.Cells(lngCurrentRowNbr,
CommentColumn) = "This Item Description is not unique. Same desc as
ID=" & rstItemMaster!ItemID & "ACC#=" & rstItemMaster!
ItemThirdPartyVendorPartNbr & " -- Rejected"
End If
End If
End If
Else
.Cells(lngCurrentRowNbr, CommentColumn) = "Invalid
Extended Cost Column -- Rejected"
End If
End If
.Cells(lngCurrentRowNbr, PropQty).ClearContents
.Cells(lngCurrentRowNbr, UnitColumn).ClearContents
lngCurrentRowNbr = lngCurrentRowNbr + 1
rstItemMaster.Filter = ""
rstItemMaster.MoveFirst
Wend
End With
************** End Code ****************
Similar Threads
