Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Location
    Sonora, CA
    Posts
    4

    Unanswered: creating multiple records in a table with a button on a form

    I new to VB so I can use some help with what I did wrong in creating my two buttons to add records to a table. Can someone help me out on this.

    Here is what I have
    Code:
    Private Sub Command39_Click()
    
    Dim rs As Recordset
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM inventorytransactions", dbOpenDynaset) ' Dynaset to enable UPdating
    
      If rs.EOF And rs.BOF Then
         MsgBox "File is Empty"
      Else
        rs.MoveLast
      End If
    
       rs.AddNew
         rs("Part") = "kortek kit"
         rs("transaction type") = "addition"
         rs("quantity") = "1"
       rs.Update
       rs.MoveLast
       
       rs.AddNew
         rs("part") = "80-0128-105"
         rs("transaction type") = "removal"
         rs("quantity") = "1"
       rs.Update
       rs.MoveLast
       
       rs.AddNew
         rs("part") = "80-0148-105"
         rs("transaction type") = "removal"
         rs("quantity") = "2"
       rs.Update
       rs.MoveLast
       
       rs.AddNew
         rs("part") = "80-0109-105"
         rs("transaction type") = "removal"
         rs("quantity") = "2"
       rs.Update
       rs.MoveLast
       
       rs.AddNew
         rs("part") = "80-0123-105"
         rs("transaction type") = "removal"
         rs("quantity") = "1"
       rs.Update
       rs.MoveLast
       
       rs.AddNew
         rs("part") = "62-0161-00"
         rs("transaction type") = "removal"
         rs("quantity") = "1"
       rs.Update
       
    End Sub
    
    Private Sub Command41_Click()
    
    Dim rs As Recordset
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM inventorytransactions", dbOpenDynaset) ' Dynaset to enable UPdating
    
      If rs.EOF And rs.BOF Then
         MsgBox "File is Empty"
      Else
        rs.MoveLast
      End If
    
       rs.AddNew
         rs("Part") = "kortek kit"
         rs("transaction type") = "addition"
         rs("quantity") = "10"
       rs.Update
       rs.MoveLast
       
       rs.AddNew
         rs("part") = "80-0128-105"
         rs("transaction type") = "removal"
         rs("quantity") = "10"
       rs.Update
       rs.MoveLast
       
       rs.AddNew
         rs("part") = "80-0148-105"
         rs("transaction type") = "removal"
         rs("quantity") = "20"
       rs.Update
       rs.MoveLast
       
       rs.AddNew
         rs("part") = "80-0109-105"
         rs("transaction type") = "removal"
         rs("quantity") = "20"
       rs.Update
       rs.MoveLast
       
       rs.AddNew
         rs("part") = "80-0123-105"
         rs("transaction type") = "removal"
         rs("quantity") = "10"
       rs.Update
       rs.MoveLast
       
       rs.AddNew
         rs("part") = "62-0161-00"
         rs("transaction type") = "removal"
         rs("quantity") = "10"
       rs.Update
       
    End Sub
    The fields are all in the same table and the form is page sytle which relates to two seperate tables this code being for one of them on a seperate page.

    Any help I would gladly take

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Make the follwoing changes

    1. Change Dim rs As Recordset to Dim rs As DAO.Recordset
    2. Remove all the .movelast statements
    3. Place ALL add records between the ELSE and END IF

    Your code should look like this

    Private Sub Command41_Click()

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM inventorytransactions", dbOpenDynaset) ' Dynaset to enable UPdating

    If rs.EOF And rs.BOF Then
    MsgBox "File is Empty"
    Else
    rs.AddNew
    rs("Part") = "kortek kit"
    rs("transaction type") = "addition"
    rs("quantity") = "10"
    rs.Update

    Remaining Add records

    End if
    End Sub

    Do the same for the Command39 code. May I suggest that you change your Command39 and Command41 to more suitable names as it will make it easier to debug.

  3. #3
    Join Date
    Jul 2010
    Location
    Sonora, CA
    Posts
    4
    I tried it the way you said and I get an error and I'm not sure why could it be because it is a combo box item? The error is Item not found in this collection. (Error 3265) on this line of code

    Code:
    rs("transaction type") = "addition"
    This is the code as it is now
    Code:
    Private Sub Add_one_kortek_Kit_Click()
    
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM inventory transactions", dbOpenDynaset) ' Dynaset to enable UPdating
      If rs.EOF And rs.BOF Then
         MsgBox "File is Empty"
      Else
       
        rs.AddNew
         rs("Part") = "kortek kit"
         rs("Transaction Type") = "addition"
         Description = "addition"
         rs("quantity") = "1"
       rs.Update
             
       rs.AddNew
         rs("part") = "80-0128-105"
         rs("transaction type") = "removal"
         rs("quantity") = "1"
       rs.Update
        
       rs.AddNew
         rs("part") = "80-0148-105"
         rs("transaction type") = "removal"
         rs("quantity") = "2"
       rs.Update
       
       rs.AddNew
         rs("part") = "80-0109-105"
         rs("transaction type") = "removal"
         rs("quantity") = "2"
       rs.Update
       
       rs.AddNew
         rs("part") = "80-0123-105"
         rs("transaction type") = "removal"
         rs("quantity") = "1"
       rs.Update
          
       rs.AddNew
         rs("part") = "62-0161-00"
         rs("transaction type") = "removal"
         rs("quantity") = "1"
       rs.Update
       
      End If
       
    End Sub
    Any help would be great.
    Last edited by scheatham; 07-26-10 at 02:16. Reason: Giving more information

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •