Solved

Manually insert a record into a Master/Child table

Posted on 2016-11-08
3
19 Views
Last Modified: 2016-11-08
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...
0
Comment
Question by:mpim
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 41878774
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41878870
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
 

Author Closing Comment

by:mpim
ID: 41878891
Great help. Thank you...
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now