0

I'm having trouble with adding multiple records to a table in access. The fields are number (data type) and I can't change them due to the relationships needed for a query used to calulate amounts. Here is the cade I have currently maybe someone could help me out with this as I'm fairly new to VB. I know that this code will work with Text (data type) fields. Please help here is the code

Private Sub Ctl1_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"
     rs("Technician") = "auto button add"
     rs("quantity") = "1"
   rs.Update
         
   rs.AddNew
     rs("part") = "80-0128-105"
     rs("transaction type") = "removal"
     rs("Technician") = "auto button add"
     rs("quantity") = "1"
   rs.Update
    
   rs.AddNew
     rs("part") = "80-0148-105"
     rs("transaction type") = "removal"
     rs("Technician") = "auto button add"
     rs("quantity") = "2"
   rs.Update
   
   rs.AddNew
     rs("part") = "80-0109-105"
     rs("transaction type") = "removal"
     rs("Technician") = "auto button add"
     rs("quantity") = "2"
   rs.Update
   
   rs.AddNew
     rs("part") = "80-0123-105"
     rs("transaction type") = "removal"
     rs("Technician") = "auto button add"
     rs("quantity") = "1"
   rs.Update
      
   rs.AddNew
     rs("part") = "62-0161-00"
     rs("transaction type") = "removal"
     rs("Technician") = "auto button add"
     rs("quantity") = "1"
   rs.Update
   
   MsgBox "1 kit added to Stock"
   
  End If
   
End Sub

Thanks

3
Contributors
3
Replies
5
Views
7 Years
Discussion Span
Last Post by scheatham
-1

First, if you cant update due to Relationship Integrity, you must add data by grouping it first, and;

What is your Foreign Key and Primary Key in Inventory Transactions table?..

Does the Value from rs("part") is a Foreign key and Does the Value you are adding is existing to its Primary Table?..

Does rs("transaction type") is a Foreign key also has a reference Table?..

Does rs("Technician") is a Foreign key also has a reference table?..

1

The fields are number (data type)

I think problem is you are trying to add Text in number field(as you have mentioned). If data type is mismatched then for sure we can't add records.
Thanks

0

They all have reference in another table except the quantity which if I pull up the table inventory transactions I can select through a combo box. That is where the problem lies. That is why I want to be able to show the vavles as numeric data types so the table will accept the data

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.