Unbound form to update two tables (1 Viewer)

[Rick Blaine]

I have two tables that I want to update from data in an unbound form. How do I capture the ID of the new record created in table 1 in order to write it to a field in the new record being created in table 2.

Table 1
id
f1
f2
f3 and so on

Table 2
ID
Table 1 id
F2
F3 and so on

Any assistance will be appreciated.

Rick

 

[plog]

First, why? Unbound forms are more work than usual forms (as you are seeing), so why is this necessary?

Second, explain the relationship between Table1 and Table2 better. My instinct upon seeing questions that involve situations going against common practice is to think your tables are improper. Can you explain the entire situation better?

Third, and to address your actual question: You do the first INSERT INTO Table1, then you use either a DMax(http://www.techonthenet.com/access/functions/domain/dmax.php) to get the largest ID, or a Dlookup(http://www.techonthenet.com/access/functions/domain/dlookup.php) using various pieces of data you inserted as criteria to get the ID. With that you then do the INSERT INTO Table2.

Again, though, why? And explain the situation a letter more.

 

[CJ_London]

in code

 dim db as dao.database
 dim rst as dao.recordset
  
 set db=currentdb
 db.execute("INSERT INTO mTable......")
 set rst=db.openrecordset("SELECT @@Identity")
 newID=rst.fields(0)
 set rst=nothing
 set db=nothing

 

[jdraw]

Rick,

It is often better to reflect your real tables - names, fields, purposes etc -than to use Table 1, Table 2, f1.... to help readers understand?
I recommend you read up on database concepts, table design, normalization and keys (Primary and Foreign).

Good luck.

Here is a tutorial that will help you -but you have to work through it. It includes a solution.

 

[Rick Blaine]

Plog, thank you for your response.

First - unbound form is not necessary, just desired.

Second - Table 1 carries general information. Table 2 carries unique information related to Table 1. There can be any number of entries in Table 2 that relate to a particular entry in Table 1.

Third - This should have been your only response. Thank you for the time and concern you showed in generating your response.

Come by Café Americain for a beverage on the house.

Rick

 

[Rick Blaine]

Thank you CJ. You got to the nub of the problem with an excellent solution.

Rick