Inserting maintable's autonumber to subtable field

Joelyue

Registered User.
Local time
Tomorrow, 03:22
Joined
Mar 16, 2007
Messages
18
Hi,
i am writing an onclick event vba which insert values in my form to 2 different tables in one click. A main table and a subtable.
The subtable is linked to the main table by a field called MainTableIndex and the main table generates the Index by autonumber.

My question is how do i reference the main table index which is yet to be generated and insert it into the subtable sql.
 
You could make the assumption that after the record is inserted into the main table, that its MainTableIndex (autonumber) is the maximum value for that table.

e.g.
Dim myIndex as Long
Currentdb.execute "INSERT INTO MainTable (Field1, Field2) VALUES (1,2);"
myIndex = dmax("MainTableIndex","MainTable")
Currentdb.execute "INSERT INTO SubTable (MainTableIndex, Field1) VALUES (" & myIndex & ",2);"

Pete
 
If you have a subform on the main form and they are linked properly, you don't have to insert anything. Access will do it for you - when you create a new record on the subform, as long as you have the Master/Child links set up to the primary/foreign key on the main/subform then it will insert the primary key of the main form as the foreign key in the subform.
 
I have no mainforms and subforms. I am only using 1 form which is not linked to any tables, it just consists of textboxes and combo boxes.
This is because i dont want users to key into the tables directly. I want values keyed into standalone boxes and then use Sql to insert values into the tables after clicking a submit button.
 
Pardon the sarcasm, but I just can't resist - So you are trying to do your hardest to keep from using any of the benefits of using Access and instead are trying to code everything.

I find it interesting that people will take a very good rapid development tool and then do their best to keep from using any of the things that make it that good tool.
 
I understand the root of your sarcasm, but i really cant let my users access the tables directly because they will change and exploit it to their advantage. It is an integrity issue. And i dont have a budget or expertise to purchase an SQL database and come up with a front end back end thingy. So Access is the best option so far.
 
So how is having a bound form letting the users access the tables directly? What are they needing to do and what are you needing them to not do?
 

Users who are viewing this thread

Back
Top Bottom