Using .AddNew to add multiple records

Hey guys,

I created a database for logging physical inventory tickets.  I have an unbound form for adding records to a subform datasheet.  I'm trying to use the .AddNew command to add multiple records at once for items that have multiple breakdowns (Units X QTY).  Please see the code below.

Every time I try to add an item that has only one breakdown, it tells me that I have to enter a value for the ItemNo field.  If I add an item with 3 breakdowns using both the Units2/QTY2 and Units3/QTY3 fields then it works as intended.  Any clues as to why it ask for a value for the ItemNo field when Units2/QTY2 or Units3/QTY3 is empty?

Thanks in advance


Set db = CurrentDb()
Set rs = db.OpenRecordset("tblInventory")
With rs
.AddNew
If Not IsNull(ItemNo.Value) Then
!ItemNo = ItemNo.Value
!Supplier = Supplier.Value
!LotNo = LotNo.Value
!QTY = QTY.Value
!Units = Units.Value
!UoM = UoM.Value
!Location = Location.Value
!ExpDate = ExpDate.Value
!CountedBy = CountedBy.Value
!RecountedBy = RecountedBy.Value
!EnteredBy = EnteredBy.Value
!ReviewedBy = ReviewedBy.Value

End If
.Update

.AddNew
If Not IsNull(Units2) Then
!ItemNo = ItemNo.Value
!Supplier = Supplier.Value
!LotNo = LotNo.Value
!QTY = QTY2.Value
!Units = Units2.Value
!UoM = UoM.Value
!Location = Location.Value
!ExpDate = ExpDate.Value
!CountedBy = CountedBy.Value
!RecountedBy = RecountedBy.Value
!EnteredBy = EnteredBy.Value
!ReviewedBy = ReviewedBy.Value

End If
.Update

.AddNew
If Not IsNull(Units3) Then
!ItemNo = ItemNo.Value
!Supplier = Supplier.Value
!LotNo = LotNo.Value
!QTY = QTY3.Value
!Units = Units3.Value
!UoM = UoM.Value
!Location = Location.Value
!ExpDate = ExpDate.Value
!CountedBy = CountedBy.Value
!RecountedBy = RecountedBy.Value
!EnteredBy = EnteredBy.Value
!ReviewedBy = ReviewedBy.Value

End If
.Update

End With

Open in new window

paintb4707Asked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
This:
With rs  .AddNew
If Not IsNull(ItemNo.Value) Then
!ItemNo = ItemNo.Value
!Supplier = Supplier.Value
!LotNo = LotNo.Value
!QTY = QTY.Value
!Units = Units.Value
!UoM = UoM.Value
!Location = Location.Value
!ExpDate = ExpDate.Value
!CountedBy = CountedBy.Value
!RecountedBy = RecountedBy.Value
!EnteredBy = EnteredBy.Value
!ReviewedBy = ReviewedBy.Value
 
End If
.Update
Needs to be:
If Not IsNull(ItemNo.Value) Then
With rs
.AddNew
!ItemNo = ItemNo.Value
!Supplier = Supplier.Value
!LotNo = LotNo.Value
!QTY = QTY.Value
!Units = Units.Value
!UoM = UoM.Value
!Location = Location.Value
!ExpDate = ExpDate.Value
!CountedBy = CountedBy.Value
!RecountedBy = RecountedBy.Value
!EnteredBy = EnteredBy.Value
!ReviewedBy = ReviewedBy.Value
 
.Update

End If
JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The others too by the way....
You only want to execute .Addnew and .Update if there is something to add.
JimD.
0
 
paintb4707Author Commented:
Wow, I can't believe that's all it was. Thanks a lot!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.