VBA - Using DAO to add record(s) to a table

Greetings...

I'm helping a buddy out with some coding for a small app he's building, and could use some help with this part.
The code that I started with is pasted below.
The idea is to add record(s) to the Inventory table.  What he would like is use an Unbound field (txtAddRecord) on a form to enter that number of items to add to the Inventory table.
So if he enters "10" into the Unbound field (txtAddRecord) - when he clicks the cmdButton he wants to add 10 items (records) to inventory (table).
AND he wants the Field "InventoryNumber" to be increased by 1 for each record added.  If the Unbound Field (txtAddRecord) is "NULL" he doesn't want any records added. Use a msgbox asking User to enter the number of records to add.

Here's some table info:
tblInventory           (Child Table)
InventoryID            (PK - autonum)
ProductID               (FK - num)
InventoryNumber    (Text)  This can be changed to Number if needed
InventorytDateIn     (Date)


I guess the idea would be to run through (Loop?) this type of code as many times as listed in the Unbound Field on the Form.

Private Sub lblInventory_Click()
' Used to add Inventory Item to the tblInventory table

On Error GoTo Err_lblInventory_Click

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
       
'This If statement needs to be improved so it will check for NULL or changes to the number entered in the field

    If IsNull(Me.txtAddRecord) Then  
        MsgBox "You Must Enter the Number of Items to ADD to Inventory.", vbInformation + vbOKOnly, "Inventory Update"
        txtAddRecord.SetFocus
    Else
        Set dbs = currentdb
        Set rst = dbs.OpenRecordset("tblInventory", dbOpenDynaset)
            With rst
                .AddNew
                ![ProductID] = Me.ProductID
                ![InventoryNumber] = 'This Number would be automatically added incrementally by 1
                ![InventorytDateIn] = date
                .Update
            End With
        Me.lstInventory.Requery
        MsgBox "Inventory Items added successfully.", vbInformation + vbOKOnly, "Inventory Update"
    End If

Exit_lblInventory_Click:
    Exit Sub

Err_lblInventory_Click:
    MsgBox Err.Description
    Resume Exit_lblInventory_Click

End Sub


I'm not sure if my question is clear enough so let me know if I need to explain further.
Thanks in advance

Cheers
Michael
LVL 5
Emanon_ConsultingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Emanon_ConsultingAuthor Commented:
I am open to change it there is a better way to achieve the objective.

Cheers
M
0
Data-ManCOOCommented:
Just to understand what you are trying to do...You want to add a record for each item in the inventory.

if there are 100 items in the inventory for a specific item, if he puts 50 in the inventory box, he wants to add 50 new records?  And the Invendory number should be incremented by 1 for each new record added continuing off with the max Inventory number for a specific productID?

Is that correct?  

Mike
0
Emanon_ConsultingAuthor Commented:
Yep, sounds 'bout right.

He has two tables "tblProduct" (Parent) and "tblInventory" (Child)
On his form he shows the current "Product"
He also displays a list of items in Inventory (Sold/Available) for that product.

What is wanted is a text box "txtAddRecord" and a command button (he's actually using a label - don't ask why) "lblAddInventory"

When he enters a number in the "txtAddRecord" field and clicks on "lblAddInventory" he wants to add that number of records to the Inventory table.
So if he enters 10 in "txtAddRecord" he wants 10 records added.  And for each new record added the InventoryNumber should increment by 1.

So...
If he has a product called 'Farm' and currently has 5 records in the Inventory table then tblInventory would look like this

tblInventory
Product  InvNum
Farm     101
Farm     102
Farm     103
Farm     104
Farm     105

Then add ten records
Farm    106
Farm    107
Farm    108
...         etc...


Sound like you got the idea.  The Inventory would only be added for the Product currently displayed on the Form.
Hope that helps to clarify...

Cheers
Michael
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Data-ManCOOCommented:
What happens when there are no inventory records for a specific product in the child table?

Mike
0
Emanon_ConsultingAuthor Commented:
Do you mean what happens when he adds a NEW Product?  What happens to the InventoryNumber?  Where does it start?
Good Question.  Lets say that for his purposes all InventoryNumbers start at "101" and will go up from there.  For all I care it could start at "1".
I think he is just looking to have it all numbered sequentially, not really caring what the first number is.  He did mention to me that he would like to start at 101.  I don't know why...

Cheers
Michael
0
Data-ManCOOCommented:
What I was getting at is this

If there are no records in the child table, the number will start at 101, but what if there were items in the inventory table and the inventory number was 154, then the records went away, the number would start at 101 again.

I recommend storing the max number in the product table

Make a new field called MaxInventoryNumber

When a user add 10 more the code would get the MaxInventoryNumber for that product and then add the records, update the MaxInventoryNumber field.  

Mike
0
Emanon_ConsultingAuthor Commented:
Gotcha.

New field has been added to the Product table.

Thanks
Cheers
Michael
0
Emanon_ConsultingAuthor Commented:
I've also set the default value for this field to 101 for any New records added to the Product table.

Cheers
Michael
0
Data-ManCOOCommented:
Looks like you have the code almost finished...just open a recordset first to get the value of the MaxInventoryNumber

after you open your recordset do this
lngMaxNum = rstTemp!MaxInventoryNumber


then when you are adding the records do this

Create a loop to add the record

open the recordset
For intcount = 1 to 10
                .AddNew
                ![ProductID] = Me.ProductID
                ![InventoryNumber] = lngMaxNum
                ![InventorytDateIn] = date
                .Update
        lngMaxNumber = lngMaxNum + 1
next



Mike

0
Data-ManCOOCommented:
Should be lngMaxNum instead of lngMaxNumber


