Adding new partial records to a subform

[DarkWolff]

I'm trying to figure out how to add a record to a continuous subform from another subform and partially fill in the data and have the user fill in the rest. Is this possible?

[pbaldy]

Any number of ways, depending on your specifics. One would be executing an append query and requerying the subform.

[DarkWolff]

Thanks for your response. That makes a whole lot of sense. I spent too much time try to set the texboxes to the value I wanted and got frustrated.

Is there a way I could select one textbox in the last record of a continuous subform? I seem to be having issues correctly referencing continuous controls from the main form.

[DarkWolff]

Ok I have the append query partially working. However, it run I run the code it tries to add 374 duplicates of the record. Here is the SQL code:

INSERT INTO tblNYTimesheet_Monthly ( StaffID, [month], [Project#], FiscalYear, hours )
SELECT [Forms]![frmTimesheet_Monthly]![txtStaffID] AS StaffID, [Forms]![frmTimesheet_Monthly]![txtmonth] AS [month], [Forms]![frmTimesheet_Monthly]![fsubTSResAlloc]![txtProjectID] AS [Project#], [Forms]![frmTimesheet_Monthly]![txtCurrentFY] AS FiscalYear, 0 AS hours
FROM tblNYTimesheet_Monthly;

The code is run from a command button on a continuous subform (so it knows the correct record). I have no idea where the 374 comes from though.

[pbaldy]

Without a WHERE clause, the SELECT will pull every record in the table. I assume there are currently 374. You want a criteria on the key field that points to the form to restrict it to that record. In SQL view that would look something like:

INSERT INTO tblNYTimesheet_Monthly ( StaffID, [month], [Project#], FiscalYear, hours )
SELECT [Forms]![frmTimesheet_Monthly]![txtStaffID] AS StaffID, [Forms]![frmTimesheet_Monthly]![txtmonth] AS [month], [Forms]![frmTimesheet_Monthly]![fsubTSResAlloc]![txtProjectID] AS [Project#], [Forms]![frmTimesheet_Monthly]![txtCurrentFY] AS FiscalYear, 0 AS hours
FROM tblNYTimesheet_Monthly
WHERE KeyFieldName = [Forms]![frmTimesheet_Monthly]![fsubTSResAlloc]![KeyFieldName]

Using the correct field/control names of course.

[pbaldy]

Oh, you could also use VALUES instead of SELECT:

INSERT INTO tblNYTimesheet_Monthly ( StaffID, [month], [Project#], FiscalYear, hours )
VALUES( [Forms]![frmTimesheet_Monthly]![txtStaffID], [Forms]![frmTimesheet_Monthly]![txtmonth], [Forms]![frmTimesheet_Monthly]![fsubTSResAlloc]![txtProjectID], [Forms]![frmTimesheet_Monthly]![txtCurrentFY], 0 )

A VALUES clause will only insert a single record.

[DarkWolff]

Thank you! That did it!

[pbaldy]

Happy to help!