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...
mpimAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor 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
rst.AddNew
rst.Field1=SomeValue
rst.Field2=SomeOtherValue
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)
0
 
PatHartmanConnect With a Mentor Commented:
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.
0
 
mpimAuthor Commented:
Great help. Thank you...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.