Member Avatar for scheatham

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
  • 131 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by scheatham

Dani AI

Generated

The root cause here is the data-type / foreign-key mismatch. As noted, Access will reject text when the table expects Number. As asked, confirm which columns in Inventory Transactions are foreign keys — if Part, Transaction Type and Technician are numeric FKs you must insert their numeric IDs, not their display names. Combo boxes often show a name but are bound to the numeric key; use the combo box Value (the bound column) rather than the displayed text.

Two practical ways to fix this:

  • If the form has combo boxes whose BoundColumn is the ID, use that value directly (e.g., Me.cboPart.Value) when you build the record.
  • If you only have the display text, look up the numeric PK in the lookup table (DLookup or a small SELECT) and use that ID. Always assign numbers without quotes (or convert them with CInt/CLng) so Access treats them as numeric.

Example pattern (replace table/field names to match your schema):

Dim partID As Long
partID = Nz(DLookup("PartID", "tblParts", "PartName = '" & Replace("kortek kit","'","''") & "'"), 0)
If partID = 0 Then
  MsgBox "Part not found: kortek kit": Exit Sub
End If

rs.AddNew
rs!PartID = partID            ' numeric FK
rs!TransactionTypeID = 2      ' numeric FK (no quotes)
rs!TechnicianID = Me.cboTech  ' combo bound to numeric PK
rs!Quantity = 1               ' numeric literal (no quotes)
rs.Update

Troubleshooting tips: verify the Relationships diagram and field types, test a single insert from the Immediate window, and include error handling. If referential integrity blocks inserts, ensure parent rows exist (or insert parents first). Avoid trying to set AutoNumber primary keys directly. Following those checks and using IDs instead of display text will resolve the type mismatch and allow the multiple inserts to succeed.

Recommended Answers

All 3 Replies

Member Avatar for jhai_salvador

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?..

Member Avatar for kinwang2009

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

Member Avatar for scheatham

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.