[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-11-03
15
Medium Priority
?
1,608 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Emanon_Consulting
  • 8
  • 7
15 Comments
 
LVL 5

Author Comment

by:Emanon_Consulting
ID: 12487559
I am open to change it there is a better way to achieve the objective.

Cheers
M
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12487844
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
 
LVL 5

Author Comment

by:Emanon_Consulting
ID: 12487993
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
Industry Leaders: 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!

 
LVL 18

Expert Comment

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

Mike
0
 
LVL 5

Author Comment

by:Emanon_Consulting
ID: 12488186
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
 
LVL 18

Expert Comment

by:Data-Man
ID: 12488242
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
 
LVL 5

Author Comment

by:Emanon_Consulting
ID: 12488297
Gotcha.

New field has been added to the Product table.

Thanks
Cheers
Michael
0
 
LVL 5

Author Comment

by:Emanon_Consulting
ID: 12488326
I've also set the default value for this field to 101 for any New records added to the Product table.

Cheers
Michael
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12488344
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
 
LVL 18

Expert Comment

by:Data-Man
ID: 12488350
Should be lngMaxNum instead of lngMaxNumber


Mike
0
 
LVL 5

Author Comment

by:Emanon_Consulting
ID: 12488396
Thanks Mike,

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

Cheers
Michael
0
 
LVL 5

Author Comment

by:Emanon_Consulting
ID: 12488588
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
 
LVL 18

Accepted Solution

by:
Data-Man earned 2000 total points
ID: 12488709
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
 
LVL 18

Expert Comment

by:Data-Man
ID: 12488719
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
 
LVL 5

Author Comment

by:Emanon_Consulting
ID: 12488854
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

872 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