Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Manually insert a record into a Master/Child table

Posted on 2016-11-08
3
Medium Priority
?
29 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 40

Assisted Solution

by:PatHartman
PatHartman earned 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

926 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