Solved

Manually insert a record into a Master/Child table

Posted on 2016-11-08
3
25 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 37

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
unknown computers in my LAN 12 99
Why Won't My Excel Workbook Refresh The Data 2 46
Microsoft Access 32-bit or 64-bit? 11 58
ISeries Remote Location Route entry 2 23
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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.

734 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