You can use the query design window to help you.
1. Create a query, without any table.
2. Change it to an Append query (Append on Query menu.)
Access will ask you which table to append to.
Choose your middle table.
3. Type 99 into the Field row.
In the Append row under this, choose the field from the middle subform that
matches the field on your main form (i.e. the foreign key field.)
4. Switch to SQL View (View menu.)
There's a sample of the statement you need.
5. Set your form's AfterInsert property to:
[Event Procedure]
6. Click the Build Button (...) beside the property.
Access opens the code window.
Set up the code like this:
Private Sub Form_AfterInsert()
Dim db As DAO.Database
dim strSql As String
strSql = "INSERT INTO YourMiddleTable (YourForeignKeyField) " & _
"SELECT " & Me.YourMainFormID & " AS Expr1;"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
'Debug.Print "Added " & db.RecordsAffected & " record(s.)"
Set db = Nothing
End Sub
If this kind of thing is new, this might help:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
deb said:
Do you have an example of an append query statement. I have not created
one
of these before.
Thank you very much for your reply.
--
deb
Allen Browne said:
You could use the AfterInsert event procedure of the main form to execute
an
Append query statement to add a record to the middle table.
deb said:
You are correct. I need to create a new record. At the time of
creation
the
user goes directly to the mIn form and the subsub form and enters data.
where as the sub form does not have user data until a later date.
I need either...
When the user creates a record in the main form, have the subform
create a
new record.
or
When the user creates a record in the subsubform, have the subform
create
a
new record.
The subform record is needed to link to the subform data.
Thanks, I know this is out of the norm but it is the way the data is
entered.
--
deb
:
Allen, did you fully answer OP's question? It reads to me like she
needs
to force the creation of a new record in the subform when she enters
data in the subsubform ... not merely make (the subform) dirty...
--
Clif
To dirty the form, assign a value to a bound control, e.g.:
Me.[Sub1].Form![SomeControl] = Null
Main form is fProj - data for project
Subform is fDeliv - data for deliverable
SubSub form is fTL - data for transmittals
I sometimes have data that must be input into the subsubform -( ie
transmittal dates due) before I have data for the Subform.
Days later the transmittal data is ready to send to the customer
and
then
the field called TLno gets input into the subform
The issue is that I need the autonumber in the subform to populate
since it
is a key for the subsubform.
Therefore I do not get an autonumber id for the subform and my
subsub
form
data is lost.
how can I force a record in the subform when data is input into the
subsubform?
Is there a way to make the subform dirty and then save?