Mike
0
Emanon_ConsultingAuthor Commented:
Thanks Mike,

I'll give it a whirl and get back to you with my results.

Cheers
Michael
0
Emanon_ConsultingAuthor Commented:
So this is what I have that works so far...
It will add records one at a time.  Now I need to add the loop statement.  I'll have to do some reading on that as I have not "looped" in a while...


Private Sub lblInventory_Click()
' Used to add Inventory Item to the tblInventory table

On Error GoTo Err_lblInventory_Click
       
    If IsNull(Me.txtAddRecord) Then
        MsgBox "You Must Enter the Number of Items to ADD to Inventory.", vbInformation + vbOKOnly, "Inventory Update"
        txtAddRecord.SetFocus
    Else
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim InvNum, counter
       
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("tblInventory", dbOpenDynaset)
       
                lngMaxNum = Me.ProductMaxInvNum
                    With rst
                        .AddNew
                        ![ProductID] = Me.ProductID
                        ![InventoryNumber] = lngMaxNum + 1
                        ![InventorytDateIn] = date
                        .Update
                    End With
                'Now update the Product table "ProductMaxInvNum" field
                Dim rs As DAO.Recordset
               
                Set db = CurrentDb()
                Set rs = db.OpenRecordset("tblProduct", dbOpenDynaset)
                    With rs
                        .FindLast "ProductID =  " & Me.ProductID
                        .Edit
                            !ProductMaxInvNum = lngMaxNum + 1
                        .Update
                    End With
                'Now requery and update the Form
                Me.Requery
            Me.lstInventory.Requery
            'Send a message to the User that records were added successfully
            MsgBox "Inventory Items added successfully.", vbInformation + vbOKOnly, "Inventory Update"
    End If

Exit_lblInventory_Click:
    Exit Sub

Err_lblInventory_Click:
    MsgBox Err.Description
    Resume Exit_lblInventory_Click

End Sub



Cheers
Michael
0
Data-ManCOOCommented:
Try this...I made a few other changed to the code....Mike

Private Sub lblInventory_Click()
' Used to add Inventory Item to the tblInventory table

On Error GoTo Err_lblInventory_Click
       
    If nz(Me.txtAddRecord,0)=0 Then
        MsgBox "You Must Enter the Number of Items to ADD to Inventory.", vbInformation + vbOKOnly, "Inventory Update"
        Me.txtAddRecord.SetFocus
    Else
        Dim rst As DAO.Recordset
        Dim intInvNum As Integer
        Dim intCounter As Integer
       
        intInvNum = Me.ProductMaxInvNum

        Set rst = CurrentDb.OpenRecordset("tblInventory", dbOpenDynaset)
       
        With rst
            For intCount = 1 to Me.txtAddRecord
                .AddNew
                ![ProductID] = Me.ProductID
                ![InventoryNumber] = intInvNum + 1
                ![InventorytDateIn] = date
                .Update
                intInvNum = intInvNum + 1
            Next
        End With
   
        'If the max inv is being held on the form just do this
        Me.ProductMaxInvNum = me.ProductMaxInvNum + me.txtAddRecord

        Me.Requery
        Me.lstInventory.Requery
        'Send a message to the User that records were added successfully
         MsgBox "Inventory Items added successfully.", vbInformation + vbOKOnly, "Inventory Update"
    End If

Exit_lblInventory_Click:
    Exit Sub

Err_lblInventory_Click:
    MsgBox Err.Description
    Resume Exit_lblInventory_Click

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Data-ManCOOCommented:
Change this

        With rst
            For intCount = 1 to Me.txtAddRecord
                .AddNew
                ![ProductID] = Me.ProductID
                ![InventoryNumber] = intInvNum + 1
                ![InventorytDateIn] = date
                .Update
                intInvNum = intInvNum + 1
            Next
        End With


To this

        With rst
            For intCount = 1 to Me.txtAddRecord
                intInvNum = intInvNum + 1
                .AddNew
                ![ProductID] = Me.ProductID
                ![InventoryNumber] = intInvNum
                ![InventorytDateIn] = date
                .Update
            Next
        End With


Mike
0
Emanon_ConsultingAuthor Commented:
Hey Mike,

That did it.  Thanks for your help today!

So this is the final code...

Private Sub lblInventory_Click()
' Used to add Inventory Item to the tblInventory table

On Error GoTo Err_lblInventory_Click
       
    If Nz(Me.txtAddRecord, 0) = 0 Then
        MsgBox "You Must Enter the Number of Items to ADD to Inventory.", vbInformation + vbOKOnly, "Inventory Update"
        Me.txtAddRecord.SetFocus
    Else
        Dim rst As DAO.Recordset
        Dim intInvNum As Integer
        Dim intCounter As Integer
       
        intInvNum = Me.ProductMaxInvNum
        Set rst = CurrentDb.OpenRecordset("tblInventory", dbOpenDynaset)
        With rst
            For intCounter = 1 To Me.txtAddRecord
                intInvNum = intInvNum + 1
                .AddNew
                ![ProductID] = Me.ProductID
                ![InventoryNumber] = intInvNum
                ![InventorytDateIn] = date
                .Update
            Next
        End With
   
        'If the max inv is being held on the form just do this
        Me.ProductMaxInvNum = Me.ProductMaxInvNum + Me.txtAddRecord
               
        Me.Requery
        Me.lstInventory.Requery
        'Send a message to the User that records were added successfully
         MsgBox "Inventory Items added successfully.", vbInformation + vbOKOnly, "Inventory Update"
    End If

Exit_lblInventory_Click:
    Exit Sub

Err_lblInventory_Click:
    MsgBox Err.Description
    Resume Exit_lblInventory_Click

End Sub


Cheers
Michael
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.