?
Solved

Using .AddNew to add multiple records

Posted on 2009-12-28
3
Medium Priority
?
335 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:paintb4707
  • 2
3 Comments
 
LVL 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 26134279
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
 
LVL 59
ID: 26134285
The others too by the way....
You only want to execute .Addnew and .Update if there is something to add.
JimD.
0
 

Author Closing Comment

by:paintb4707
ID: 31670568
Wow, I can't believe that's all it was. Thanks a lot!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

621 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