Auto insert new record on subform. 1

[TommyF]

I have a mainform with a linked subform on it. I insert a new record on the mainform which inserts the next order number for me. As well inserting the next order number I also want it to insert a new record in the subform with one of the fields filled in with data.

 

[Hmadyson]

write a query to append a new record to the subform table when a new record is added, then refresh the subform.

 

[TommyF]

Thank you for your help but I am having trouble with my query. I can do an append query to insert a new record by making the Linked ID of the subform equal to the ID of the mainform, but I can't seem to get the critera correct so it just picks the one record.

Am I running my query correct to make a new record?

How would I also in my append query set it up so that it would fill in some text in a field for me or would I have to run an update query to do this.

Thanks again.

 

[Hmadyson]

I think that the query should be something like:

INSERT INTO SubformTable( ID )
SELECT 592 AS Expr1;

The number 592 is the only thing that changes each time, so you could make it a parameter query

PARAMETERS MainTableLink Long;
INSERT INTO SubformTable( ID )
SELECT [MainTableLink] AS Expr1;

but you would have to in code assign the parameter when you call it by using the Query object.

Sub RunQuery()
Dim qdefUse As QueryDef

Set qdefUse = CurrentDb.QueryDefs("AddToSubform&quot
With qdefUse
.Parameters("MakeTableLink&quot = 592
.Execute
End With

Set qdefUse = Nothing
End Sub

 

[TommyF]

I have worked through your email and applied to my form and its works perfectly, thanks for all your help