• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 31
  • Last Modified:

Manually insert a record into a Master/Child table

Hi, I have two tables Invoices and InvoiceItems which are linked by InvoiceNo. Normally, records are added thru a Form/Subform which takes care automatically of ensuring data integrity. However, I have one instance where I need to manually add a new record into Invoices and InvoiceItems. What is the best way to ensure that I know which InvoiceNo to use to link the two tables  (it is generated automatically in Invoices as AUTONUMBER)? Thank you...
2 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Depends on how you're doing it. If you're using Recordsets, then your Recordset would contain the AutoNumber:

Dim invoice As Double
Dim rst As DAO.Recordset
invoice = rst("YourIDFieldName")
etc etc

If you're doing it via SQL or Queries you can use @@IDENTITY:

<code here to add your Invoice>
Dim invoice As Double
Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT @@IDENTITY")
invoice = rst(0)
If your BE is Jet/ACE (or possibly SQL Server), you can use one update as long as you are inserting only a single row into the child table.

Create a query that selects the necessary rows from both tables.  Do not include the PK from the parent table but DO include the FK from the child table --
SELECT tblParent.parentText, tblChild.ParentID, tblChild.childText
FROM tblParent INNER JOIN tblChild ON tblParent.ParentID = tblChild.ParentID;

In your DAO code, populate parentText and childText.  Access will fill ParentID (the FK) automatically.

Of course if you are appending multiple rows to the child table, you will need to do them separately and use the @@Identity as Scott described if the BE is SQL Server or you run an append query rather than using DAO.   But if the BE is Jet/ACE, you can just reference the autonumber from within the procedure as Scott showed in his first example.
mpimAuthor Commented:
Great help. Thank you...

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now