In MS Access 2007, how do I save/add new record from a form to a specific table?

That's the way bound forms work - any code you run impacts the current record, unless you specifically tell Access to work with other records.

Can you explain more about your macro? I have worked with macros in a long time, but if your macro does nothing more than write a date to the Form's textbox, then clicking that button will overwrite it each time it's clicked.

If you want to create a NEW record each time the button is clicked, you can do that as well.

Please clarify what you want to do, and we'll see if we can assist.

Could it be that you are writing the date to a new record?  If so, that might better be done by using code on the BeforeInsert event of the form.  That would ensure that the date is only written to a new record.  Or (if it is something simple, like today's date), just put Date() in the table as the Date field's default value.

If you are running Access 2010, you could try the new data macro feature for tables.

Could you add a test in the Macro to see if a date is already in the field and to not update the value if IsDate([field]) is true?

Sorry for the delay.  The client has forced me to change my form/subform since then.

I have a main form and 3 subforms (all are bounded to 4 specific tables that have the hierarchical relationships.  the subform I want to append data has 3 fields: Task, Analyst and Date.
Everytime I change a specific field on the main form [Main_Task], I want to take the current values of Main_Task, Main_Date and sfrm_Analyst and append it to the subform's 3 fields: Task, Analyst and Date.  I am assuming I need to use a button to trigger a macro to append the 3 specific values?

Thank you in advance

You do not have to use a button.  This wouuld provide a pro-active method to run the process.   When the main form does an update, the BeforeUpdate Event is triggered.  Your code can be placed in this routine to add the subform record and, then, refresh the display.

so on the main form, under the event BeforeUpdate, what is the code and what do I use to refresh both form and subforms?

the actual names of the form is "frm 100 Working F" and the subform is "sfrm 300 Working TL"

Thanks,

Just use an INSERT query:

CurrentProject.Connection.Execute "INSERT INTO YourTable(Col1, Col2, Col3) VALUES(Val1, Val2, Val3)"

Below in the Append Query (Insert Into query).  What would be the VB syntax in placing in the "AfterUpdate()" Sub?

INSERT INTO [300 Working T] ( superkey, [Task Analyst], Task, [Task Date] )
SELECT [100 Working F].superkey, [000 Task List].Analyst, [100 Working F].[Current Task], [100 Working F].[Status Date]
FROM [100 Working F] INNER JOIN [000 Task List] ON [100 Working F].superkey = [000 Task List].superkey
WHERE ((([100 Working F].superkey)=[Forms]![frm 100 Working F]![superkey]))
ORDER BY [100 Working F].[Status Date];


Thanks in advance

CurrentProject.Connection.Execute "Your Insert String Here"

I just entered as prescribed but it errored out.
What am I missing?

CurrentProject.Connection.Execute "INSERT INTO [300 Working T] ( superkey, [Task Analyst], Task, [Task Date] )SELECT [100 Working F].superkey, [000 Task List].Analyst, [100 Working F].[Current Task], [100 Working F].[Status Date] FROM [100 Working F] INNER JOIN [000 Task List] ON [100 Working F].superkey = [000 Task List].superkey WHERE ((([100 Working F].superkey) = [Forms]![frm 100 Working F]![superkey]))ORDER BY [100 Working F].[Status Date];"

Thanks,